M
Michael_Speicher
Hello first of all...I am new to this forum,
I have a question..
I have trying to open 2 Excel Files into 2 different Access Tables.
Right now I have this code, but this is where my error message pops up,
here's my code:
Public Function ImportExcelMultipleFiles()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Excel.Application, objWorkbook As Excel.Workbook
Dim colWorksheets As Collection
Dim strPathFile As String, strTable As String
Dim strPassword As String
' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
' Replace C:\Filename.xls with the actual path and filename
strPathFile = "C:\Users\Michael.Speicher\Documents\Comparison\21.12.2009.
CompareUser.xlsx"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "MemberInfo"
' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = vbNullString
blnReadOnly = True ' open EXCEL file in read-only mode
' Open the EXCEL file and read the worksheet names into a collection
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, blnReadOnly,
strPassword) <<<<<<<
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount
' Close the EXCEL file without saving the file, and clean up the EXCEL
objects
objWorkbook.Close False
Set objWorkbook = Nothing
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing
' Import the data from each worksheet into the table
For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount)
& "$"
Next lngCount
' Delete the collection
Set colWorksheets = Nothing
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile
End Function
<<<<<<< shows where the error message is!
Does anybody know why this is happening?
The file I am using right now is .xlsx and I am using Windows Vista with
Office 2007.
Any Help would help.
Regards
Michael
I have a question..
I have trying to open 2 Excel Files into 2 different Access Tables.
Right now I have this code, but this is where my error message pops up,
here's my code:
Public Function ImportExcelMultipleFiles()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Excel.Application, objWorkbook As Excel.Workbook
Dim colWorksheets As Collection
Dim strPathFile As String, strTable As String
Dim strPassword As String
' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
' Replace C:\Filename.xls with the actual path and filename
strPathFile = "C:\Users\Michael.Speicher\Documents\Comparison\21.12.2009.
CompareUser.xlsx"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "MemberInfo"
' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = vbNullString
blnReadOnly = True ' open EXCEL file in read-only mode
' Open the EXCEL file and read the worksheet names into a collection
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, blnReadOnly,
strPassword) <<<<<<<
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount
' Close the EXCEL file without saving the file, and clean up the EXCEL
objects
objWorkbook.Close False
Set objWorkbook = Nothing
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing
' Import the data from each worksheet into the table
For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount)
& "$"
Next lngCount
' Delete the collection
Set colWorksheets = Nothing
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile
End Function
<<<<<<< shows where the error message is!
Does anybody know why this is happening?
The file I am using right now is .xlsx and I am using Windows Vista with
Office 2007.
Any Help would help.
Regards
Michael