Importing Excel data into Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using this logic to import Excel data into Access.

Public Sub ImportProtected(strFile As String, strPassword As String)
Dim oExcel As Object, oWb As Object

Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Import", strFile, -1
oWb.Close
oExcel.Quit
Set oExcel = Nothing
End Sub

It works great but it leaves an instance open of Excel in my task list.
Can someone tell me what is wong with this logic and why it is not actually
exiting Excel?
 
George

This a sample of the code that I use to import data from Excel

' import from the spreadsheet
DoCmd.TransferSpreadsheet acImport, 8, "tbl_cie_raw_data",
"D:\availability.xls", False, "a1:g200"

tbl_cie_raw_data is the table where the data will be held
D:\availability.xls is the excel spreadsheet
a1:g200 is the range in the spreadsheet.

Allan Murphy
Email: (e-mail address removed)
 
Allen,

Can I use this code for the following?

I want to use an Excel spread sheet to populate A list box. This list box,
or drop down box contains a list of users. As this list changes on a regular
basis, I do not want to re-code the data base to add/ delete users.

I have created an excel spreadsheet called tbl_lenders_HeadOffice and have
filled in Column "A" only (from line 1 to line 6)

Here is an amended copy of the code you supplied below:-

' import from the spreadsheet
DoCmd.TransferSpreadsheet acImport, 8, "Lender",
"c:\tbl_lenders_HeadOffice.xls", False, "a1:a6"

It does not seem to work???!!

Regards
 
Back
Top