Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As and new workbook new name used in existing code
With this code in a standard module in a workbook named "Copy of Vehicle Quotation Internal V7a" and I save it as a workbook named "My New Workbook 1" how can I get the code to now reference the new name.
Where in the code below this line of code: Set wbSource = Workbooks("Copy of Vehicle Quotation Internal V7a.xlsm") Will the equivalent of this: Set wbSource = Workbooks("My New Workbook 1.xlsm") I tried using a variable where Dim wbName As WorkBook is set to ActiveWorkbook.Name with Set wbSource = Workbooks(wbName & ".xlsm") but it errors out wanting an object or type mismatch depending on either Dim wbName As Syring or Dim wbName As WorkBook Thanks. Howard Function IsFileOpen(FileName As String) Dim iFilenum As Long Dim iErr As Long On Error Resume Next iFilenum = FreeFile() Open FileName For Input Lock Read As #iFilenum Close iFilenum iErr = Err On Error GoTo 0 Select Case iErr Case 0: IsFileOpen = False Case 70: IsFileOpen = True Case Else: Error iErr End Select End Function Sub MyVQICopy() Dim wbSource As Workbook Dim wbDest As Workbook If Not IsFileOpen("Income Report v2.xlsm") Then Workbooks.Open ("Income Report v2.xlsm") End If Set wbSource = Workbooks("Copy of Vehicle Quotation Internal V7a.xlsm") Set wbDest = Workbooks("Income Report v2.xlsm") wbDest.Sheets("Sheet1").Range("J" & Rows.Count).End(xlUp)(2) = wbSource.Sheets("Sheet1").Range("B2").Value wbDest.Sheets("Sheet1").Range("K" & Rows.Count).End(xlUp)(2) = wbSource.Sheets("Sheet1").Range("D3").Value wbDest.Sheets("Sheet1").Range("L" & Rows.Count).End(xlUp)(2) = wbSource.Sheets("Sheet1").Range("B29").Value wbDest.Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp)(2) = wbSource.Sheets("Sheet1").Range("B19").Value wbDest.Sheets("Sheet1").Range("O" & Rows.Count).End(xlUp)(2) = Time wbSource.Sheets("Sheet1").Range("B2,D3,B29,B19").C learContents End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As and new workbook new name used in existing code
Hi Howard,
Am Thu, 19 Jun 2014 22:49:33 -0700 (PDT) schrieb L. Howard: With this code in a standard module in a workbook named "Copy of Vehicle Quotation Internal V7a" and I save it as a workbook named "My New Workbook 1" how can I get the code to now reference the new name. if wbSource is the workbook with the code then set wbSource =ThisWorkbook Set wbSource = Workbooks("Copy of Vehicle Quotation Internal V7a.xlsm") Set wbDest = Workbooks("Income Report v2.xlsm") if not then Set wbSource = Workbooks("My New Workbook 1.xlsm") Set wbDest = Workbooks("Income Report v2.xlsm") Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As and new workbook new name used in existing code
if wbSource is the workbook with the code then set wbSource =ThisWorkbook Set wbSource = Workbooks("Copy of Vehicle Quotation Internal V7a.xlsm") Set wbDest = Workbooks("Income Report v2.xlsm") if not then Set wbSource = Workbooks("My New Workbook 1.xlsm") Set wbDest = Workbooks("Income Report v2.xlsm") Regards Claus B. Thanks Claus, I'll give it a go. Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As and new workbook new name used in existing code
Thanks Claus, I'll give it a go. Howard Hi Claus, set wbSource =ThisWorkbook Worked very nicely! Thanks. Howard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As and new workbook new name used in existing code
Thanks Claus, I'll give it a go.
Howard Hi Claus, set wbSource =ThisWorkbook Worked very nicely! Thanks. Howard Note that every copy you make using ThisWorkbook.SaveAs contains macros, which is not recommended as a 'good practice' in general! <FWIW Since you appear to be using an original xlsm as a template for making quotes, I recommend you copy the quote sheet to a new workbook and work with that so it's macro-free when opened by others. This approach leaves the source file containing code being used as a 'master' template for generating quotes. Optionally, you could store all copies of the quote sheet in the source file and output each new quote to PDF using SaveAsFixedFormat. (See any post I've responded to with "pdf" in the subject line for examples of the various ways to do this via code). Optimally, store the quote 'template' in a separate file (or files if more than 1) so you can 'Add' sheets to the master file based on a preset template. This allows switching templates 'on-the-fly' if you want to use the code in the master file as a "quotation" project capable of creating 'quotes/sales orders/purchase orders' from a single project. I have a few generic projects that do this, plus a proprietary addin I made for a client that used (at the time) 18 different quote sheets that were used by sales reps selling the client's products. The quote could be copied as a Sales Order to get signed by the customer, and/or copied as a Purchase Order to be submitted to my client. I have my own generic version of this as a 'PointOfSale' addin ("POS") which also stores customer info as an option if dealing with repeat customers. I use this with clients that require some way to present a quote or invoice 'in-the-field' or outside their accounting software. The primary useage is for products that are available in multiple configurations, meaning each 'item template' has sections containing all possible options/configs and users (sales reps) just enter for 'Qty' of the desired option[s]. Printouts can (optionally) only include the selected items/options. Obviously all templates share the same 'structure' so code refs work regardless of layout. Some also include current pricing so all calcs happen automatically when a Qty is entered for each line item. All include handling of sales taxes as 'Tax1' and 'Tax2' (option if VAT is used). Each original quote is stored in a 'period' workbook named with the following formats... "MyOrgName_yyyy_mm_Type.FileExtension" '//monthly "MyOrgName_yyyy_mm#_Type.FileExtension" '//weekly per month "MyOrgName_yyyy_Type.FileExtension" '//yearly ...where '_Type' is either "Quotes" or "Invoices", and "#" denotes the week of the specified month. </FWIW -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As and new workbook new name used in existing code
Note that every copy you make using ThisWorkbook.SaveAs contains macros, which is not recommended as a 'good practice' in general! <FWIW Since you appear to be using an original xlsm as a template for making quotes, I recommend you copy the quote sheet to a new workbook and work with that so it's macro-free when opened by others. This approach leaves the source file containing code being used as a 'master' template for generating quotes. Garry Hi Garry, I'm not pretending to understand all you have said, but I think I am seeing the 'bad practice" of the copy method being used by the OP. And I am responding to his request for code to some stuff. I offered a solution that was workable to him, and he asked a follow up question which is the gist of this thread. I notice in my VB Editor Project Window about 6 or 7 workbook names linked (probably the wrong terminology) to the workbook "Copy of Vehicle Quotation Internal V7a". If I click on one I can get a cascading display of all those workbooks displayed in the editor screen. Is this what you are warning me about? Howard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As and new workbook new name used in existing code
Note that every copy you make using ThisWorkbook.SaveAs contains macros, which is not recommended as a 'good practice' in general! <FWIW Since you appear to be using an original xlsm as a template for making quotes, I recommend you copy the quote sheet to a new workbook and work with that so it's macro-free when opened by others. This approach leaves the source file containing code being used as a 'master' template for generating quotes. Garry Hi Garry, I'm not pretending to understand all you have said, but I think I am seeing the 'bad practice" of the copy method being used by the OP. And I am responding to his request for code to some stuff. I offered a solution that was workable to him, and he asked a follow up question which is the gist of this thread. I notice in my VB Editor Project Window about 6 or 7 workbook names linked (probably the wrong terminology) to the workbook "Copy of Vehicle Quotation Internal V7a". If I click on one I can get a cascading display of all those workbooks displayed in the editor screen. Is this what you are warning me about? In part. This is an indication, though, that each 'copy' of the original contains code and so users get the annoying prompt to enable macros when opening the file. Another thing to consider is the original workbook should absolutely contain no names with global scope to avoid the 'name conflict' prompt when copying the original sheet (ie: template) to another file that already contains other copies from the same source file. Also, I see no reason why you can't suggest a different approach to your OP! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As and new workbook new name used in existing code
Also, I see no reason why you can't suggest a different approach to your OP! I would need to have a much better grasp on all this to offer a different approach. If the post had asked "What is the best way to set up a template so I can produce auto quotes etc." I would never have responded to that post. The post was essentially how to copy values from four scattered cells to a specific range in another workbook. I was able to offer a solution the OP was happy with. Then the follow up question where I was stumped and came here. Howard |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As and new workbook new name used in existing code
Also, I see no reason why you can't suggest a different approach to your OP! I would need to have a much better grasp on all this to offer a different approach. If the post had asked "What is the best way to set up a template so I can produce auto quotes etc." I would never have responded to that post. The post was essentially how to copy values from four scattered cells to a specific range in another workbook. I was able to offer a solution the OP was happy with. Then the follow up question where I was stumped and came here. Howard Yeah, that's the problem with that forum. Seems to me they follow some pretty 'sticky' posting rules there. Seems like they're fairly adament about answering the Q 'as is' because I got a reprimand from one of the frequenters via email for posting alternative solutions to what was already answered. Turned me right off that forum!! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save Workbook with existing Name in the Folder | Excel Programming | |||
save workbook without code | Excel Programming | |||
How to save existing Workbook with a new new name, without changingthe open Workbook name? | Excel Programming | |||
Allowing xlBook.SaveAs in Access code to overwrite existing workbook? | Excel Programming | |||
Save Workbook without VBA code | Excel Programming |