Workbooks.open method fails

[Guest]

Hi,

I am trying to open an Excel spreadsheet from Access using the following
code in Access:

Private mobjXL As Excel.Application

Private Sub cmdImportFiles_Click()
Dim wrk As Excel.Workbook
Set mobjXL = New Excel.Application
With mobjXL
.ScreenUpdating = False
.Visible = false
.DisplayAlerts = False
Set wrk = mobjXL.Workbooks.Open("Excel file path and name")
' Do something with the Excel spreadsheet
End With
' error handler
End Sub

I keep getting an error message of 'Method 'Open' of object Workbook failed'
everytime Excel tries to open the spreadsheet (and it can be any
spreadsheet). Any suggestions on how to open the Excel spreadsheet in VBA
without the error message?

Regards
Justin

 

[Jim Rech]

Your code looks okay, although it doesn't look as if you copied in the real
code since the "false" after .Visible is not proper case.
You are specifying an existing file along with an extension? Like
"c:\MyPath\MyFile.xls"

--
Jim Rech
Excel MVP
| Hi,
|
| I am trying to open an Excel spreadsheet from Access using the following
| code in Access:
|
| Private mobjXL As Excel.Application
|
| Private Sub cmdImportFiles_Click()
| Dim wrk As Excel.Workbook
| Set mobjXL = New Excel.Application
| With mobjXL
| .ScreenUpdating = False
| .Visible = false
| .DisplayAlerts = False
| Set wrk = mobjXL.Workbooks.Open("Excel file path and name")
| ' Do something with the Excel spreadsheet
| End With
| ' error handler
| End Sub
|
| I keep getting an error message of 'Method 'Open' of object Workbook
failed'
| everytime Excel tries to open the spreadsheet (and it can be any
| spreadsheet). Any suggestions on how to open the Excel spreadsheet in VBA
| without the error message?
|
| Regards
| Justin

 

[Guest]

Jim thank you for your reply,

I am using the full path with the file extension. The code is just a summary
of my full code. I am trying to convert some Excel files into a text file so
they can be imported into a SQL server database using a DTS package.

The files I get from a client and they always seem to have been corrupted
and recovered by Excel at the clients end as I get a message when I open them
in Excel telling me this. The DTS package won't accept the Excel files
because of this message that pops up when the file is imported, hence the
conversion to a text file.

I was hoping to be able to get Excel to open the files without the message
(hence DisplayAlerts=False) and convert them to a text file. Any suggestions
on how to open the files without the recovery warning messages as it seems to
be this message that is giving me the problems?

Justin

 

[Jim Rech]

Sorry Justin, I don't know how to turn off this error message. Very strange
that you always have this problem with this client's files. Investigating
that problem is the next step I think.

--
Jim Rech
Excel MVP
| Jim thank you for your reply,
|
| I am using the full path with the file extension. The code is just a
summary
| of my full code. I am trying to convert some Excel files into a text file
so
| they can be imported into a SQL server database using a DTS package.
|
| The files I get from a client and they always seem to have been corrupted
| and recovered by Excel at the clients end as I get a message when I open
them
| in Excel telling me this. The DTS package won't accept the Excel files
| because of this message that pops up when the file is imported, hence the
| conversion to a text file.
|
| I was hoping to be able to get Excel to open the files without the message
| (hence DisplayAlerts=False) and convert them to a text file. Any
suggestions
| on how to open the files without the recovery warning messages as it seems
to
| be this message that is giving me the problems?
|
| Justin
|
|
| "Jim Rech" wrote:
|
| > Your code looks okay, although it doesn't look as if you copied in the
real
| > code since the "false" after .Visible is not proper case.
| > You are specifying an existing file along with an extension? Like
| > "c:\MyPath\MyFile.xls"
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > | Hi,
| > |
| > | I am trying to open an Excel spreadsheet from Access using the
following
| > | code in Access:
| > |
| > | Private mobjXL As Excel.Application
| > |
| > | Private Sub cmdImportFiles_Click()
| > | Dim wrk As Excel.Workbook
| > | Set mobjXL = New Excel.Application
| > | With mobjXL
| > | .ScreenUpdating = False
| > | .Visible = false
| > | .DisplayAlerts = False
| > | Set wrk = mobjXL.Workbooks.Open("Excel file path and
name")
| > | ' Do something with the Excel spreadsheet
| > | End With
| > | ' error handler
| > | End Sub
| > |
| > | I keep getting an error message of 'Method 'Open' of object Workbook
| > failed'
| > | everytime Excel tries to open the spreadsheet (and it can be any
| > | spreadsheet). Any suggestions on how to open the Excel spreadsheet in
VBA
| > | without the error message?
| > |
| > | Regards
| > | Justin
| >
| >
| >