Switching focus from access to excel 1

[dbanker]

Hello all,

I am writing code that opens an Excel file from Access and places previously acquired data into certain fields of the Excel sheet. It works fine the first time it is run but if you run it again without first closing Access then reopening it Excel is visible but not the workbook. The code I wrote is as follows:

'Check to see if Excel is already running
Function ExcelInstance() As Boolean
Const ERR_APP_NOTRUNNING As Long = 429
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err = ERR_APP_NOTRUNNING Then
Set xlApp = New Excel.Application
End If
End Function

xlWB = "C:\Midwest\Office\Midwest_MDB\Small_Worksheet_Delaware.xls"
Workbooks.Open Filename:=xlWB
With xlApp
.Visible = True
With ActiveSheet
.Range("ClientName").Value = GName
.Range("C6").Value = Date
.Range("C5").Value = ContactName
.Range("H6").Value = ContactNumber
.Range("C4").Select
End With
End With
At the end of the code I also have the lines:
xlApp.Quit
Set xlApp = Nothing
to zero out the varible for Excel
If anyone has an answer for this please help me out. Thanks

 

[PHV]

Always use full qualified objects:
xlWB = "C:\Midwest\Office\Midwest_MDB\Small_Worksheet_Delaware.xls"
[highlight]xlApp.[/highlight]Workbooks.Open Filename:=xlWB
With xlApp
.Visible = True
With [highlight].[/highlight]ActiveSheet
.Range("ClientName").Value = GName
.Range("C6").Value = Date
.Range("C5").Value = ContactName
.Range("H6").Value = ContactNumber
.Range("C4").Select
End With
End With

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

 

[dbanker]

Thanks, Ill take your advise but unfortunately it still didn't solve the problem.