×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help - need macro to convert excel files to access files

Help - need macro to convert excel files to access files

Help - need macro to convert excel files to access files

(OP)
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

RE: Help - need macro to convert excel files to access files

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

RE: Help - need macro to convert excel files to access files

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.

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close