VBA Looping Import Code

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I wrote a VBA code to loop through a folder and look for
any file with a .txt extension and then import those files
into a database that I name during the process.
Everything works fine except for the fact that when I
import more than one table, all the column names are
exactly the same as the first table that is created. Does
anyone have any idea what the problem may be? I can post
the code if you would like.

Thank You
 
Private Sub RenameFiles()

Dim strFolder
Dim strFileName As String
Dim strNewName As String
Dim InputFldr As String

InputFldr = "Type the path of the folder where the
files are located"
strFolder = InputBox(InputFldr)

strFileName = Dir(strFolder & "\*.xls")

Do Until Len(strFileName) = 0

' Only rename the ones with embedded periods.
If strFileName Like "*.*.*" Then

' strip off the ".xls" to get new name
strNewName = Left(strFileName, Len
(strFileName) - 4)

' remove any embedded periods (.)
strNewName = Replace(strNewName, ".", "")

' replace the ".xls" suffix.
strNewName = strNewName & ".txt"

' Rename the file.
Dim fs As FileSystemObject
Dim f As File

Set fs = New FileSystemObject
Set f = fs.GetFile(strFolder & strFileName)


f.Name = strNewName
Set fs = Nothing
Set f = Nothing
End If

' Get next file name.
strFileName = Dir()

Loop

End Sub

'This code is for the import button'

Private Sub Command11_Click()

Call RenameFiles

Dim myfile
Dim mypath
Dim InputMsg As String
Dim InputTblName As String
Dim mytable

'The below code allows you to chose the folder where the
text files are located'
InputMsg = "Type the path of the folder that contains the
files you want to import."
mypath = InputBox(InputMsg)

'The below code allows you to set the table name for the
output'
InputTblName = "Type the name of the table you want to
create."
mytable = InputBox(InputTblName)


myfile = Dir(mypath & "*.txt")
Do While myfile <> ""
'This will import ALL the excel files (one at a time,
but automatically) in this folder.'
DoCmd.TransferText acImportDelim, "Tab_Spec", mytable,
mypath & myfile
myfile = Dir
Loop

End Sub

'This code is for the change extension button'
Private Sub Change_Extension_Click()

On Error GoTo Err_Change_Extension_Click

Dim stDocName As String

stDocName = "RunApp_test"
DoCmd.RunMacro stDocName

Exit_Change_Extension_Click:
Exit Sub

Err_Change_Extension_Click:
MsgBox Err.Description
Resume Exit_Change_Extension_Click

End Sub
 
The data is specific for each table. But thanks for the
help.
-----Original Message-----
Sorry I can't see anything. I thought you might be
Click to expand...
importing the data cell by cell so I could understand if
you had not redimed the column names.
 
Back
Top