Importing data from multiple spreadsheets into a single table 2

[platypus71]

Ok, I searched for posts and found very little actual code to give me an idea on how to do this.

I have several spreadsheets, all with different names, all will be generated multiple times and have new names each time. All spreadsheets have the same column headers and columns. I need a way to import the data from *.xls into my existing table.

I am assuming I need something like:

Function AppendSpreadsheets()
    Dim strfile As String

    DoCmd.TransferSpreadsheet acImport, 8, Table1, "C:\Repository\" & strfile, True


End Function

I wasn't sure if String was the right variable to use for the filename. I also wasn't sure how to get it to cycle through the files in that folder.

Any help would be appreciated.

 

[PhoenixDon]

I do something similar with TXT files, so I hope this is what you are looking for.

Set fs = Application.FileSearch
With fs
.LookIn = "C:\REPOSITORY"
.FileName = "*.XLS"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
FilesWereFound = True
End If

For i = 1 To .FoundFiles.Count

Next i

If FilesWereFound = True Then
DoCmd.TransferSpreadsheet acImport, 8, Table1, "C:\Repository\" & strfile, True
Else
MsgBox "There were no files found to import!", vbInformation, "Error!"
End If
End With

 

[platypus71]

Code does find the right files.
It breaks on the DoCmd.TransferSpreadsheet line though.

Reason being, the actual file name isn't passed to "strfile".

Need help getting the actual file name into this line to make it work. Not sure how to do that.

 

[PhoenixDon]

My bad, got ahead of myself. Try this.


Set fs = Application.FileSearch
With fs
.LookIn = "C:\REPOSITORY"
.FileName = "*.XLS"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
FilesWereFound = True
End If

For i = 1 To .FoundFiles.Count
strFile = .FoundFiles(i)
Next i

If FilesWereFound = True Then
DoCmd.TransferSpreadsheet acImport, 8, Table1, strfile, True
Else
MsgBox "There were no files found to import!", vbInformation, "Error!"
End If
End With

 

[PhoenixDon]

Probably should be this too.

For i = 1 To .FoundFiles.Count
strFile = .FoundFiles(i)
If FilesWereFound = True Then
DoCmd.TransferSpreadsheet acImport, 8, Table1, strfile, True
Next i

 

[platypus71]

I am getting Run-time error '2495'
The action or method requires a Table Name argument.

What is a table name argument?

The line the debugger brings up is the DoCmd.TransferSpreadsheet line.

Chris

 

[Remou]

Be aware that file search will return any XLS shortcuts. As dcrowe99 has it:

Set fs = Application.FileSearch
With fs
    .LookIn = "C:\REPOSITORY"
    .FileName = "*.XLS"
    If .Execute > 0 Then
        MsgBox "There were " & .FoundFiles.Count & " file(s) found."
        For i = 1 To .FoundFiles.Count
            strFile = .FoundFiles(i)
            'Create table name from file name
            strTable = Mid(strFile, InStrRev(strFile, "\") + 1)
            strTable = Left(strTable, Len(strTable) - 4)
            DoCmd.TransferSpreadsheet acImport, 8, strTable, strFile, True
        Next
    Else
        MsgBox "There were no files found to import!", vbInformation, "Error!"
    End If
End With

 

[platypus71]

I don't need it to create a table name from the file name.
I omitted that part of the code.
I have an existing table with the correct fields in it (all spreadsheets will have the same fields). I keep getting an error that it requires a Table Name arguement in the DoCmd line.

Here is the current iteration of the code:

Function AppendSpreadsheets()
    Dim strfile As String
    Set fs = Application.FileSearch
 With fs
  .LookIn = "C:\Assessment"
  .FileName = "*.XLS"
  If .Execute > 0 Then
   MsgBox "There were " & .FoundFiles.Count & " file(s) found."
  For i = 1 To .FoundFiles.Count
    strfile = .FoundFiles(i)
    DoCmd.TransferSpreadsheet acImport, 8, tblTable1, strfile, True
  Next
  
  Else
   MsgBox "There were no files found to import!", vbInformation, "Error!"
  End If
  
 End With
    
End Function

It counts fine. But it breaks on the DoCmd line.

 

[Remou]

Table name is a string:
[tt]DoCmd.TransferSpreadsheet acImport, 8, "tblTable1", strfile, True[/tt]

 

[platypus71]

DOH! Silly "s.

Thanks.
Chris