software

Is there a way to have two cells in the range, so the the two cells make up
the file name?

quot;Frank Kabelquot; wrote:

gt; Hi
gt; in a macro
gt; sub save_it()
gt; dim fname
gt; with activeworkbook
gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; quot;.xlsquot;
gt; .saveas fname
gt; end with
gt; end sub
gt;
gt;
gt; quot;-tinkaquot; wrote:
gt;
gt; gt; Is it possible to make an Excel (2003) template (or a macro in the template)
gt; gt; to save a file with a specific cell-contents as file name? For example an
gt; gt; invoice-template where a cell contains the invoice number. I want it to
gt; gt; automatically save the file as quot;invoice-numberquot;.xls in default file location.
gt; gt;
gt; gt; Thanks... :-)

fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; _
.worksheets(quot;sheet2quot;).range(quot;z99quot;).value amp; _
quot;.xlsquot;

You could use the same sheet and adjacent cells, too.

Petester wrote:
gt;
gt; Is there a way to have two cells in the range, so the the two cells make up
gt; the file name?
gt;
gt; quot;Frank Kabelquot; wrote:
gt;
gt; gt; Hi
gt; gt; in a macro
gt; gt; sub save_it()
gt; gt; dim fname
gt; gt; with activeworkbook
gt; gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; quot;.xlsquot;
gt; gt; .saveas fname
gt; gt; end with
gt; gt; end sub
gt; gt;
gt; gt;
gt; gt; quot;-tinkaquot; wrote:
gt; gt;
gt; gt; gt; Is it possible to make an Excel (2003) template (or a macro in the template)
gt; gt; gt; to save a file with a specific cell-contents as file name? For example an
gt; gt; gt; invoice-template where a cell contains the invoice number. I want it to
gt; gt; gt; automatically save the file as quot;invoice-numberquot;.xls in default file location.
gt; gt; gt;
gt; gt; gt; Thanks... :-)

--

Dave Peterson

Thanks for the information. What would be the full code for this so I can
copy and paste or how could I fix the following code that works with a ctrl-s.

Sub Macro1()
ActiveWorkbook.SaveAs Filename:=quot;C:\quot; amp; Range(quot;I5quot;).Value amp; quot;.xlsquot;, _
FileFormat:=xlNormal, _
Password:=quot;quot;, _
WriteResPassword:=quot;quot;, _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub

quot;Dave Petersonquot; wrote:

gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; _
gt; .worksheets(quot;sheet2quot;).range(quot;z99quot;).value amp; _
gt; quot;.xlsquot;
gt;
gt; You could use the same sheet and adjacent cells, too.
gt;
gt; Petester wrote:
gt; gt;
gt; gt; Is there a way to have two cells in the range, so the the two cells make up
gt; gt; the file name?
gt; gt;
gt; gt; quot;Frank Kabelquot; wrote:
gt; gt;
gt; gt; gt; Hi
gt; gt; gt; in a macro
gt; gt; gt; sub save_it()
gt; gt; gt; dim fname
gt; gt; gt; with activeworkbook
gt; gt; gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; quot;.xlsquot;
gt; gt; gt; .saveas fname
gt; gt; gt; end with
gt; gt; gt; end sub
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;-tinkaquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Is it possible to make an Excel (2003) template (or a macro in the template)
gt; gt; gt; gt; to save a file with a specific cell-contents as file name? For example an
gt; gt; gt; gt; invoice-template where a cell contains the invoice number. I want it to
gt; gt; gt; gt; automatically save the file as quot;invoice-numberquot;.xls in default file location.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks... :-)
gt;
gt; --
gt;
gt; Dave Peterson
gt;

ActiveWorkbook.SaveAs Filename:=quot;C:\quot; amp; Range(quot;I5quot;).Value _
amp; range(quot;J5quot;).value amp; quot;.xlsquot;, _

Change the cell to what you want.

Petester wrote:
gt;
gt; Thanks for the information. What would be the full code for this so I can
gt; copy and paste or how could I fix the following code that works with a ctrl-s.
gt;
gt; Sub Macro1()
gt; ActiveWorkbook.SaveAs Filename:=quot;C:\quot; amp; Range(quot;I5quot;).Value amp; quot;.xlsquot;, _
gt; FileFormat:=xlNormal, _
gt; Password:=quot;quot;, _
gt; WriteResPassword:=quot;quot;, _
gt; ReadOnlyRecommended:=False, _
gt; CreateBackup:=False
gt; End Sub
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; _
gt; gt; .worksheets(quot;sheet2quot;).range(quot;z99quot;).value amp; _
gt; gt; quot;.xlsquot;
gt; gt;
gt; gt; You could use the same sheet and adjacent cells, too.
gt; gt;
gt; gt; Petester wrote:
gt; gt; gt;
gt; gt; gt; Is there a way to have two cells in the range, so the the two cells make up
gt; gt; gt; the file name?
gt; gt; gt;
gt; gt; gt; quot;Frank Kabelquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi
gt; gt; gt; gt; in a macro
gt; gt; gt; gt; sub save_it()
gt; gt; gt; gt; dim fname
gt; gt; gt; gt; with activeworkbook
gt; gt; gt; gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; quot;.xlsquot;
gt; gt; gt; gt; .saveas fname
gt; gt; gt; gt; end with
gt; gt; gt; gt; end sub
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;-tinkaquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Is it possible to make an Excel (2003) template (or a macro in the template)
gt; gt; gt; gt; gt; to save a file with a specific cell-contents as file name? For example an
gt; gt; gt; gt; gt; invoice-template where a cell contains the invoice number. I want it to
gt; gt; gt; gt; gt; automatically save the file as quot;invoice-numberquot;.xls in default file location.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks... :-)
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Thank you very much. This worked, it's great!

quot;Dave Petersonquot; wrote:

gt; ActiveWorkbook.SaveAs Filename:=quot;C:\quot; amp; Range(quot;I5quot;).Value _
gt; amp; range(quot;J5quot;).value amp; quot;.xlsquot;, _
gt;
gt; Change the cell to what you want.
gt;
gt; Petester wrote:
gt; gt;
gt; gt; Thanks for the information. What would be the full code for this so I can
gt; gt; copy and paste or how could I fix the following code that works with a ctrl-s.
gt; gt;
gt; gt; Sub Macro1()
gt; gt; ActiveWorkbook.SaveAs Filename:=quot;C:\quot; amp; Range(quot;I5quot;).Value amp; quot;.xlsquot;, _
gt; gt; FileFormat:=xlNormal, _
gt; gt; Password:=quot;quot;, _
gt; gt; WriteResPassword:=quot;quot;, _
gt; gt; ReadOnlyRecommended:=False, _
gt; gt; CreateBackup:=False
gt; gt; End Sub
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; _
gt; gt; gt; .worksheets(quot;sheet2quot;).range(quot;z99quot;).value amp; _
gt; gt; gt; quot;.xlsquot;
gt; gt; gt;
gt; gt; gt; You could use the same sheet and adjacent cells, too.
gt; gt; gt;
gt; gt; gt; Petester wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Is there a way to have two cells in the range, so the the two cells make up
gt; gt; gt; gt; the file name?
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Frank Kabelquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi
gt; gt; gt; gt; gt; in a macro
gt; gt; gt; gt; gt; sub save_it()
gt; gt; gt; gt; gt; dim fname
gt; gt; gt; gt; gt; with activeworkbook
gt; gt; gt; gt; gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; quot;.xlsquot;
gt; gt; gt; gt; gt; .saveas fname
gt; gt; gt; gt; gt; end with
gt; gt; gt; gt; gt; end sub
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;-tinkaquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Is it possible to make an Excel (2003) template (or a macro in the template)
gt; gt; gt; gt; gt; gt; to save a file with a specific cell-contents as file name? For example an
gt; gt; gt; gt; gt; gt; invoice-template where a cell contains the invoice number. I want it to
gt; gt; gt; gt; gt; gt; automatically save the file as quot;invoice-numberquot;.xls in default file location.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks... :-)
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

This was a really useful place to start for me, but I have a slightly
different variation!

I have a linked spreadsheet (data on the backend and a really complicated
spreadsheet filled with afore mentioned data on the front end). I need to be
able to save as pdf (or print to PDF) the front page directly to a file.
Hopefully being able to use the handy information here about pulling the
client information from a cell.

Can anyone help with a macro that would allow me to extract just the first
page and save to file; generating a file name from a cell. Saving in PDF if
at all possible....but I would live with .xls

quot;Dave Petersonquot; wrote:

gt; ActiveWorkbook.SaveAs Filename:=quot;C:\quot; amp; Range(quot;I5quot;).Value _
gt; amp; range(quot;J5quot;).value amp; quot;.xlsquot;, _
gt;
gt; Change the cell to what you want.
gt;
gt; Petester wrote:
gt; gt;
gt; gt; Thanks for the information. What would be the full code for this so I can
gt; gt; copy and paste or how could I fix the following code that works with a ctrl-s.
gt; gt;
gt; gt; Sub Macro1()
gt; gt; ActiveWorkbook.SaveAs Filename:=quot;C:\quot; amp; Range(quot;I5quot;).Value amp; quot;.xlsquot;, _
gt; gt; FileFormat:=xlNormal, _
gt; gt; Password:=quot;quot;, _
gt; gt; WriteResPassword:=quot;quot;, _
gt; gt; ReadOnlyRecommended:=False, _
gt; gt; CreateBackup:=False
gt; gt; End Sub
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; _
gt; gt; gt; .worksheets(quot;sheet2quot;).range(quot;z99quot;).value amp; _
gt; gt; gt; quot;.xlsquot;
gt; gt; gt;
gt; gt; gt; You could use the same sheet and adjacent cells, too.
gt; gt; gt;
gt; gt; gt; Petester wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Is there a way to have two cells in the range, so the the two cells make up
gt; gt; gt; gt; the file name?
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Frank Kabelquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi
gt; gt; gt; gt; gt; in a macro
gt; gt; gt; gt; gt; sub save_it()
gt; gt; gt; gt; gt; dim fname
gt; gt; gt; gt; gt; with activeworkbook
gt; gt; gt; gt; gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; quot;.xlsquot;
gt; gt; gt; gt; gt; .saveas fname
gt; gt; gt; gt; gt; end with
gt; gt; gt; gt; gt; end sub
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;-tinkaquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Is it possible to make an Excel (2003) template (or a macro in the template)
gt; gt; gt; gt; gt; gt; to save a file with a specific cell-contents as file name? For example an
gt; gt; gt; gt; gt; gt; invoice-template where a cell contains the invoice number. I want it to
gt; gt; gt; gt; gt; gt; automatically save the file as quot;invoice-numberquot;.xls in default file location.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks... :-)
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Maybe.....

Dim myFileName as string
with activeworkbook
worksheets(1).copy 'to a new workbook
with activesheet
with .usedrange
.copy
.pastespecial paste:=xlpastevalues 'remove formulas???
end with
'pick up the name from some cells???
myfilename = .range(quot;a1quot;).value amp; .range(quot;B1quot;).value amp; quot;.xlsquot;
myfilename = quot;C:\my folder\quot; amp; myfilename '????
.parent.saveas filename:=myfilename, fileformat:=xlworkbooknormal
.parent.close savechanges:=false
end with
end with

I don't speak the .pdf stuff.

southofI10chaos wrote:
gt;
gt; This was a really useful place to start for me, but I have a slightly
gt; different variation!
gt;
gt; I have a linked spreadsheet (data on the backend and a really complicated
gt; spreadsheet filled with afore mentioned data on the front end). I need to be
gt; able to save as pdf (or print to PDF) the front page directly to a file.
gt; Hopefully being able to use the handy information here about pulling the
gt; client information from a cell.
gt;
gt; Can anyone help with a macro that would allow me to extract just the first
gt; page and save to file; generating a file name from a cell. Saving in PDF if
gt; at all possible....but I would live with .xls
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; ActiveWorkbook.SaveAs Filename:=quot;C:\quot; amp; Range(quot;I5quot;).Value _
gt; gt; amp; range(quot;J5quot;).value amp; quot;.xlsquot;, _
gt; gt;
gt; gt; Change the cell to what you want.
gt; gt;
gt; gt; Petester wrote:
gt; gt; gt;
gt; gt; gt; Thanks for the information. What would be the full code for this so I can
gt; gt; gt; copy and paste or how could I fix the following code that works with a ctrl-s.
gt; gt; gt;
gt; gt; gt; Sub Macro1()
gt; gt; gt; ActiveWorkbook.SaveAs Filename:=quot;C:\quot; amp; Range(quot;I5quot;).Value amp; quot;.xlsquot;, _
gt; gt; gt; FileFormat:=xlNormal, _
gt; gt; gt; Password:=quot;quot;, _
gt; gt; gt; WriteResPassword:=quot;quot;, _
gt; gt; gt; ReadOnlyRecommended:=False, _
gt; gt; gt; CreateBackup:=False
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; _
gt; gt; gt; gt; .worksheets(quot;sheet2quot;).range(quot;z99quot;).value amp; _
gt; gt; gt; gt; quot;.xlsquot;
gt; gt; gt; gt;
gt; gt; gt; gt; You could use the same sheet and adjacent cells, too.
gt; gt; gt; gt;
gt; gt; gt; gt; Petester wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Is there a way to have two cells in the range, so the the two cells make up
gt; gt; gt; gt; gt; the file name?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Frank Kabelquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Hi
gt; gt; gt; gt; gt; gt; in a macro
gt; gt; gt; gt; gt; gt; sub save_it()
gt; gt; gt; gt; gt; gt; dim fname
gt; gt; gt; gt; gt; gt; with activeworkbook
gt; gt; gt; gt; gt; gt; fname = .worksheets(quot;sheet1quot;).range(quot;A1quot;).value amp; quot;.xlsquot;
gt; gt; gt; gt; gt; gt; .saveas fname
gt; gt; gt; gt; gt; gt; end with
gt; gt; gt; gt; gt; gt; end sub
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;-tinkaquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Is it possible to make an Excel (2003) template (or a macro in the template)
gt; gt; gt; gt; gt; gt; gt; to save a file with a specific cell-contents as file name? For example an
gt; gt; gt; gt; gt; gt; gt; invoice-template where a cell contains the invoice number. I want it to
gt; gt; gt; gt; gt; gt; gt; automatically save the file as quot;invoice-numberquot;.xls in default file location.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Thanks... :-)
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Hi,
I'm getting back to this old question, some problems doesn't go away.

I have this macro and it works fine until invoice has already been saved.
First ofcourse excel wants to know if I want replace earlier file -gt; no -gt;
next popup is
quot;Runtime error 1004
Method 'SaveAs' of object '_Workbook' failed.

How can I get excel to say for ex quot;file couldn't be saved, check nr or
receiver OR replace older filequot; This problem doesn't come up if I want
replace old file.

Sub Macro1()

ActiveWorkbook.SaveAs Filename:=quot;D:\SentInvoices\quot; amp; Range(quot;C3quot;).Value _
amp; Range(quot;A8quot;).Value amp; quot;.xlsquot;, _
FileFormat:=xlNormal, _
Password:=quot;quot;, _
WriteResPassword:=quot;quot;, _
ReadOnlyRecommended:=False, _
CreateBackup:=False

End Sub