| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a worksheet to a new workbook
I am trying to copy a worksheet to a new workbook and then close the
current workbook, so I can work in the worksheet. This is what I've written. Worksheets("Source_Worksheet").Activate ActiveSheet.Copy ActiveSheet.Paste ActiveSheet.SaveAs "c:\My Excel Files\" & wsname & ".xls" Workbooks("Source_Workbook.xls").Close When I try the SaveAs, I get a run-time 1004 error for a bad path or file name. (wsname is a string data type.) David |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a worksheet to a new workbook
hi
try this instead replace the line ActiveSheet.SaveAs "c:\My Excel Files\" & wsname & ".xls" with Application.Dialogs(xlDialogSaveAs).Show regards FSt1 rega " wrote: I am trying to copy a worksheet to a new workbook and then close the current workbook, so I can work in the worksheet. This is what I've written. Worksheets("Source_Worksheet").Activate ActiveSheet.Copy ActiveSheet.Paste ActiveSheet.SaveAs "c:\My Excel Files\" & wsname & ".xls" Workbooks("Source_Workbook.xls").Close When I try the SaveAs, I get a run-time 1004 error for a bad path or file name. (wsname is a string data type.) David |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a worksheet to a new workbook
On Feb 4, 5:21*pm, FSt1 wrote:
hi try this instead replace the line ActiveSheet.SaveAs "c:\My Excel Files\" & wsname & ".xls" with Application.Dialogs(xlDialogSaveAs).Show regards FSt1 rega Rega, That helped me identify a problem. Thank you. David |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a worksheet to a new workbook
Worksheets("Source_Worksheet").Copy 'to a new workbook
activesheet.parent.saveas filname:="c:\My Excel Files\" & wsname & ".xls" Workbooks("Source_Workbook.xls").Close " wrote: I am trying to copy a worksheet to a new workbook and then close the current workbook, so I can work in the worksheet. This is what I've written. Worksheets("Source_Worksheet").Activate ActiveSheet.Copy ActiveSheet.Paste ActiveSheet.SaveAs "c:\My Excel Files\" & wsname & ".xls" Workbooks("Source_Workbook.xls").Close When I try the SaveAs, I get a run-time 1004 error for a bad path or file name. (wsname is a string data type.) David -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a worksheet to a new workbook
On Feb 4, 5:25*pm, Dave Peterson wrote:
Worksheets("Source_Worksheet").Copy 'to a new workbook activesheet.parent.saveas filname:="c:\My Excel Files\" & wsname & ".xls" Workbooks("Source_Workbook.xls").Close " wrote: I am trying to copy a worksheet to a new workbook and then close the current workbook, so I can work in the worksheet. This is what I've written. Worksheets("Source_Worksheet").Activate ActiveSheet.Copy ActiveSheet.Paste ActiveSheet.SaveAs "c:\My Excel Files\" & wsname & ".xls" Workbooks("Source_Workbook.xls").Close When *I try the SaveAs, I get a run-time 1004 error for a bad path or file name. (wsname is a string data type.) David -- Dave Peterson Dave, Thanks for the suggestion. Unfortunately, for some reason I'm being sent to: c:\Program Files\Common Files\System\MSMAPI\1033 Why is that? And how can I redirect the file location. (Chdir and Chdrive don't seem to be doing the trick) Thanks, David |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a worksheet to a new workbook
First, I had a typo. Sorry about that!
activesheet.parent.saveas filEname:="c:\My Excel Files\" & wsname & ".xls" (Missed an E in FileName:=...) Do you have a C: drive on this pc? Is there a folder named "\my Excel files\" on that C: drive? What does the wsname variable hold? ==== And upon reflection, I'd be more explicit in the .saveas line: activesheet.parent.saveas _ filename:="c:\My Excel Files\" & wsname & ".xls", _ fileformat:=xlworkbooknormal " wrote: On Feb 4, 5:25 pm, Dave Peterson wrote: Worksheets("Source_Worksheet").Copy 'to a new workbook activesheet.parent.saveas filname:="c:\My Excel Files\" & wsname & ".xls" Workbooks("Source_Workbook.xls").Close <<snipped Dave, Thanks for the suggestion. Unfortunately, for some reason I'm being sent to: c:\Program Files\Common Files\System\MSMAPI\1033 Why is that? And how can I redirect the file location. (Chdir and Chdrive don't seem to be doing the trick) Thanks, David -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a worksheet to a new workbook
Dave,
Thanks a lot. First of all I was really stupid and copied your code directly, not even noticing the missing "e." So thanks for that correction. It looks like my mistake was using a spreadsheet that I'd included in an e-mail, that's why I couldn't save things. I must have used it once and kept on opening it via the "Documents" menu from the Start button. So I propogated the mistake. David On Feb 4, 6:25*pm, Dave Peterson wrote: First, I had a typo. *Sorry about that! activesheet.parent.saveas filEname:="c:\My Excel Files\" & wsname & ".xls" (Missed an E in FileName:=...) Do you have a C: drive on this pc? Is there a folder named "\my Excel files\" on that C: drive? What does the wsname variable hold? ==== And upon reflection, I'd be more explicit in the .saveas line: activesheet.parent.saveas _ * * filename:="c:\My Excel Files\" & wsname & ".xls", _ * * fileformat:=xlworkbooknormal |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Saving a Worksheet/Workbook with VALUES ONLY | Excel Discussion (Misc queries) | |||
| Saving Worksheet/Workbook via CommandButton | Excel Programming | |||
| Saving into 1 workbook as a different worksheet | Excel Worksheet Functions | |||
| When saving workbook only the first worksheet gets saved. | Excel Worksheet Functions | |||
| Saving a worksheet out of a workbook | Excel Programming | |||
Hybrid Mode
