Please help,

When I attempt to use the Excel workbook open method with
options in MS Access, I get an error message saying it
can't find the file.
I want to programmatically open an excel file that is
password protected and daily transfer this file to
Access. (I have the password).


Dim xlapp As Excel.Application
Dim xlworkbook As Excel.Workbooks
Dim strFileName As String

Set xlapp = CreateObject("Excel.application")
xlapp.Workbooks.Open ("strfilename,,,password")
xlapp.Visible = True
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, tbl1, strFileName

xlapp.Quit
Set xlapp = Nothing

Gwen,

Change
xlapp.Workbooks.Open ("strfilename,,,password")
to
xlapp.Workbooks.Open strfilename,,,password ' no quotes or
parens


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Gwen" wrote in message
...
Please help,

When I attempt to use the Excel workbook open method with
options in MS Access, I get an error message saying it
can't find the file.
I want to programmatically open an excel file that is
password protected and daily transfer this file to
Access. (I have the password).


Dim xlapp As Excel.Application
Dim xlworkbook As Excel.Workbooks
Dim strFileName As String

Set xlapp = CreateObject("Excel.application")
xlapp.Workbooks.Open ("strfilename,,,password")
xlapp.Visible = True
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, tbl1, strFileName

xlapp.Quit
Set xlapp = Nothing

Hi Gwen,

Get rid of the double quotes around the arguments to the Workbooks.Open
method:

xlapp.Workbooks.Open ("strfilename,,,password")

should be

xlapp.Workbooks.Open (strFileName,,,"password")

I'm assuming "password" is a literal string here. If it's a constant or
variable, get rid of the double quotes around it too.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Gwen" wrote in message
...
Please help,

When I attempt to use the Excel workbook open method with
options in MS Access, I get an error message saying it
can't find the file.
I want to programmatically open an excel file that is
password protected and daily transfer this file to
Access. (I have the password).


Dim xlapp As Excel.Application
Dim xlworkbook As Excel.Workbooks
Dim strFileName As String

Set xlapp = CreateObject("Excel.application")
xlapp.Workbooks.Open ("strfilename,,,password")
xlapp.Visible = True
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, tbl1, strFileName

xlapp.Quit
Set xlapp = Nothing