Access SQL for Importing Worksheet from Multiple Workbooks (1 Viewer)

[sarahd09]

Hi everyone,

This is my first posting, and I am new to this Forum and Access.

My ask today is for the help of writing an SQL script in Access for automating the importing process of 52 worksheets from different workbooks? Maybe looping would be a must?

Situation: I am trying to import 52 worksheets from 52 workbooks to an Access database, i.e. the worksheets with same worksheet name in 52 weekly workbooks (these workbooks have multiple worksheets). While I am doing it manually, just wonder if there is a way to import it via SQL in Access. Below are some background information:

Path of the 52 workbooks: C:\Users\jenny\Documents\2014\
Workbook Name: Vendor Income WEddmmyyyy.xlsm (only the date "ddmmyyyy" is variable in this file naming convention, which means there are 52 dates)
Worksheet Name: Transaction
Column Headers: while the headers may vary in order, or missing one or two in some worksheets. The naming is consistent. I would like to define below fields while importing for its type: Shop (Number), Product Category (Number), PO (Number), Amount (Number), Apid (Text), RR (Number), Item (Number), Quantity (Number), Last Year (Number), Ref Num (Text), Date (Number), Type (Text), Fund Description (Text)

Notice that in above brackets are data types for those fields. I would like to define data type in the SQL script, with the purpose that Access will do the data cleansing by generating error report if any data failed to be imported.

I am estimating a total of 2 million roles for all of these 52 worksheets altogether, by doing a simple Union querry after the importing.

Brainstorming: Not sure if this is the best method, but I am not doing it by an append or union query right away for the purpose of the ability of tracing back to each worksheet.

Solution, and new ideas of doing things are all welcome!

Thank you!
Jane

 

[Ranman256]

here is code to run to scan the folder, then import every workbook in that folder.
be sure to set your table name and folder.
This can be run from a macro...
usage:
runcode ImportAllFilesInDir "c:\folder\"

Public function ImportAllFilesInDir(ByVal pvDir)
Dim vFile, vTargT
Dim i As Integer
dim sSql As String
Dim fso
Dim oFolder, oFile

On Error GoTo errImp
If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"

sTbl = "xlFile"

Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)

For Each oFile In oFolder.Files

    vFile = pvDir & oFile.Name
    If InStr(vFile, ".xls") > 0 Then      'ONLY DO EXCEL FILES  

       DoCmd.TransferSpreadsheet acImport, sTBL, vFile , True
    endif   
Next

Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
DoCmd.SetWarnings True
Exit function

errImp:
MsgBox Err.Description, vbCritical, "ImportAllFilesInDir():" & Err
End Sub

 

[sarahd09]

Thank you.

My knowledge about macro is very limited. I guess I will take my time to understand it and try to run it in VBA.

May post more question later