Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
During a macro, I will move data to a new workbook. Then I will to do
a Save-as to name the new book. What I need to know is the coding to do this Save-as in the current folder without regard to the drive letter. I don't know the drive letter a user will have these files in, nor will I necessarily know the folders they will be in. But, I want the new workbook to be in the same folder as the original. Example, from the original file (which contains the macro), a new workbook is created. Data from the original is copied then pasted in the new workbook. This stuff I know how to do.... At this point I need help. I need to instruct the macro to do a Save- as using the file name of Test.Xls. No path, no drive letter. I've looked at old posts, and cannot find this. Thanks j.o. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
Jeff
Try Sub Macro2() ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & "Book2.xls" End Sub or something along that line. Ken On Oct 12, 2:54*pm, jeff wrote: During a macro, I will move data to a new workbook. Then I will to do a Save-as to name the new book. *What I need to know is the coding to do this Save-as in the current folder without regard to the drive letter. I don't know the drive letter a user will have these files in, nor will I necessarily know the folders they will be in. But, I want the new workbook to be in the same folder as the original. Example, from the original file (which contains the macro), a new workbook is created. Data from the original is copied then pasted in the new workbook. This stuff I know how to do.... At this point I need help. I need to instruct the macro to do a Save- as using the file name of Test.Xls. * *No path, no drive letter. I've looked at old posts, and cannot find this. Thanks j.o. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
'NewWorkbook' below is your new workbook created with the likes of: Workbooks.add Set Activeworkbook= NewWorkbook 'Thisworkbook' is a reserved word referring to the workbook the code is in. So try this: NewWorkbook.saveas Thisworkbook.Path & "\test.xls" -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143554 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
Ken;523100 Wrote: Jeff Try Sub Macro2() ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & "Book2.xls" End Sub or something along that line. Ken There might be a problem with 'ActiveWorkbook.Path' ken.. the activeworkbook hasn't got a path yet since it's never been saved! -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143554 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
Good point. ThisWorkBook should take care of that issue as you
amended. Ken On Oct 12, 3:44*pm, p45cal wrote: Ken;523100 Wrote: Jeff Try Sub Macro2() ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & "Book2.xls" End Sub or something along that line. Ken There might be a problem with 'ActiveWorkbook.Path' ken.. the activeworkbook hasn't got a path yet since it's never been saved! -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143554 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
On Oct 12, 2:44*pm, p45cal wrote:
Ken;523100 Wrote: Jeff Try Sub Macro2() ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & "Book2.xls" End Sub or something along that line. Ken There might be a problem with 'ActiveWorkbook.Path' ken.. the activeworkbook hasn't got a path yet since it's never been saved! -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143554 Thanks for the heads up. Good point. What I had planned was to do a Save prior to this coding just to make sure the path was established. j.o. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
On Oct 13, 6:51*am, jeff wrote:
On Oct 12, 2:44*pm, p45cal wrote: Ken;523100 Wrote: Jeff Try Sub Macro2() ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & "Book2.xls" End Sub or something along that line. Ken There might be a problem with 'ActiveWorkbook.Path' ken.. the activeworkbook hasn't got a path yet since it's never been saved! -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143554 Thanks for the heads up. Good point. What I had planned was to do a Save prior to this coding just to make sure the path was established. j.o.- Hide quoted text - - Show quoted text - I can get this to copy and paste ok. But, I still can't get it to save to the same path as the original workbook. Most of the variations shown below copy and paste ok, but they save the new workbook in the root directory. If somebody can show me what I'm doing wrong, I would appreciate it. Thanks j.o. Sub Macro3() Windows("Vehicle gas & main records.xls").Activate ActiveWorkbook.Save Sheets("V 2").Select ActiveSheet.Copy Dim ActWkbk As Workbook Set ActWkbk = ActiveWorkbook ' Set ActiveWorkbook = NewWorkbook ' Object Required 'Tried these variations to get it to save as Test.xls in the ' same directory as the original workbook. ' ActWkbk.SaveAs ThisWorkbook.Path & "test.xls" ' this one copies ok, but saves to root as MISCTEST.xls 'ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & "Test.xls" 'copies ok - saves to root as Test.xls ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "Test.xls" 'copies ok - saves to root as Test.xls ' ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "Test.xls" 'copies ok - saves to root as Test.xls 'ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "Test.xls" '****this will rename original file as Test.xls - it copies data ok to new file, ' but leaves that as Book*.xls ALSO - file is saved in root directory. Windows("Vehicle gas & main records.xls").Activate Sheets("Menu").Select End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
If both the code and the sheet 'V 2' is in the workbook called 'Vehicle gas & main records.xls' then this code should do it: Sub blah() ThisWorkbook.Sheets("V 2").Copy ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Test.xls" ThisWorkbook.Activate Sheets("Menu").Select 'might not need this line if the Menu sheet was already the active sheet before the above lines are executed. End Sub If the code and sheet to be copied are not in that workbook, tell us which file the code is in and I'll post again. A variation of the above to be used if you've got more processing to do to the new workbook: Sub blah2() Dim NewWkbk As Workbook ThisWorkbook.Sheets("V 2").Copy Set NewWkbk = ActiveWorkbook NewWkbk.SaveAs ThisWorkbook.Path & "\Test.xls" ThisWorkbook.Activate Sheets("Menu").Select 'may not be needed. 'If you've more stuff to add to the new workbook you 'can refer to it as 'NewWkBk' within this macro. End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143554 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
On Oct 13, 6:32*pm, p45cal wrote:
If both the code and the sheet 'V 2' is in the workbook called 'Vehicle gas & main records.xls' then this code should do it: Sub blah() ThisWorkbook.Sheets("V 2").Copy ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Test.xls" ThisWorkbook.Activate Sheets("Menu").Select 'might not need this line if the Menu sheet was already the active sheet before the above lines are executed. End Sub If the code and sheet to be copied are not in that workbook, tell us which file the code is in and I'll post again. A variation of the above to be used if you've got more processing to do to the new workbook: Sub blah2() Dim NewWkbk As Workbook ThisWorkbook.Sheets("V 2").Copy Set NewWkbk = ActiveWorkbook NewWkbk.SaveAs ThisWorkbook.Path & "\Test.xls" ThisWorkbook.Activate Sheets("Menu").Select 'may not be needed. 'If you've more stuff to add to the new workbook you 'can refer to it as 'NewWkBk' within this macro. End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143554 This is what I was looking for. Excellent job! I appreciate your help. Thanks j.o. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
On Oct 14, 6:44*am, jeff wrote:
On Oct 13, 6:32*pm, p45cal wrote: If both the code and the sheet 'V 2' is in the workbook called 'Vehicle gas & main records.xls' then this code should do it: Sub blah() ThisWorkbook.Sheets("V 2").Copy ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Test.xls" ThisWorkbook.Activate Sheets("Menu").Select 'might not need this line if the Menu sheet was already the active sheet before the above lines are executed. End Sub If the code and sheet to be copied are not in that workbook, tell us which file the code is in and I'll post again. A variation of the above to be used if you've got more processing to do to the new workbook: Sub blah2() Dim NewWkbk As Workbook ThisWorkbook.Sheets("V 2").Copy Set NewWkbk = ActiveWorkbook NewWkbk.SaveAs ThisWorkbook.Path & "\Test.xls" ThisWorkbook.Activate Sheets("Menu").Select 'may not be needed. 'If you've more stuff to add to the new workbook you 'can refer to it as 'NewWkBk' within this macro. End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143554 This is what I was looking for. Excellent job! I appreciate your help. Thanks j.o.- Hide quoted text - - Show quoted text - If I could bother you again. There was 1 more thing. I thought this would be easier to figure out. As part of the new file name, I wanted to reference a range name from the original workbook. Here's what I tried: Sub DeleteVehicleV2() ThisWorkbook.Sheets("V 2").Copy 'ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Test.xls" 'this is original - works 'This line is what I tried, to use range name as the new file name. ActiveWorkbook.SaveAs ThisWorkbook.Path & Range("TempNumber").Value 'this will save to the root directory, and adds the path as part of file name, along with range value. 'Instead of Excel \ Vehicle \ V2.xls in Vehicle directory, it gives me \ Excel \ Vehicle V2.xls in the Excel 'directory. 'The Vehicle directory is where the original file is, and where the new file should go. However, just as you set 'up before, I don't want any reference to directory names in the macro. 'I tried variations of this, but could not get the syntax correct. ThisWorkbook.Activate Sheets("Menu").Select End Sub Thanks again, j.o. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
Try replacing ActiveWorkbook.SaveAs ThisWorkbook.Path & Range("TempNumber").Value with ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Range("TempNumber").Value -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143554 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save-as without stating path
On Oct 14, 10:01*am, p45cal wrote:
Try replacing ActiveWorkbook.SaveAs ThisWorkbook.Path & Range("TempNumber").Value with ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Range("TempNumber").Value -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143554 You are awesome. It would have taken me a year to stumble on that solution. Thank you very much. j.o. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing & Stating The Nearest Matching | Excel Discussion (Misc queries) | |||
Stating an area in vba | Excel Programming | |||
My IF statement is stating False but if I manual calculate it it | Excel Discussion (Misc queries) | |||
save as path | Excel Discussion (Misc queries) | |||
Stating ranges in formulae | Excel Programming |