Opening a specific excel sheet with access 2000

[Adien]

I know how to open a blank excel sheet but can't figure out how to
open a specific one. Any suggestions?

 

[Pat Hartman]

I'm assuming that you mean via automation since the TransferSpreadsheet
method only works with a specific spreadsheet.

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
sPathName = "C:\Hartman\BrokerLicenses"
sDocName = QUOTE & sPathName & "\qCleanUpMissingBeazleyTradeID2.xls" &
QUOTE

Set ExcelWorkbook = ExcelApp.Workbooks.Add(sDocName) 'opens a copy of
the workbook

Set ExcelWorkbook = ExcelApp.Workbooks.Open(sDocName) 'opens the
workbook itself - check out the options with this method

End Sub

 

[Adien]

Pat,

Thanks for the responce I'll give a shot, but it looks like it'll do
what I needed!

Adien

 

[Adien]

"Dim ExcelApp As Excel.Application"

Is giving me an "User defined type not defined error"

 

[Pat Hartman]

You need to add a reference to Excel. Go to Tools/References (from the VBA
window). Scroll down to Microsoft Excel and check the box. I am using
E2003. I don't know if this code would need to be different in other
versions.

 

[Adien]

Well that cleared up the first part.

However

"I'm assuming that you mean via automation since the
TransferSpreadsheet
method only works with a specific spreadsheet. "

I didn't (and don't) understand what you meant by "via automation" but
seems like I better find out since now I'm getting a "Automation Error
- The server threw an exception"

 

[Pat Hartman]

What exactly do you want to do with spreadsheet once you have it open? The
code I gave you allows you to open the spreadsheet (usually you would set
visible to false because your code will perform the modifications rather
than the user) and manipulate it with code. You can do things like place
values in particular cells or set formatting. Basically, anything you can
do with the Excel GUI, you can do programmatically once you figure out the
correct objects, methods, and properties.

 

[Adien]

Pat,

Basically I would be inserting data in cells and some of the other
cells would do calculations based on the initial input. Though the
hope was that the button would basically be a hyperlink to excel, a
quick shortcut from the program to enter the next set of data in the
daily routine rather then going to look for it.

Thanks for all the help thus far
Adien