Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Excel Workbooks versus Excel Workbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Excel Workbooks versus Excel Workbooks
Specify the location for the copied sheet...
Workbooks("byname.asp").Sheets("OvertimeSummary"). Copy _ Befo= Workbooks("LocalBook").Worksheets(1) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Ken" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Excel Workbooks versus Excel Workbooks
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 _ Befo=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" wrote in message ... Specify the location for the copied sheet... Workbooks("byname.asp").Sheets("OvertimeSummary"). Copy _ Befo= Workbooks("LocalBook").Worksheets(1) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Ken" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Excel Workbooks versus Excel Workbooks
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" 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 _ Befo=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" wrote in message ... Specify the location for the copied sheet... Workbooks("byname.asp").Sheets("OvertimeSummary"). Copy _ Befo= Workbooks("LocalBook").Worksheets(1) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Ken" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating Workbooks from multiple links Workbooks | Excel Worksheet Functions | |||
Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
Display 2 formulas from source workbooks to destination workbooks | Excel Discussion (Misc queries) | |||
suddenly my excel workbooks are "shared workbooks" | Excel Discussion (Misc queries) | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming |