Opening a Specific Excel Sheet from Access

[Adien]

Well as the name states I'm tyring to open a specific excel sheet from
access 2000 for editing another poster helped me get this far

Option Compare Database
Option Explicit

Public Const QUOTE = """"

Public Sub testExcel()
Dim sDocName As String
Dim sPathName As String
Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook


'Fire up excel
Set ExcelApp = New Excel.Application
ExcelApp.Visible = True

Set ExcelWorkbook = ExcelApp.Workbooks.Open(sDocName)
sPathName = "C:\Hartman\BrokerLicenses"
sDocName = QUOTE & sPathName &
"\qCleanUpMissingBeazleyTradeID2.xls" & QUOTE

this is all editted appropriately for my situation but now I'm getting
a "Automation Error - Server thre an exception"

Any suggestions would be a appareciated

Adien

 

[boblarson]

Well, first of all you are trying to open your sDocName before you set a
value to it.

Second, you don't need the QUOTE parts around the path.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Adien]

Weird, not sure why it pasted like that, but this is what it's
supposed to look like

Option Compare Database
Option Explicit

Private Sub btnCash_Click()

Const QUOTE = """"

Dim sDocName As String
Dim sPathName As String
Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook


'Fire up excel
Set ExcelApp = New Excel.Application
ExcelApp.Visible = True
sPathName = "C:\Sebastian\Sales"
sDocName = QUOTE & sPathName & "\Sales.xls" & QUOTE

Set ExcelWorkbook = ExcelApp.Workbooks.Open(sDocName)


End Sub

So the quotes arn't needed?

 

[boblarson]

Nope, you don't need the extra quotes.

You just need:

Option Compare Database
Option Explicit

Private Sub btnCash_Click()

Dim sDocName As String
Dim sPathName As String
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook


'Fire up excel
Set xlApp = New Excel.Application
xlApp.Visible = True
sPathName = "C:\Sebastian\Sales"
sDocName = sPathName & "\Sales.xls"

Set xlWB = ExcelApp.Workbooks.Open(sDocName)
xlWB.Worksheets("YourWorkSheetNameHere").Select
' or you can use xlWB.Worksheets(1).Select to select by index (1 based
instead of 0 based)
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Adien]

Bob,

Alterered accordingly but I'm still getting the "Automation error -
server threw an exception"

Thanks,
Rick

 

[boblarson]

Which line highlights if you click the DEBUG button on the error dialog?
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Adien]

Set xlWB = xlApp.Workbooks.Open(sDocName)

 

[boblarson]

Make sure your directories and file exist. Make sure you have Excel
installed. I took your code and modified it slightly to fit my directories
and used this as a sample and it opened just fine:

Dim sDocName As String
Dim sPathName As String
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook


'Fire up excel
Set xlApp = New Excel.Application
xlApp.Visible = True
sPathName = "D:\Temp\Sales"
sDocName = sPathName & "\Sales.xls"

Set xlWB = xlApp.Workbooks.Open(sDocName)
xlWB.Worksheets(2).Select

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Adien]

Odd. The xls file and the path are correct (full path C:\Sebastian
\Sales\Sales.xls). Excel is installed, I've been opening this
particular file as well as others regurlarly. The excel library in
references is checked. Just to dbl check, that line of code is usable
in access 2000? I don't know whatr else might make the difference.

 

[boblarson]

Yeah, I've done all of that within Access 2000. Maybe you should import all
of your stuff into a new, blank MDB file as sometimes corruption can creep
in.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Adien]

Just finished switching everything into a fresh mdb and too no avail
still getting that automation error

 

[boblarson]

If you want to email me the db I can take a look.

(e-mail address removed)

Take out the numbers and you have my email address.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Adien]

PS on the bright side it did compress the file size from 25MB to 1MB
lol