Issues when opening excel through access

[marylander]

I have created the code to open an excel spreadsheet through access,
HOWEVER, in that excel spreadsheet there is an "Auto_Open" macro that
I want to run. Only one part of the macro runs upon the opening of
the Excel Spreadsheet through Access. If I open the spreadsheet
outside of access, the macro runs without issues.

Does Access control macros if it opens the spreadsheet, if so, is the
a way to transfer control to excel until the spreadsheet is closed.

thank you

 

[a a r o n _ k e m p f]

you can call macros in excel from access

 

[marylander]

Part of the macro in Excel, when opened, is to update and then copy
data from one sheet and paste special it on another sheet. when I
open it through Access, the data is updated however the copy step is
never executed. Would Access be blocking this step?

 

[Graham R Seach]

I can't think of any reason why the macro wouldn't completely run when the
sheet is opened via Access. You might check to see if there are any
conditions (for example, something that requires Excel to be visible or have
mouse/keyboad focus perhaps).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[marylander]

The only issue I see is that it seems that once Access opens Excel .
Excel does not switch to a Sheet2 once it copies the information from
Sheet1. when I open Excel directly the macro runs without fail.

 

[Graham R Seach]

Hi Mary,

I'm assuming you're launching an instance of Excel in code. In that case,
when you instantiate Excel, make it visible immediately. Only then open the
spreadsheet. With any luck, this'll resolve the issue.

Set objXL = New.Excel.Application
objXL.Visible = True
DoEvents '***Very important

Set objXLWorkbook = objXL.Workbooks.Open(... ...)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


The only issue I see is that it seems that once Access opens Excel .
Excel does not switch to a Sheet2 once it copies the information from
Sheet1. when I open Excel directly the macro runs without fail.

 

[marylander]

Thanks Graham,

I changed the order however, the macros still stops on the first
command. The updating of the sheet.

Here is the code I have in Excel

Sheets("Rawdata").Select <------- linked to a table in Access
Range ("A1").Select
ActiveWorkbook.Refreshall
Range ("A2"). Select
Range (Selection,ACtiveCell.SpecialCells(xlLastCell)).Select
Selection.copy
Sheets("Transposed").Select
Range("B1").Select
Selection.PasteSpecial. Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _;=Falste,Transpose:=True
Sheets("Report").Select


It gets to the refresh comand and then stops

 

[Graham R Seach]

Hi Excel folk,

This is a cross-post from the Access group. As you can see from the short
history, the OP (Mary) is having trouble with an Auto_Open procedure which
seems to run fine when the spreadsheet is opened normally, but seems to fail
after RefreshAll when opened via automation. Would some of you fine folk
please take a look?

Mary: As you can see, I've cross-posted this to the Excel newsgroup, because
I don't speak Excel. Hopefully one of the experts in there can provide some
insight.

The only thing I can see is that there's a space after the dot ( Range
("A2"). Select ), but perhaps that's just a transcription problem. Mary,
would you check this please?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham,

I changed the order however, the macros still stops on the first
command. The updating of the sheet.

Here is the code I have in Excel

Sheets("Rawdata").Select <------- linked to a table in Access
Range ("A1").Select
ActiveWorkbook.Refreshall
Range ("A2"). Select
Range (Selection,ACtiveCell.SpecialCells(xlLastCell)).Select
Selection.copy
Sheets("Transposed").Select
Range("B1").Select
Selection.PasteSpecial. Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _;=Falste,Transpose:=True
Sheets("Report").Select


It gets to the refresh comand and then stops

 

[Barb Reinhardt]

This is what I see

1) Have you set up a reference to Excel within your Access code. I do it
from the Excel VBE using TOOLS - > References

2) Have you defined an XLApp

XLApp = GetObject("Excel Application") 'I think that's the syntax

3) When you work in another application besides the one running the macro,
you need let the program know which application you're working in. If you're
calling a macro from Access, it doesn't know that Worksheets is an excel
worksheet.

Try something like this

Dim aWB = Excel.workbook
Dim RawDataWS as Excel.Worksheet
Dim TransposeWS as Excel.WOrksheet
Dim myRange as Excel.range


Set aWB = XLApp.activebook 'I never remember if it's activebook or
activeworkbook
Set RawDataWS = awb.worksheets("RawData")

aWB.Refreshall '<~~I've never seen Refreshall in Excel VBA. Do you mean
calculate?

Set myRange =RawDataWS.Range ("A2") 'I prefer not to use .select at all.
It slows down execution
Set myRange = RawDataWS.Range (myRange,myRange.SpecialCells(xlLastCell))
myRange.Copy
Set TransposeWS = aWB.Sheets("Transposed")
TransposeWS.range("B1").PasteSpecial. Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _;=Falste,Transpose:=True
aWB.Sheets("Report").Select

This is untested!. But it gives you an idea of things to look for.

 

[Barb Reinhardt]

See response in Access forum for ideas.

 

[Barb Reinhardt]

I'm guessing you're talking about "Workbook_Open" code?