Problem getting Access to open visible Excel

[jlf81]

Hi,

I am having problems with Access opening an excel file and running a macro, it seems to open excel in a non visible format but I have no idea why?, I am sure I am missing something simple, and would appreciate it if some one could point me in the right direction.

Thanks

Josh
This is the code I am using in access: -

Private Sub updatecompetitorsladderssummary_Click()


Dim stDocName As String
Dim ExcelLocation As String
Dim ExcelName As String
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook


'Change these 2 if the Comp Ladder Summary spreadsheet is moved
ExcelLocation = "M:\Pricing\Products and Pricing\Archive Pricing Papers\"
ExcelName = "Comp Ladder Summary.xls"

'This runs the relevant create table and append queries
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query 4002 Grouped Data for Summary Comp Ladders", acViewNormal, acEdit
DoCmd.SetWarnings True

'Checks if excel is open, and names the current instance as xlapp, if not opens it and names it xlapp
On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
Set xlapp = New Excel.Application
End If

xlapp.Visible = True
Workbooks.Open Filename:=(ExcelLocation & ExcelName), UpdateLinks:=1
Set xlbook = ActiveWorkbook
xlapp.Application.Run "UpdateGraphs"

End Sub

 

[PHV]

Replace this:
Workbooks.Open Filename:=(ExcelLocation & ExcelName), UpdateLinks:=1
Set xlbook = ActiveWorkbook
with this:
Set xlbook = xlapp.Workbooks.Open Filename:=(ExcelLocation & ExcelName), UpdateLinks:=1

ie, always use full qualified objects.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

Err=True only if Err.Number=-1, so rather use:
[tt]If Err<>0 Then[/tt]


combo

 

[PHV]

combo, Err=True only if Err.Number<>0 (ie not false)

 

[jlf81]

Thanks for the help I have managed to get the second bit in no problem, but the first bit

"Set xlbook = xlapp.Workbooks.Open Filename:=(ExcelLocation & ExcelName), UpdateLinks:=1"

is throwing me back the error

"Complie Error: Expected: list separator or )"

Not sure why as it looks correct to me

Thanks

Josh

 

[combo]

After assignment you need:
Set xlbook = xlapp.Workbooks.Open(Filename:=(ExcelLocation & ExcelName), UpdateLinks:=1)

combo

 

[SkipVought]

FYI,

You can use a function thusly, with & without parentheses...

SomeVar = YourFunction(arglist)
YourFunction arglist

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[eVAPor8]

I'd advise porting the "Update Graphs" macro into Access and Automating the lot from there so you have the lot under "one roof".

Also

On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
    Set xlapp = New Excel.Application
End If

Is horribly dangerous. I you have an instance of Excel already running (and possibly batch processing another Macro) all hell will break lose (and that On Error Resume Next doest help).

I'd advice you...

Set xlapp = New Excel.Application
Set xlwbk = xlapp.workbooks.open(strFileNameOfYourWorkbook)
Set xlSht = xlwbk.Sheets("Sheet Name On Tab")
xlapp.visible = true

and then port your macro across to Access. It's easy most of the time. Just "With-block" the macro code so that..

Range("A1").Value = 10 (in Excel)
Range("A4").Font.Bold = True

Becomes

With xlsht
.Range("A1").Value = 10
.Range("A4").Font.Bold = True
End With

You might have to look up the Excel Enums for constants like xlCalculationManual but it looks like you're early-binding so it shouldn't be a problem. Heh...

Until you deploy it that is and you'll HAVE to...

 

[jlf81]

Thanks,

That all looks to have got it working, I have port the marco, but out of interest why was it worth doing this?

Thanks

Josh