Import Multiple Text files in Folder

[Guest]

I'm trying to automate the import of several tab-delimited text files all
stored in a common folder, and append the data to tblCPG. I've got this code
that compiles, but when I run it, it skips right over the code to the MsgBox
- nothing imports. I'm using Access '07.

Sub MassImport()

Dim strPath As String
Dim strFileName As String
Dim dbf As Database

Set dbf = CurrentDb
' Set the path to the directory where the files will be.
strPath = "C:\Documents and Settings\kphillips\My
Documents\Kirk\Accounting\Navision\Gross Revenue"
strFileName = Dir(strPath & ".txt") ' Retrieve the first entry.
Do While strFileName <> "" ' Start the loop.
DoCmd.TransferText acImportDelim, "CPG Import Spec", "tblCPG", strPath &
strFileName, True
strFileName = Dir ' Get next entry.
Loop

MsgBox "Objects imported successfully!", vbInformation, "Import Status"

End Sub

Help?

 

[Guest]

If the sub goes right to the msgbox, I suspect Do While strFileName <> ""
is returning "" for the first loop.
Try putting MsxBox strFileName right after the Do line, and see what it
returns - if it's blank, that's your problem

 

[Guest]

My bad - it's a simple syntax error. I'm missing the last "\" after Gross
Revenue in strPath = "C:\Documents and Settings\kphillips\My
Documents\Kirk\Accounting\Navision\Gross Revenue"

 

[jerkyjpnes]

How about modifying this bit of code for Spreadsheets. Right now, i'm using a bit of code that I found in newsgroups here somewhere.

Public Sub Import_From_Excel()
'Macro Loops through the specified directory (strPath)
'and imports ALL Excel files to specified table in the Access
'Database.

Const strPath As String = "N:LNPCommercial ServicesTRACKINGDaily RPM Snapshots" 'Directory Path
Dim strSheetName As String 'Worksheet Name
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
'Loop through the folder & build file list
strFile = Dir(strPath & "*.xls")
'strFileName = InputBox("Enter the name of the file.")
strSheetName = "Sheet 1"
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
'strFile = Dir()
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called MyTable
For intFile = 1 To UBound(strFileList)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import", strPath & strFile, True, , False
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

When I run this, it ends up with a 'Run Time Error: 3051'

When I hit debug, it highlights the :"DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import", strPath & strFile, True, , False" line, which I presume is where the code stopped running.

I'm sure you can tell I am somewhat green to VBA, but I am usually ok with figuring things out. Any help is greatly apprechated.


thanks,

PS. I have verified that I have all the needed permissions to the files in the directory.