Open any excel files in access

[kapaw]

Hi,

I would like to know how to open any excel files from any directory and extract specific columns and copy it to an Access table. Can I use wildcards in the vba code? This is the code I used in Excel to open any excel files but the code doesn't work when I tried using it on Access:

Sub btnOpenExcel
Dim vFile As Variant

'Showing Excel Open Dialog Form
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)

'If Cancel then exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
End If

'Open selected file
Workbooks.Open vFile

End Sub

 

[AccessNub]

If you are trying to import xls files into Access it can be done fairly easily, here is the code I use to Import files and move them to an archive folder then add the file name to an import log table. If any of the import fails, it will undo.

Function ImportAllFiles()
On Error GoTo Error_Handle
Dim StrFile As String, StrPath As String, StrArchive As String

StrPath = File Path
StrArchive = Archive Path

'goto directory
ChDir StrPath

'import the files
Do
'Find the first xls file
StrFile = Dir("File*.xls")
If StrFile = "" Then Exit Function
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, 8, "tbl_TableName", StrPath & StrFile, True, ""
DoCmd.SetWarnings True

'Move it to an Archive folder
Name StrPath & StrFile As StrArchive & StrFile

'add file to import log
CurrentDb.Execute "INSERT INTO tbl_ImportLog ( ImportDate, ImportFileName, Imported )" & _
                    "SELECT Date(), '" & StrFile & "', -1"
Loop

Error_Handle:
CurrentDb.Execute "DELETE tbl_TableName.*, tbl_TableName.ImportDate FROM tbl_TableName" & _
                "WHERE (((tbl_TableName.ImportDate)=Date()))"

    
StrErrorSQL = "SELECT tbl_ImportLog.ImportDate, tbl_ImportLog.ImportFileName, tbl_ImportLog.Imported FROM tbl_ImportLog WHERE tbl_ImportLog.ImportDate=Date()"
Dim Rst As DAO.Recordset
Set Rst = CurrentDb.OpenRecordset(StrErrorSQL, dbOpenDynaset)
Do Until Rst.EOF
    Name StrArchive & Rst!ImportFileName As StrPath & StrFile
Rst.Delete
Rst.MoveNext
Loop
Rst.Close

End Function

 

[The_Doc_Man]

AccessNub offers one way to do this and it is valid.

The other way is to create an application object for Excel, then you can use that object to open a workbook (with wildcard filenames). You make a specific sheet the active sheet, then you can do things with the appropriate references in your application such that Access can use Excel objects, you can "directly" open and read the spreadsheet in VBA code.

For simple imports, AccessNub's suggestion is probably better. For really complex imports, you might need a little code underlying your activities.

 

[kapaw]

Thanks AccessNub!! I tried putting the code on vb but this lines : StrPath = File Path
StrArchive = Archive Path are red and will say syntax error. I'm still a noob when it comes to access. Any suggestions on what I need to add? Thanks again!!!

If you are trying to import xls files into Access it can be done fairly easily, here is the code I use to Import files and move them to an archive folder then add the file name to an import log table. If any of the import fails, it will undo.

Function ImportAllFiles()
On Error GoTo Error_Handle
Dim StrFile As String, StrPath As String, StrArchive As String

StrPath = File Path
StrArchive = Archive Path

'goto directory
ChDir StrPath

'import the files
Do
'Find the first xls file
StrFile = Dir("File*.xls")
If StrFile = "" Then Exit Function
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, 8, "tbl_TableName", StrPath & StrFile, True, ""
DoCmd.SetWarnings True

'Move it to an Archive folder
Name StrPath & StrFile As StrArchive & StrFile

'add file to import log
CurrentDb.Execute "INSERT INTO tbl_ImportLog ( ImportDate, ImportFileName, Imported )" & _
                    "SELECT Date(), '" & StrFile & "', -1"
Loop

Error_Handle:
CurrentDb.Execute "DELETE tbl_TableName.*, tbl_TableName.ImportDate FROM tbl_TableName" & _
                "WHERE (((tbl_TableName.ImportDate)=Date()))"

    
StrErrorSQL = "SELECT tbl_ImportLog.ImportDate, tbl_ImportLog.ImportFileName, tbl_ImportLog.Imported FROM tbl_ImportLog WHERE tbl_ImportLog.ImportDate=Date()"
Dim Rst As DAO.Recordset
Set Rst = CurrentDb.OpenRecordset(StrErrorSQL, dbOpenDynaset)
Do Until Rst.EOF
    Name StrArchive & Rst!ImportFileName As StrPath & StrFile
Rst.Delete
Rst.MoveNext
Loop
Rst.Close

End Function

 

[AccessNub]

StrPath = File Path
StrArchive = Archive Path

These 2 need to be set by you, StrPath could be C:\Temp and StrArchive could be C:\Temp\Archive, or wherever you want to get the files from and put the files to.

 

[kapaw]

Thanks AccessNub!!! I have also find a code that creates a dialog box and select the file from there:

Option Compare Database
Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Function LaunchCD(strform As Form) As String
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = strform.Hwnd
sFilter = "Excel Files (*.xl*)" & Chr(0) & "*.xl*" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\"
OpenFile.lpstrTitle = "Select a file using the Common Dialog DLL"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "A file was not selected!", vbInformation, _
"Select a file using the Common Dialog DLL"
Else
LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
End If
End Function

 

[fatalmusic]

Dear AccessNub,

I'm using your function and it works as far as the importing & moving goes, but gives an error on the Error Handle (how ironic )

Error_Handle:
CurrentDb.Execute "DELETE testingBeatport.*, testingBeatport.ImportDate FROM testingBeatport" & _
                "WHERE (((testingBeatport.ImportDate)=Date()))"

Where it says 'testingBeatport' i assumed i had to rename the tbl_TabelName to my import table's name (hence testingBeatport)

What did I do wrong here?

The error is:

Run-time error '3131'

Syntax error in FROM clause

Hope you can shine a light on the syntax mistake
thanks in advance!
Iris