Import CSV files

[Rich1968]

I keep getting an error "Could not find object". Then it references the file CAPCMS08062012. which is the first file in the folder.

I modified this code from an example I got on this "AWESOME" site. I really mean that!

I have multiple files to import on a regular basis. This would save me a great deal of time.

Thanks
Rich1968


Option Compare Database
Function Load_Core_Data()

Dim NextFile, ImportFile, FileCriteria, ctr As Variant

FileCriteria = "C:\Core\*.CSV"

' create field with path and filename to import'
NextFile = Dir(FileCriteria)

' Check we have something to import
If NextFile = "" Then
MsgBox "No files to import", , "Error"
Exit Function
End If
ctr = 0

' Import each file that meets the criteria '*.CSV'
While NextFile <> ""

' count files imported
ctr = ctr + 1

' add the path to the returned filename
ImportFile = FileCriteria & NextFile

' Import file into table
DoCmd.TransferText acImport, , "Core Import Specfication", ImportFile, True

' get another file if it exists
NextFile = Dir()

Wend

MsgBox ctr & " files imported", , "Core Data Import"
End Function

 

[AccessMSSQL]

Is the error occurring at the docmd.transfertext line? I was able to run the code with no errors (excluding that line). Have you tried stepping through the code?

 

[Rich1968]

Yes the error is occouring on the DoCmd line. doing the step through this afternoon. I'll keep you posted.

Rich1968

 

[Rich1968]

Ok here's my latest code. I have taken out the *.csv reference in the FileCriteria line and get message no files to import.

If I put FileCriteria="C:\Core\*.csv" (This is the path to my data) I get run time error 3011 can't find the object. and it highlights my DoCmd line. Any help is appreciated.

Thanks
Rich1968


Option Compare Database
Function Load_Core_Data()

Dim NextFile, ImportFile, FileCriteria, ctr As Variant

FileCriteria = "C:\Core"

' create field with path and filename to import'
NextFile = Dir(FileCriteria)

' Check we have something to import
If NextFile = "" Then
MsgBox "No files to import", , "Error"
Exit Function
End If
ctr = 0

' Import each file that meets the criteria '*.CSV'
While NextFile <> ""

' count files imported
ctr = ctr + 1

' add the path to the returned filename
ImportFile = FileCriteria & NextFile

' Import file into table
DoCmd.TransferText acImport, , "Core Import Specfication", ImportFile, True

' get another file if it exists
NextFile = Dir()

Wend

MsgBox ctr & " files imported", , "Core Data Import"
End Function

 

[boblarson]

Your problem is that you need to use the wildcard in the first part to get the file but then you need to get the wildcard out of there afterwards.

Function Load_Core_Data()
    Dim NextFile, ImportFile, FileCriteria, ctr As Variant
 
    [B][COLOR=red]FileCriteria = "C:\Core\*.csv"[/COLOR][/B]
 
    ' create field with path and filename to import'
    NextFile = Dir(FileCriteria)
 
    ' Check we have something to import
    If NextFile = "" Then
        MsgBox "No files to import", , "Error"
        Exit Function
    End If
 
    ctr = 0
 
    ' Import each file that meets the criteria '*.CSV'
    While NextFile <> ""
        ' count files imported
        ctr = ctr + 1
 
        ' add the path to the returned filename
        ImportFile =[B][COLOR=red] Left(strFile, InStr(1, strFile, "*") - 1)[/COLOR][/B] & NextFile
 
        ' Import file into table
        DoCmd.TransferText acImport, , "Core Import Specfication", ImportFile, True
        ' get another file if it exists
        NextFile = Dir()
 
    Wend
 
    MsgBox ctr & " files imported", , "Core Data Import"
 
End Function

 

[Rich1968]

Bob,

Thanks for the help. I tried this line and got an invalid call or procedure.

ImportFile = Left(strFile, InStr(1, strFile, "*") - 1) & NextFile

Thanks

Rich

 

[boblarson]

Sorry, I used my own test code instead of replacing with your name:

ImportFile = Left(strFile, InStr(1, strFile, "*") - 1) & NextFile

Should be:

ImportFile = Left(FileCriteria, InStr(1, strFile, "*") - 1) & NextFile

And I should have seen your declarations and made changes:

Dim NextFile, ImportFile, FileCriteria, ctr As Variant

should be

Dim NextFile As String, ImportFile As String, FileCriteria As String, ctr As Variant

When you put the declarations on one line you can't do in VBA like you can in VB6. You have to include the type or else they will become variants.

 

[Rich1968]

Hey Bob,

Thanks for the help. Sorry to say i'm still getting the invalid call for

ImportFile = Left(FileCriteria, InStr(1, strFile, "*") - 1) & NextFile

This should work. I even changed the declarations. same problem.

Thanks

Rich1968

 

[boblarson]

What version of Access are you using?

 

[Rich1968]

I'm using 2003

 

[boblarson]

Post the revised code you now have but still isn't working.

 

[Rich1968]

Access 2003

 

[boblarson]

Access 2003

No, I said post the REVISED CODE which you are using after you did the fixes I suggested but you say it still errors out.

 

[Rich1968]

Thanks for the help Bob, Here's my currect code: Error code 5 invailid procedure or call pops when I run this

Function Load_Core_Data()
Dim NextFile As String, ImportFile As String, FileCriteria As String, ctr As Variant

FileCriteria = "C:\Core\*.csv"

' create field with path and filename to import'
NextFile = Dir(FileCriteria)

' Check we have something to import
If NextFile = "" Then
MsgBox "No files to import", , "Error"
Exit Function
End If

ctr = 0

' Import each file that meets the criteria '*.CSV'
While NextFile <> ""
' count files imported
ctr = ctr + 1

' add the path to the returned filename
ImportFile = Left(FileCriteria, InStr(1, strFile, "*") - 1) & NextFile

' Import file into table
DoCmd.TransferText acImport, , "Core Import Specfication", ImportFile, True
' get another file if it exists
NextFile = Dir()

Wend

MsgBox ctr & " files imported", , "Core Data Import"

End Function

 

[boblarson]

Go to the VBA Window and look to see what the "Project Name" is.

See here for where to look for that:

 

[Rich1968]

The Project Name is CMS(CMS Management)

 

[boblarson]

Hmm, so that isn't the problem.

When all logical items have been ruled out, I've found doing a DECOMPILE sometimes fixes those unexplained problems.

See here for how to do it:
http://www.granite.ab.ca/access/decompile.htm
and make a copy as a backup BEFORE you do it.

 

[Rich1968]

Bob,

Finally got it right, I appreciate your help! Here's my final code:

Option Explicit
Function Import_multiple_csv_files()

Const strPath As String = "C:\Core\" 'Directory Path
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 & "*.csv")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Function
End If

For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelim, , "Core Raw Data", strPath & strFileList(intFile)

Next
MsgBox UBound(strFileList) & " Files were Imported"

Kill "C:\core\*.csv"

End Function