Help - need macro to convert excel files to access files

[nikkai]

Hi

My first post and badly in need of an answer:

Is there a way to make a script that will consolidate 300+ excel files into one gigatic access database? (ie: Automated convert then append?).

Sample macros would be *HUGELY* appreciated.

Please email me an answer as I have no clue how I would find my own post again.

Thanks

 

[johnwm]

Hi and welcome to Tek-Tips. To get the best from the forum read faq222-2244. Although it's in the VB forum it will give guidelines for other stuff:

1. Read the forum to make sure that you're posting in the correct forum. For this question forum707 is probably best. (para 3 of the faq)

2. Don't double post - it wastes time and space (para 5 of the faq)

3. We don't do email (para 12 of the faq)

4. Samples may be offered, but full code is not expected (para 8,9 10)

Good luck in forum707 (after you've done your research)!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[willir]

nikkai

To transfer from the spreadsheet file. Research TransferText or TransferSpreadsheet for options and usage.

DoCmd.TransferText acImportDelim, , NewTable, Source.csv
OR
DoCmd.TransferSpreadsheet acImport, , NewTable, Source.xls

For 300+ files...
Use the FSO (you may have to add the reference, Alt-F11 for VB coding window, then from the menu, "Tools" -> "References" and "look for Microsoft Scripting Runtime")

Following code loads a combo box with a list of files in a folder.

Dim fso As Scripting.FileSystemObject
Dim fso_folder As Folder
Dim fso_file As File
Dim dir_path As Folder
Dim strFilepathFrom As String

'From the form
strFilepathFrom = Nz(Me.txtFileLocation,"")
strFiles = ""

If Len(strFilepathFrom) Then

    Set fso = New FileSystemObject
    Set fso_folder = fso.GetFolder(strFilepathFrom)
    
    If fso.FolderExists(strFilepathFrom) Then
    
        For Each fso_file In fso_folder.Files
            strFiles = strFiles & ";" & Nz(fso_file.Name, "")
        Next fso_file
        
        strFiles = Right(strFiles, Len(strFiles) - 1)
    
        If Len(strFiles) Then
            Me.cmbFileName.RowSource = strFiles
        End If
        
    End If
    
    Set fso = Nothing

End If

Instead of loading the combo box, you should be able to use the TransferSpreadsheet or TransferText method.

A gotcha -- the spreadsheet has to have the same format / fields, and is not forgiving with bad data. Troubleshooting a run time data error on the Tranfer method can be problematic, for me anyway.

Richard