Public Function Import_System_Access_Reports()
Dim strFolder As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strFile As String
Dim strTable As String
Dim strExtension As String
Dim lngFileType As Long
Dim strSQL As String
Dim strFullFileName As String
Dim varPieces As Variant
With Application.FileDialog(4) ' msoFileDialogFolderPicker
If .Show Then
strFolder = .SelectedItems(1)
Else
MsgBox "No folder specified!", vbCritical
Exit Function
End If
End With
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If
strFile = Dir(strFolder & "*.xls*")
Do While strFile <> ""
lngPos = InStrRev(strFile, ".")
strTable = "RawData" '<- this could be a constant instead of a variable
Set db = CurrentDb()
' make the UPDATE a parameter query ...
strSQL = "UPDATE [" & strTable & "] SET FileName=[pFileName]" & vbCrLf & _
"WHERE FileName Is Null OR FileName='';"
Set qdf = db.CreateQueryDef(vbNullString, strSQL)
strFile = Dir(strFolder & "*.xls*")
Do While Len(strFile) > 0
varPieces = Split(strFile, ".")
strExtension = varPieces(UBound(varPieces))
Select Case strExtension
Case "xls"
lngFileType = acSpreadsheetTypeExcel9
Case "xlsx", "xlsm"
lngFileType = acSpreadsheetTypeExcel12Xml
Case "xlsb"
lngFileType = acSpreadsheetTypeExcel12
End Select
strFullFileName = strFolder & strFile
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=lngFileType, _
TableName:=strTable, _
Filename:=strFullFileName, _
HasFieldNames:=True ' or False if no headers
' supply the parameter value for the UPDATE and execute it ...
qdf.Parameters("pFileName").Value = strFile
qdf.Execute dbFailOnError
Loop
'Move to the next file
strFile = Dir
Loop
'Clean up
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
'rstTable.Close
Set rstTable = Nothing
End Function
ASKER
Option Compare Database
Option Explicit
Private Const TABLE_NAME As String = "RawData"
Public Sub Test()
Dim Folder As String
With Application.FileDialog(4) ' msoFileDialogFolderPicker
If .Show Then
Folder = .SelectedItems(1)
ImportSystemAccessReports Folder
Else
MsgBox "No folder specified!", vbCritical
End If
End With
End Sub
Private Function ImportFile(AFile As String) As Boolean
On Local Error GoTo LocalError
Dim FilenameParts() As String
Dim Extension As String
Dim SpreadsheetType As Long
FilenameParts() = Split(AFile, ".")
Extension = FilenameParts(UBound(FilenameParts))
Select Case Extension
Case "xls"
SpreadsheetType = acSpreadsheetTypeExcel9
Case "xlsx", "xlsm"
SpreadsheetType = acSpreadsheetTypeExcel12Xml
Case "xlsb"
SpreadsheetType = acSpreadsheetTypeExcel12
Case Else
Exit Function
End Select
DoCmd.TransferSpreadsheet acImport, SpreadsheetType, TABLE_NAME, AFile, True
ImportFile = True
Exit Function
LocalError:
MsgBox "ImportFile:" & Err.Number & " - " & Err.Description
End Function
Public Function ImportSystemAccessReports(AFolder As String)
Const SQL_UPDATE As String = _
"UPDATE {TableName} " & _
"SET FileName = [pFileName] " & _
"WHERE Len(Trim(FileName & '')) = 0;"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim File As String
Set db = CurrentDb()
Set qdf = db.CreateQueryDef(vbNullString, Replace(SQL_UPDATE, "{TableName}", TABLE_NAME))
If Right(AFolder, 1) <> "\" Then
AFolder = AFolder & "\"
End If
File = Dir(AFolder & "*.xls*")
Do While File <> ""
If ImportFile(AFolder & File) Then
qdf.Parameters("pFileName").Value = File
qdf.Execute dbFailOnError
End If
File = Dir
Loop
Set qdf = Nothing
Set db = Nothing
End Function
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
TRUSTED BY
strFile = Dir(strFolder & "*.xls*")
»bp