Pozdrav. Treba da importujem excel fajlove, formatirane na odredjeni nacin, u jednu tabelu u Accessu. Da li imate neki predlog kako bi se to moglo automatski izvesti (ne preko import wizarda), s obzirom da ce biti potrebno unositi oko 100 fajlova dnevno...
importovanje excel fajlova u access
Zaključano
Zakačeno
Pogledaj DoCmd.TransferSpreadsheet
Potpis
All beer is good. Some beer is better.
Mislio sam na ovo:
Code:
Private Sub cmdImportFiles_Click()
' Convert the Excel files to a text file so that they can then be imported
Dim intFileCount As Integer
On Error GoTo ErrorHere
With Application.FileSearch
.NewSearch
.LookIn = "Folder of Excel files path name" ' You could use the FileDialog object to let the user select the folder with the Excel spreadsheets in them orjust hard code it.
.SearchSubFolders = False ' set to true if you want subfolders to be searched
.FileName = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute Then
For intFileCount = 1 To .FoundFiles.COUNT
' You might need to change some of these options depending on your version of Excel, range names, field names etc
DoCmd.TransferSpreadsheet transferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="MyTable", FileName:=.FoundFiles(intFileCount), HasFieldNames:=True
Next intFileCount
Else
MsgBox "No Excel files were found", vbInformation, "No Excel Files Found"
GoTo ExitHere
End If
End With
ExitHere:
Exit Sub
ErrorHere:
MsgBox "Error In: Form '" & Me.Name & "'" & vbCrLf _
& "Procedure: cmdImportFiles_Click" & _
vbCrLf & "Error Code: " & Err.Number & _
vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
Resume ExitHere
End Sub
Private Sub cmdImportFiles_Click()
' Convert the Excel files to a text file so that they can then be imported
Dim intFileCount As Integer
On Error GoTo ErrorHere
With Application.FileSearch
.NewSearch
.LookIn = "Folder of Excel files path name" ' You could use the FileDialog object to let the user select the folder with the Excel spreadsheets in them orjust hard code it.
.SearchSubFolders = False ' set to true if you want subfolders to be searched
.FileName = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute Then
For intFileCount = 1 To .FoundFiles.COUNT
' You might need to change some of these options depending on your version of Excel, range names, field names etc
DoCmd.TransferSpreadsheet transferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="MyTable", FileName:=.FoundFiles(intFileCount), HasFieldNames:=True
Next intFileCount
Else
MsgBox "No Excel files were found", vbInformation, "No Excel Files Found"
GoTo ExitHere
End If
End With
ExitHere:
Exit Sub
ErrorHere:
MsgBox "Error In: Form '" & Me.Name & "'" & vbCrLf _
& "Procedure: cmdImportFiles_Click" & _
vbCrLf & "Error Code: " & Err.Number & _
vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
Resume ExitHere
End Sub