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


"marylander" wrote in message
...
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)).Se lect
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


On Aug 3, 5:45 am, "Graham R Seach"
wrote:
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

"marylander" wrote in message

...
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.

On Aug 1, 11:08 am, "Graham R Seach" <gseach@ac



cessmvp_REMOVE.com wrote:
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" wrote in message

...

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

See response in Access forum for ideas.
--
HTH,
Barb Reinhardt



"Graham R Seach" wrote:

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


"marylander" wrote in message
...
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)).Se lect
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


On Aug 3, 5:45 am, "Graham R Seach"
wrote:
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

"marylander" wrote in message

...
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.

On Aug 1, 11:08 am, "Graham R Seach" <gseach@ac



cessmvp_REMOVE.com wrote:
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" wrote in message

...

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -