Web Excel Workbooks versus Excel Workbooks

[Ken]

I am having a problem copying a sheet from a web based spreadsheet to a
local workbook. When I try and run a macro locally and go to the web based
sheet it sometimes does not recognize the web based sheet. I can run a
macro and add a sheet to the web based spreadsheet, move data from the first
sheet to the new sheet, format, etc; the new sheet. Results looks good. I
then want to copy the new sheet to a local workbook and save the sheet with
all its data and formating. The web based workbook is named "byname.asp"
the original sheet is "byname". I have added a sheet named
"OvertimeSummary".

I am trying to copy the sheet with the command:

Workbooks("byname.asp").Sheets("OvertimeSummary").Copy

If I run the Macro from the local workbook, either calling the Macro from
the menu or with a button on the spreadsheet, it makes a copy of the sheet
as a new sheet within the web based workbook. If I run the Macro from the
VBA editor, it makes a new local workbook "Book1" with the sheet just as I
want.

I don't want the have to run from the VBA editor!

What causes the difference? Also, I can not "Activate" the web based
workbooks. Not allowed.........

Ken

 

[Jim Cone]

Specify the location for the copied sheet...

Workbooks("byname.asp").Sheets("OvertimeSummary").Copy _
Before:= Workbooks("LocalBook").Worksheets(1)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Ken"
<[email protected]>
wrote in message
I am having a problem copying a sheet from a web based spreadsheet to a
local workbook. When I try and run a macro locally and go to the web based
sheet it sometimes does not recognize the web based sheet. I can run a
macro and add a sheet to the web based spreadsheet, move data from the first
sheet to the new sheet, format, etc; the new sheet. Results looks good. I
then want to copy the new sheet to a local workbook and save the sheet with
all its data and formating. The web based workbook is named "byname.asp"
the original sheet is "byname". I have added a sheet named
"OvertimeSummary".

I am trying to copy the sheet with the command:

Workbooks("byname.asp").Sheets("OvertimeSummary").Copy

If I run the Macro from the local workbook, either calling the Macro from
the menu or with a button on the spreadsheet, it makes a copy of the sheet
as a new sheet within the web based workbook. If I run the Macro from the
VBA editor, it makes a new local workbook "Book1" with the sheet just as I
want.

I don't want the have to run from the VBA editor!
What causes the difference? Also, I can not "Activate" the web based
workbooks. Not allowed.........
Ken

 

[Ken]

Thanks for the suggestion; this is code I should be using anyway. I tried
it and it does produce a sheet within the workbook I am running the Macro
from, or any workbook I point to, but only if I run the Macro from within
VBA. Again; If I call the Macro from the menu or run it from a button; I
get a Run-time error '1004'. Another interest feature is the
ActiveWindow.WindowState changes to xlNormal from xlMaximize in the
workbook.sheet the macro is running from. This does not occur if the macro
runs from VBA. Very odd. The only code in the macro is:

Sub MoveSheet()
Dim CurrentWorkbook As Variant
CurrentWorkbook = ActiveWorkbook.Name
Workbooks("byname.asp").Sheets("OvertimeSummary").Copy _
Before:=Workbooks(CurrentWorkbook).Worksheets(1)
End Sub

The CurrentWorkbook is the Macro Workbook.
The "byname.asp" is a web based Excel file in IE 6.0

Ken

 

[Jim Cone]

Some things to try ...
The code should be in a standard module not in the
ThisWorkbook module or a module associated with a sheet.

If you are using a button from the Control Toolbox,
try setting the "TakeFocusOnClick" property to False.

Also, try making worksheets(1) the active sheet and selecting
a cell on it (as the first part of your code).

Note: a workbook name is a string and "CurrentWorkbook"
can be declared as a String.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Ken" <[email protected]>
wrote in message
Thanks for the suggestion; this is code I should be using anyway. I tried
it and it does produce a sheet within the workbook I am running the Macro
from, or any workbook I point to, but only if I run the Macro from within
VBA. Again; If I call the Macro from the menu or run it from a button; I
get a Run-time error '1004'. Another interest feature is the
ActiveWindow.WindowState changes to xlNormal from xlMaximize in the
workbook.sheet the macro is running from. This does not occur if the macro
runs from VBA. Very odd. The only code in the macro is:

Sub MoveSheet()
Dim CurrentWorkbook As Variant
CurrentWorkbook = ActiveWorkbook.Name
Workbooks("byname.asp").Sheets("OvertimeSummary").Copy _
Before:=Workbooks(CurrentWorkbook).Worksheets(1)
End Sub

The CurrentWorkbook is the Macro Workbook.
The "byname.asp" is a web based Excel file in IE 6.0

Ken