Problem importing text file into Table

O

OrganMan

Registered User.
Local time
Today, 16:09
Joined
Jun 9, 2008
Messages
36
Hi there,
Can someone please look at my code and help me out help.

I am trying to convert over VB code to work with VBA in Access 2003. The code to list all of the available text files in a particular folder is working fine, and they get populated in a listbox called lstFiles, the problem is when I click on one of the textfile from this listbox I want it to show the contents of the text file in the listbox below named lstFileLines. I get the Compile Error: Method or data member not found, and highlights Text from lstFiles.Text on the following line of code:
Set oFile = fso.GetFile(txtFolder.Text & lstFiles.Text)

Here is all of my code, I am ultimately trying to import this text file into a table named CardTest, in case you notice any other code errors that I may have.

Option Compare Database
Private Sub cmdClear_Click()
Me.lstFileLines = ""
Me.lstFiles = ""
End Sub
Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmImportCards", acSaveYes
End Sub
Private Sub cmdGetFiles_Click()
'Declaring the following variables
Dim fso As Scripting.FileSystemObject
Dim oFolder As Scripting.Folder
Dim oFile As Scripting.File
Dim i As Long

'Instantiating the filesystem object
Set fso = New Scripting.FileSystemObject

'Clearing the file list
'Me.lstFiles = ""

txtFolder.SetFocus
txtFolder.Text = "E:\Cards\1980-81\O-Pee-Chee"

If Len(txtFolder.Text) > 0 Then

If Right(txtFolder.Text, 1) <> "\" Then
txtFolder.Text = txtFolder.Text & "\"
End If

'Checking to see if the folder name typed in the textbox actually exists
If fso.FolderExists(txtFolder.Text) Then
'Setting the oFolder object to what is in the textbox
Set oFolder = fso.GetFolder(txtFolder.Text)

'Looping through the files in the folder object
For Each oFile In oFolder.Files
'Checking the file extension of the file
If Len(oFile.Name) >= 4 Then
'If the file has a txt file extension then add the files
If UCase(Right(oFile.Name, 4)) = ".TXT" Then
lstFiles.AddItem oFile.Name
End If
End If
Next

Else
MsgBox "This folder does not exist."

End If
End If

'Removing the oFile object from memory
Set oFile = Nothing
Set oFolder = Nothing
Set fso = Nothing

End Sub

Private Sub cmdImport_Click()

Dim i As Long, iPos As Long
Dim sLine As String
Dim sCardNo As String, sFirstName As String, sLastName As String
Dim sRookieCard As String, sAdditionalDescription As String
Dim sYearId As String, sSetId As String, sSubsetId As String

Me.Refresh
DoEvents

If lstFileLines.ListCount > 0 Then
m_oConnection.Open

If m_oConnection.State = adStateOpen Then
sSetId = InputBox("What is the Set #?", "Set", "0")
sSubsetId = InputBox("What is the Subset #?", "Set", "0")
sYearId = InputBox("What is the Year Id#?", "Set", "0")

For i = 1 To lstFileLines.ListCount
sLine = lstFileLines.ListCount(i - 1)
Debug.Print sLine
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sCardNo = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sFirstName = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sLastName = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sRookieCard = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))
sAdditionalDescription = Replace(sLine, """", """""")
End If
End If
End If
End If
ImportRecord sCardNo, sFirstName, sLastName, sRookieCard, sAdditionalDescription, sSetId, sSubsetId, sYearId
Next i
m_oConnection.Close
End If
End If

End Sub


'Private Sub Form_Load()

'm_sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Cards\AnotherTry.mdb"

'Set m_Connection = New ADODB.Connection
'm_oConnection.ConnectionString = m_sConnectionString
'End Sub
Private Sub lstFiles_Click()
Dim fso As Scripting.FileSystemObject
Dim oStream As Scripting.TextStream
Dim oFile As Scripting.File

Set fso = New Scripting.FileSystemObject

Set oFile = fso.GetFile(txtFolder.Text & lstFiles.Text)

Set oStream = oFile.OpenAsTextStream(ForReading)

'Me.lstFileLines = ""

Do While Not oStream.AtEndOfStream
lstFileLines.AddItem oStream.ReadLine
Loop

oStream.Close

If lstFileLines.ListCount > 0 Then
cmdImport.Enabled = True
End If


End Sub

Private Sub ImportRecord(ByVal sCardNo As String, ByVal sFirstName As String, ByVal sLastName As String, ByVal sRookieCard As String, ByVal sAdditionalDescription As String, ByVal SetId As String, ByVal SubsetId As String, ByVal sYearId As String)
Dim sSQL As String
Dim iSetId As Integer
Dim iSubsetId As Integer
Dim iYearId As Integer

iSetId = Val(sSetId)
iSubsetId = Val(sSubsetId)
iYearId = Val(sYearId)

If Len(sCardNo) <> 0 And Len(sFirstName) <> 0 And Len(sLastName) <> 0 Then
sSQL = "INSET INTO CARDTEST (CardNo, FirstName, LastName, RookieCard, AdditionalDescription, SetId, SubsetId, YearId)VALUES (""" & sCardNo & """, """ & sFirstName & """, """ & sLastName & """, """ & sRookieCard & """, """ & sAdditionalDescription & """, " & iSetId & ", " & iSubsetId & ", " & iYearId & ")"
m_oConnection.Execute sSQL
End If
Debug.Print sSQL

End Sub


Thanks
 
An Access.Listbox object does not have a property called 'Text'
 
Ok maybe I should break my problem down.

I have the code working to search a particular folder and display all of the txt files from this folder in a list box(lstFiles). What I would next like to do is populate a second listbox(lstFileLines) with the Tab delimited data contained in the textfile that the user clicks on in the lstFiles listbox. Can someone supply me with the proper code needed.
Finally after viewing this data in the lstFileLines listbox I will want to import this data into a Access 2003 table named (CardTest).

Thanks for the help.
 
You might try importing the data into a temp table then use a subform to display the imported data. If the user approves then allow the user to click a button to append/update the temp data into your table.
 
Ok, I have been able to fix more of my code. I am now able to populate the second listbox with the data contained in the text file. I am now trying to get this data imported into my access table and seem to be having problems with the Connection object, getting the error, "Run time error'91', Object variable or With block variable not set" and the breakpoint goes to m_oConnection.Open within the cmdImport_Click sub procedure.

Keep in mind that my code did work with a VB6.0 form when importing to an Access table. I have been just trying to convert the code that I have to convert to get it to work with my Access 2003 VBA form and table.

Option Compare Database
'Declaring the object as a connection object
Private m_oConnection As ADODB.Connection
'Declaring the string as a connection string
Private m_sConnectionString As String
Private Sub cmdClear_Click()
Me.lstFileLines.RowSource = ""
Me.lstFiles.RowSource = ""
End Sub
Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmImportCards", acSaveYes
End Sub

Private Sub cmdGetFiles_Click()
'Declaring the following variables
Dim fso As Scripting.FileSystemObject
Dim oFolder As Scripting.Folder
Dim oFile As Scripting.file
Dim i As Long

'Instantiating the filesystem object
Set fso = New Scripting.FileSystemObject

'Clearing the file list
'Me.lstFiles = ""

txtFolder.SetFocus

If Len(txtFolder.Text) > 0 Then

If Right(txtFolder.Text, 1) <> "\" Then
txtFolder.Text = txtFolder.Text & "\"
End If

'Checking to see if the folder name typed in the textbox actually exists
If fso.FolderExists(txtFolder.Text) Then
'Setting the oFolder object to what is in the textbox
Set oFolder = fso.GetFolder(txtFolder.Text)

'Looping through the files in the folder object
For Each oFile In oFolder.Files
'Checking the file extension of the file
If Len(oFile.Name) >= 4 Then
'If the file has a txt file extension then add the files
If UCase(Right(oFile.Name, 4)) = ".TXT" Then
lstFiles.AddItem oFile.Name
End If
End If
Next

Else
MsgBox "This folder does not exist."

End If
End If

'Removing the oFile object from memory
Set oFile = Nothing
Set oFolder = Nothing
Set fso = Nothing

End Sub

Private Sub cmdImport_Click()

Dim i As Long, iPos As Long
Dim sLine As String
Dim sCardNo As String, sFirstName As String, sLastName As String
Dim sRookieCard As String, sAdditionalDescription As String
Dim sYearId As String, sSetId As String, sSubsetId As String

'picImporting.Visible = True
'Me.MousePointer = vbHourglass
Me.Refresh
DoEvents

If lstFileLines.ListCount > 0 Then

m_oConnection.Open

If m_oConnection.State = adStateOpen Then
sSetId = InputBox("What is the Set #?", "Set", "0")
sSubsetId = InputBox("What is the Subset #?", "Set", "0")
sYearId = InputBox("What is the Year Id#?", "Set", "0")

For i = 1 To lstFileLines.ListCount
sLine = lstFileLines.Value(i - 1)
Debug.Print sLine
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sCardNo = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sFirstName = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sLastName = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))

If iPos > 0 Then
sRookieCard = Mid(sLine, 1, iPos - 1)
sLine = Right(sLine, Len(sLine) - iPos)
iPos = InStr(1, sLine, Chr(9))
sAdditionalDescription = Replace(sLine, """", """""")
End If
End If
End If
End If
ImportRecord sCardNo, sFirstName, sLastName, sRookieCard, sAdditionalDescription, sSetId, sSubsetId, sYearId
Next i
m_oConnection.Close
End If
End If

End Sub


Private Sub Form_Load()
m_sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Cards\AnotherTry.mdb"

Set m_oConnection = New ADODB.Connection
m_oConnection.ConnectionString = m_sConnectionString
'Me.lstFileLines.RowSource = ""
'Me.lstFiles.RowSource = ""

End Sub
Private Sub lstFiles_Click()
Dim fso As Scripting.FileSystemObject
Dim oStream As Scripting.TextStream
Dim oFile As Scripting.file

Set fso = New Scripting.FileSystemObject

txtFolder.SetFocus
Set oFile = fso.GetFile(txtFolder.Text & lstFiles.Value)

Set oStream = oFile.OpenAsTextStream(ForReading)

Me.lstFileLines = ""

Do While Not oStream.AtEndOfStream
lstFileLines.AddItem oStream.ReadLine
Loop

oStream.Close

If lstFileLines.ListCount > 0 Then
cmdImport.Enabled = True
End If


End Sub
Private Sub lstFiles_AfterUpdate()
Me.lstFileLines.Requery
End Sub

Private Sub ImportRecord(ByVal sCardNo As String, ByVal sFirstName As String, ByVal sLastName As String, ByVal sRookieCard As String, ByVal sAdditionalDescription As String, ByVal SetId As String, ByVal SubsetId As String, ByVal sYearId As String)
Dim sSQL As String
Dim iSetId As Integer
Dim iSubsetId As Integer
Dim iYearId As Integer

iSetId = Val(sSetId)
iSubsetId = Val(sSubsetId)
iYearId = Val(sYearId)

If Len(sCardNo) <> 0 And Len(sFirstName) <> 0 And Len(sLastName) <> 0 Then
sSQL = "INSERT INTO CARDTEST ([CardNo], [FirstName], [LastName], [RookieCard], [AdditionalDescription], [SetId], [SubsetId], [YearId])VALUES (""" & sCardNo & """, """ & sFirstName & """, """ & sLastName & """, """ & sRookieCard & """, """ & sAdditionalDescription & """, " & iSetId & ", " & iSubsetId & ", " & iYearId & ")"
m_oConnection.Execute sSQL
End If
Debug.Print sSQL

End Sub


Thanks so much
 
Dude, use code tags which'll preserve your indents. Highlight text and click the number sign '#' so you get something like ....
Code: 
Private Sub Test()
  With CodeTags
    .ThingsAreEasierToUnderstand = true
    .YoureMoreLikelyToGetUsefulAssistance = true
  End With
[COLOR="DarkRed"]  Debug.Print "Highlighting an important line with color is useful too"[/COLOR]
End Sub
It just makes helping you out way easier.
Cheers,
 
Hey Lagbolt, Thanks for the wonderful tidbits of information. I would appreciate if you didn't reply to any of my posts, as the two replies that you have provided have not been of any assistance to me, I am pretty sure that I can get some actual help from just about anyone else, DUDE!!
 
OrganMan said:
Hey Lagbolt, Thanks for the wonderful tidbits of information. I would appreciate if you didn't reply to any of my posts, as the two replies that you have provided have not been of any assistance to me, I am pretty sure that I can get some actual help from just about anyone else, DUDE!!
Click to expand...
His first post did highlight an important point that a listbox doesn't have a Text property. It only has a Value or a Column(x) property which you can use to get a value from a listbox.

It does help when code is enclosed in code tags. I also tend to stress this point because we find it difficult to read unindented code. Here's how you do it:
[c o d e]
Your code goes in here
[/ c o d e]

Without spaces and without the colour.
 
Nothing wrong with the lagbolts suggestions. Why are you not using the simple TransferText command to import your text file into a table isntead of the long road you are trying to take?
 
Hey Hudson,

I totally agree with what Lagbolt said but I just found the tone of the email rather sarcastic, and I understand your backing him up since I am a newbie. On a positive note I was able to get everthing working on my own.

Thanks for now guys.
 
I regret that what I wrote landed as sarcastic. That was not my intention.
 
OrganMan said:
Hey Hudson,

I totally agree with what Lagbolt said but I just found the tone of the email rather sarcastic, and I understand your backing him up since I am a newbie. On a positive note I was able to get everthing working on my own.

Thanks for now guys.
Click to expand...

it is considered good forum etiquette to post your solution when you find one.

also, we're not backing lagbolt simply because you are a newbie. we're backing him because he is right.

The more you help us, the more we'll help you. if we can't read your code easily, there is less possibility we'll even try to decipher wher your code might be going wrong.

also, i didn't detect any bad 'tone' in lagbolt's posts. unlike yours.

glad you found a solution, you are obviously competent at coding.
 
Wiklendt, Not sure why you are replying back over two months later. If it is for the solution, I have moved on to other projects since, so it might be a little hard to find the solution for this one.

A few others replied saying that his comments weren't out of line and I accepted that, I just thought that it was a dead issue, I don't see a whole lot of positive out of your comments, sounds like you are just trying to stir the pot.

Everything was left cool back in July why not just leave it at that.

Thanks
 
you didn't sound cool or accepting of anything. sounded like you left in a stomp.

as for two month old post - the internet knows no time - i did a search online and this thread came up. for me it was only a few minutes old.

i can find solutions to projects i tackled several years back. you may want to look at organising your files or using backups to help you there. i find that i'll often want to copy/paste stuff i've done before into new projects - saves heaps of time building on what already exists than re-going everything from scratch.

not stirring the pot, just making things clear.
 
"Hey Hudson,

I totally agree with what Lagbolt said but I just found the tone of the email rather sarcastic, and I understand your backing him up since I am a newbie. On a positive note I was able to get everthing working on my own.

Thanks for now guys.

Doesn't sound like a stomp to me .................


...but we can go on forever lady...
 
You must log in or register to reply here.

Similar threads

P
Solved Access VBA
Replies
4
Views
361
prasadgov
P
B
Outlook - Filtered Unread, Server duplicate emails & think Unread = False not work so errors
Replies
6
Views
282
bignose2
B
D
Default Signature new outlook current user
Replies
17
Views
363
GPGeorge
GPGeorge
R
  • Question Question
Save the names of tables, forms, and reports in a Microsoft Access database to a table named "ObjectNames.
2 3
Replies
42
Views
2,341
Pat Hartman
P
N
Data source changed from a table to a query
Replies
13
Views
291
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom