Yes, this can be done as well. I parse data from a txt file into one of my apps. This is a lot of code, but it is showing how to check for the file, then open it for reading, then overwriting it with a blank copy (so data is not imported twice). Try this:
'-----Code Starts-----
Dim i As Integer
Dim strYYYY As String
Dim strLine As String
Dim strDate As String
Dim strMM As String
Dim strDD As String
Dim strYY As String
Dim strTime As String
Dim strHR As String
Dim strMin As String
Dim strSSN As String
Dim strL500 As Integer
Dim strR500 As Integer
Dim strL1000 As Integer
Dim strR1000 As Integer
Dim strL2000 As Integer
Dim strR2000 As Integer
Dim strL3000 As Integer
Dim strR3000 As Integer
Dim strL4000 As Integer
Dim strR4000 As Integer
Dim strL6000 As Integer
Dim strR6000 As Integer
Dim strL8000 As Integer
Dim strR8000 As Integer
Dim fso As Variant
Dim objStream As Variant
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim objFile As Variant
Dim AudioArray As Variant
Dim strSQL As String
i = 0
strYYYY = DatePart("yyyy", Date)
'Create FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
'Check for file existence and open if there
If fso.FileExists("C:\data.txt") Then
Set objStream = fso.OpenTextFile("C:\data.txt", 1, False, 0)
End If
'Read File and parse data (in my file, not yours)
Do While Not objStream.AtEndOfStream
strLine = objStream.ReadLine
AudioArray = Split(strLine, ",")
strDate = Right(AudioArray(0), 6)
strMM = Left(strDate, 2)
strDD = Right(Left(strDate, 4), 2)
strYY = Left(strYYYY, 2) & Right(strDate, 2)
strDate = strMM & "/" & strDD & "/" & strYY
strTime = AudioArray(1)
strHR = Left(strTime, 2)
strMin = Right(strTime, 2)
strTime = strHR & ":" & strMin
strSSN = AudioArray(2)
strL500 = AudioArray(9)
strR500 = AudioArray(10)
strL1000 = AudioArray(11)
strR1000 = AudioArray(12)
strL2000 = AudioArray(13)
strR2000 = AudioArray(14)
strL3000 = AudioArray(15)
strR3000 = AudioArray(16)
strL4000 = AudioArray(17)
strR4000 = AudioArray(18)
strL6000 = AudioArray(19)
strR6000 = AudioArray(20)
strL8000 = AudioArray(21)
strR8000 = AudioArray(22)
'Connect to back end and send data
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "DSN=Audiometer;"
objRS.Open "SELECT * FROM tblAudiometer", objConn, 3, 3
objRS.AddNew
objRS("Date") = strDate
objRS("Time") = strTime
objRS("SSN") = strSSN
objRS("L500") = strL500
objRS("R500") = strR500
objRS("L1000") = strL1000
objRS("R1000") = strR1000
objRS("L2000") = strL2000
objRS("R2000") = strR2000
objRS("L3000") = strL3000
objRS("R3000") = strR3000
objRS("L4000") = strL4000
objRS("R4000") = strR4000
objRS("L6000") = strL6000
objRS("R6000") = strR6000
objRS("L8000") = strL8000
objRS("R8000") = strR8000
objRS.Update
objRS.Close
objConn.Close
i = i + 1
Loop
'Create new file, overwriting old file and eliminating old data
Set objFile = fso.CreateTextFile("C:\Data.txt")
MsgBox i & " File(s) Transferred"
'-----Code Ends-----
HTH
BTW, I haven't gotten around to moving the connection info off the loop, so it is opened and closed on each loop. That adds time. YOu can move the connection info before the loop to save time.
mmcdonal
|
