VBA cannot open excel sheet if another excel is open!!

[akashraog]

Hi All,
I am using VBA to pupulate Ms-Excel from Ms-Access. Once done i activate the excel sheet, so that i can see the data in excel.
Now, if another excel sheet is open, then the excel-sheet that i populated does not get activated(neither is it hidden in the background). It just doesn't show up or open.

My code to open excel :-
Set oApp = CreateObject("Excel.Application")
Set xlWrkBk = GetObject("C:\Excel Form.xls")
Set xlSht = xlWrkBk.Worksheets(1)
'code to populate excel
'other code
xlWrkBk.Application.Visible = True

Can anyone help me with this?

:beerchug:
Thanks
Akash

[Bob Phillips]

Not tested, but try this



On Error Resume Next
Set oApp = GetObject(, "Excel.Application")
On Error GoTo 0
If oApp Is Nothing Then

Set oApp = CreateObject("Excel.Application")
If Not oApp Is Nothing Then

oApp.Visible = True
Set xlWrkBk = oApp.Workbooks.Open("C:\Excel Form.xls")
Set xlSht = xlWrkBk.Worksheets(1)
'code to populate excel
'other code
End If
End If

[GTO]

Greetings akashraog,

You shouldn't have to both CreateObject and GetObject... As long as you know the file (WorkBook) exists, this should work, regardless of whether Excel currently exists of not. The wacky (to me leastwise) problem is that you have to set the window's visibility.

try:
Sub PopCurFile()
Dim wb As Object
Dim sh As Object
Dim r As Object

On Error Resume Next
'// For testing, change to suit. //
Set wb = GetObject("D:\120508\TEMP 010209\newfile.xls")
If Err.Number > 0 Then
MsgBox "File not found", 0, ""
Err.Clear
Exit Sub
End If

wb.Parent.Visible = True

wb.Windows(1).Visible = True

wb.WorkSheets(1).Range("A1").Value = "Yo Adrian! I can see me!"

wb.Save

End Sub

Hope that helps,

Mark

[akashraog]

Thanks Mark for the quick response, that worked.

:beerchug: Cheers:beerchug: