Save As and new workbook new name used in existing code

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

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.

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").ClearContents

End Sub
 
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.
Click to expand...

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")
Click to expand...

if not then
Set wbSource = Workbooks("My New Workbook 1.xlsm")
Set wbDest = Workbooks("Income Report v2.xlsm")


Regards
Claus B.
 
if wbSource is the workbook with the code then

set wbSource =ThisWorkbook







if not then

Set wbSource = Workbooks("My New Workbook 1.xlsm")

Set wbDest = Workbooks("Income Report v2.xlsm")





Regards

Claus B.
Click to expand...


Thanks Claus, I'll give it a go.

Howard
 
Thanks Claus, I'll give it a go.
Hi Claus,

set wbSource =ThisWorkbook

Worked very nicely!

Thanks.

Howard
Click to expand...

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. 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
 
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
Click to expand...

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
 
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?
Click to expand...

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
 
Also, I see no reason why you can't suggest a different approach to

your OP!
Click to expand...

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
 
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
Click to expand...

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
 
Back
Top