Excel to Access

[Shahrukh Ali]

Hello,




I have got 1400+ Excel workbooks with different columns and a lot of rows. I want to combine them all into one. I think Access Append Query is gonna work fine for me as I am not familiar with coding and vba. But the problem is, even if I go for the append query option it's gonna take days or maybe weeks. Is there any way to bring all excel workbooks in access at once? and combine them all in one query?


Well, this is just my thought. If anyone has any better way to get it done, please suggest.


Cheers!

[ranman256]

put all the files into 1 folder, then make a form to run this code.
click a run button to begin this code, the code suppies the folder where all the worbooks are..

sub btnRun_click()
ImportAllFilesInDir( "c:\folder\")
end sub

Public Sub ImportAllFilesInDir(ByVal pvDir)
Dim vFil, vTargT
Dim i As Integer
dim sSql As String
Dim db 'As Database
Dim fso
Dim oFolder, oFile

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

sTbl = "xlFile"
Set db = CurrentDb
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)

For Each oFile In oFolder.Files

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

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

Set db = Nothing
Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
DoCmd.SetWarnings True
Exit Sub

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

[John_G]

Do all the Excel workbooks have the same column structure, in terms of the number of columns and their names?

Note: The Transferspreadsheet command should be: DoCmd.TransferSpreadsheet acImport, , sTBL, vFil , True
(extra comma for a blank second parameter)

If not, then ranman256's code won't work, at least not properly. If your column names are not the same in all spreadsheets, then the fifth parameter for HasFieldNames = True will cause all sorts of problems.

Can you describe the spreadsheet format a bit more, please?

[ssanfu]

Are there more than 1 worksheet in each workbook??

You might check out Ken Snell's site:
Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

There is a lot of code for Importing/Exporting Excel to Access