Connect to Excel file to import data

[Douglas J. Steele]

You shouldn't need to use Automation.

Try just using

Function ImportBidData(FileName As String)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tbl_Temp_Quote1", FileName, False, "Quote1Direct"

End Function

 

[fredg]

Hello,

I have a vba code that will import the data from the excel file. I open the
excel file,import it and close it. But I think I have the problem with
closing it.The VBA works fine, but when I reopen the excel file from the
file inself, it says something about the Read Only, Notify......

Here is my code

Function ImportBidData(FileName As String)
Set excel = CreateObject("excel.Application")

excel.workbooks.Open (FileName)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tbl_Temp_Quote1", FileName, False, "Quote1Direct"

excel.workbooks.Close

excel.Quit

Set excel = Nothing

It is not necessary to actually open the Excel file to do the
importing.

Sub ImportBidData(FileName As String)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tbl_Temp_Quote1", FileName, False, "Quote1Direct"
End Sub

 

[Boon]

Hello,

I have a vba code that will import the data from the excel file. I open the
excel file,import it and close it. But I think I have the problem with
closing it.The VBA works fine, but when I reopen the excel file from the
file inself, it says something about the Read Only, Notify......

Here is my code

Function ImportBidData(FileName As String)
Set excel = CreateObject("excel.Application")

excel.workbooks.Open (FileName)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tbl_Temp_Quote1", FileName, False, "Quote1Direct"

excel.workbooks.Close

excel.Quit

Set excel = Nothing

 

[Boon]

thanks for suggestion. I appologize... I forgot to tell you that the excel
file is sheet-protected. The story is that this excel file is what I sent
out to my suppoliers to fill in the data. I locked the sheet so that they
will not be able to mess up with the format that I pre-set in the file.
Thus, openning the file before importing is the way to go, I guess. If you
have any idea, please advise.

thanks,
Boon