Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save worksheet to a unique filename after worksheet is printed
Thanks for your help ... works great.
Is it possible to save the worksheet in the background (don't want the Save As dialog box to appear) and then print it to the default printer? okrob wrote: Sorry, on the wscopy sub, you don't need to dim SBookName That was left over in my original workbook code. Rob okrob wrote: May be a little more than you asked for, but you can always remark/delete the stuff you don't need. Rob ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Sub MD() On Error GoTo done MkDir "C:\YourPath\" '<=== Change "YourPath" to where you save the files done: End Sub Sub wscopy() Call MD Dim SBookName Dim tdate As String tdate = Format(Date, "ddmmyyyy") Dim rng1 As String, rng2 As String, rng3 as String rng1 = Range("A1").Value rng2 = Range("A2").Value rng3 = Range("B1").Value Dim FN As Variant ActiveSheet.Copy With Application FN = .GetSaveAsFilename _ ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls") '<=== Change "YourPath" to where you save the files If FN < False Then ActiveWorkbook.SaveAs FN End If End With ' Unmark the next line to attach the file to an email using your default email client ' Application.Dialogs(xlDialogSendMail).Show End Sub ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Lifestyle wrote: I have a worksheet where cell "B1" automatically generates a unique number (i.e. like an invoice number). I would like to have a command button (as well using the File|Print) to print the worksheet as well as saving the worksheet to a unique filename which includes whatever is in cell A1 and A2 as well as the date that it was printed. i.e. A1 = PAS, B1 = 001 then the filename should be PAS001_ddmmyyy.xls. I do not want to save the whole workbook to the new filename but only the individual worksheet. Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save worksheet to a unique filename after worksheet is printed
You bet... Forgot that you wanted to print it. NickHK had it on the
money... The default save location for excel is used though, not a specific directory. In most cases, the default is the my documents folder. Also, I just didn't figure you wanted the format to actually be mmddyyy This would give you a date of 18010718. Basically, the day, the month, the 2 digit year, and the day again. If that's the way you wanted it, I kinda took some liberty here and changed it for you. Anyway, here's my updated version: Sub wscopy() Call MD Dim tdate As String tdate = Format(Date, "ddmmyyy") Dim rng1 As String, rng2 As String, rng3 As String rng1 = Range("A1").Value rng2 = Range("A2").Value rng3 = Range("B1").Value Dim FN As Variant With ActiveSheet .Copy .PrintOut End With With Application FN = ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls") '<=== Change "YourPath" to where you save the files If FN < False Then ActiveWorkbook.SaveAs FN End If End With ' Unmark the next line to attach the file to an email using your default email client ' Application.Dialogs(xlDialogSendMail).Show End Sub Maddoktor wrote: Thanks for your help ... works great. Is it possible to save the worksheet in the background (don't want the Save As dialog box to appear) and then print it to the default printer? okrob wrote: Sorry, on the wscopy sub, you don't need to dim SBookName That was left over in my original workbook code. Rob okrob wrote: May be a little more than you asked for, but you can always remark/delete the stuff you don't need. Rob ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Sub MD() On Error GoTo done MkDir "C:\YourPath\" '<=== Change "YourPath" to where you save the files done: End Sub Sub wscopy() Call MD Dim SBookName Dim tdate As String tdate = Format(Date, "ddmmyyyy") Dim rng1 As String, rng2 As String, rng3 as String rng1 = Range("A1").Value rng2 = Range("A2").Value rng3 = Range("B1").Value Dim FN As Variant ActiveSheet.Copy With Application FN = .GetSaveAsFilename _ ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls") '<=== Change "YourPath" to where you save the files If FN < False Then ActiveWorkbook.SaveAs FN End If End With ' Unmark the next line to attach the file to an email using your default email client ' Application.Dialogs(xlDialogSendMail).Show End Sub ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Lifestyle wrote: I have a worksheet where cell "B1" automatically generates a unique number (i.e. like an invoice number). I would like to have a command button (as well using the File|Print) to print the worksheet as well as saving the worksheet to a unique filename which includes whatever is in cell A1 and A2 as well as the date that it was printed. i.e. A1 = PAS, B1 = 001 then the filename should be PAS001_ddmmyyy.xls. I do not want to save the whole workbook to the new filename but only the individual worksheet. Thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save worksheet to a unique filename after worksheet is printed
oops... I was trying the code out to see what the mmddyyy would get me
and I forgot to change it back before I copied it to here... Suggest changing it to mmddyyyy for accurate date representation. Rob okrob wrote: You bet... Forgot that you wanted to print it. NickHK had it on the money... The default save location for excel is used though, not a specific directory. In most cases, the default is the my documents folder. Also, I just didn't figure you wanted the format to actually be mmddyyy This would give you a date of 18010718. Basically, the day, the month, the 2 digit year, and the day again. If that's the way you wanted it, I kinda took some liberty here and changed it for you. Anyway, here's my updated version: Sub wscopy() Call MD Dim tdate As String tdate = Format(Date, "ddmmyyy") Dim rng1 As String, rng2 As String, rng3 As String rng1 = Range("A1").Value rng2 = Range("A2").Value rng3 = Range("B1").Value Dim FN As Variant With ActiveSheet .Copy .PrintOut End With With Application FN = ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls") '<=== Change "YourPath" to where you save the files If FN < False Then ActiveWorkbook.SaveAs FN End If End With ' Unmark the next line to attach the file to an email using your default email client ' Application.Dialogs(xlDialogSendMail).Show End Sub Maddoktor wrote: Thanks for your help ... works great. Is it possible to save the worksheet in the background (don't want the Save As dialog box to appear) and then print it to the default printer? okrob wrote: Sorry, on the wscopy sub, you don't need to dim SBookName That was left over in my original workbook code. Rob okrob wrote: May be a little more than you asked for, but you can always remark/delete the stuff you don't need. Rob ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Sub MD() On Error GoTo done MkDir "C:\YourPath\" '<=== Change "YourPath" to where you save the files done: End Sub Sub wscopy() Call MD Dim SBookName Dim tdate As String tdate = Format(Date, "ddmmyyyy") Dim rng1 As String, rng2 As String, rng3 as String rng1 = Range("A1").Value rng2 = Range("A2").Value rng3 = Range("B1").Value Dim FN As Variant ActiveSheet.Copy With Application FN = .GetSaveAsFilename _ ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls") '<=== Change "YourPath" to where you save the files If FN < False Then ActiveWorkbook.SaveAs FN End If End With ' Unmark the next line to attach the file to an email using your default email client ' Application.Dialogs(xlDialogSendMail).Show End Sub ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Lifestyle wrote: I have a worksheet where cell "B1" automatically generates a unique number (i.e. like an invoice number). I would like to have a command button (as well using the File|Print) to print the worksheet as well as saving the worksheet to a unique filename which includes whatever is in cell A1 and A2 as well as the date that it was printed. i.e. A1 = PAS, B1 = 001 then the filename should be PAS001_ddmmyyy.xls. I do not want to save the whole workbook to the new filename but only the individual worksheet. Thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save worksheet to a unique filename after worksheet is printed
Thanks Rob ... works great.
Can you tell me how close the new workbook automatically after printing that was created. okrob wrote: oops... I was trying the code out to see what the mmddyyy would get me and I forgot to change it back before I copied it to here... Suggest changing it to mmddyyyy for accurate date representation. Rob okrob wrote: You bet... Forgot that you wanted to print it. NickHK had it on the money... The default save location for excel is used though, not a specific directory. In most cases, the default is the my documents folder. Also, I just didn't figure you wanted the format to actually be mmddyyy This would give you a date of 18010718. Basically, the day, the month, the 2 digit year, and the day again. If that's the way you wanted it, I kinda took some liberty here and changed it for you. Anyway, here's my updated version: Sub wscopy() Call MD Dim tdate As String tdate = Format(Date, "ddmmyyy") Dim rng1 As String, rng2 As String, rng3 As String rng1 = Range("A1").Value rng2 = Range("A2").Value rng3 = Range("B1").Value Dim FN As Variant With ActiveSheet .Copy .PrintOut End With With Application FN = ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls") '<=== Change "YourPath" to where you save the files If FN < False Then ActiveWorkbook.SaveAs FN End If End With ' Unmark the next line to attach the file to an email using your default email client ' Application.Dialogs(xlDialogSendMail).Show End Sub Maddoktor wrote: Thanks for your help ... works great. Is it possible to save the worksheet in the background (don't want the Save As dialog box to appear) and then print it to the default printer? okrob wrote: Sorry, on the wscopy sub, you don't need to dim SBookName That was left over in my original workbook code. Rob okrob wrote: May be a little more than you asked for, but you can always remark/delete the stuff you don't need. Rob ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Sub MD() On Error GoTo done MkDir "C:\YourPath\" '<=== Change "YourPath" to where you save the files done: End Sub Sub wscopy() Call MD Dim SBookName Dim tdate As String tdate = Format(Date, "ddmmyyyy") Dim rng1 As String, rng2 As String, rng3 as String rng1 = Range("A1").Value rng2 = Range("A2").Value rng3 = Range("B1").Value Dim FN As Variant ActiveSheet.Copy With Application FN = .GetSaveAsFilename _ ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls") '<=== Change "YourPath" to where you save the files If FN < False Then ActiveWorkbook.SaveAs FN End If End With ' Unmark the next line to attach the file to an email using your default email client ' Application.Dialogs(xlDialogSendMail).Show End Sub ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Lifestyle wrote: I have a worksheet where cell "B1" automatically generates a unique number (i.e. like an invoice number). I would like to have a command button (as well using the File|Print) to print the worksheet as well as saving the worksheet to a unique filename which includes whatever is in cell A1 and A2 as well as the date that it was printed. i.e. A1 = PAS, B1 = 001 then the filename should be PAS001_ddmmyyy.xls. I do not want to save the whole workbook to the new filename but only the individual worksheet. Thank you |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save worksheet to a unique filename after worksheet is printed
Add this:
ActiveWorkbook.Close False AFTER this line in the code: ActiveWorkbook.SaveAs FN Rob Maddoktor wrote: Thanks Rob ... works great. Can you tell me how close the new workbook automatically after printing that was created. okrob wrote: oops... I was trying the code out to see what the mmddyyy would get me and I forgot to change it back before I copied it to here... Suggest changing it to mmddyyyy for accurate date representation. Rob okrob wrote: You bet... Forgot that you wanted to print it. NickHK had it on the money... The default save location for excel is used though, not a specific directory. In most cases, the default is the my documents folder. Also, I just didn't figure you wanted the format to actually be mmddyyy This would give you a date of 18010718. Basically, the day, the month, the 2 digit year, and the day again. If that's the way you wanted it, I kinda took some liberty here and changed it for you. Anyway, here's my updated version: Sub wscopy() Call MD Dim tdate As String tdate = Format(Date, "ddmmyyy") Dim rng1 As String, rng2 As String, rng3 As String rng1 = Range("A1").Value rng2 = Range("A2").Value rng3 = Range("B1").Value Dim FN As Variant With ActiveSheet .Copy .PrintOut End With With Application FN = ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls") '<=== Change "YourPath" to where you save the files If FN < False Then ActiveWorkbook.SaveAs FN End If End With ' Unmark the next line to attach the file to an email using your default email client ' Application.Dialogs(xlDialogSendMail).Show End Sub Maddoktor wrote: Thanks for your help ... works great. Is it possible to save the worksheet in the background (don't want the Save As dialog box to appear) and then print it to the default printer? okrob wrote: Sorry, on the wscopy sub, you don't need to dim SBookName That was left over in my original workbook code. Rob okrob wrote: May be a little more than you asked for, but you can always remark/delete the stuff you don't need. Rob ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Sub MD() On Error GoTo done MkDir "C:\YourPath\" '<=== Change "YourPath" to where you save the files done: End Sub Sub wscopy() Call MD Dim SBookName Dim tdate As String tdate = Format(Date, "ddmmyyyy") Dim rng1 As String, rng2 As String, rng3 as String rng1 = Range("A1").Value rng2 = Range("A2").Value rng3 = Range("B1").Value Dim FN As Variant ActiveSheet.Copy With Application FN = .GetSaveAsFilename _ ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls") '<=== Change "YourPath" to where you save the files If FN < False Then ActiveWorkbook.SaveAs FN End If End With ' Unmark the next line to attach the file to an email using your default email client ' Application.Dialogs(xlDialogSendMail).Show End Sub ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Lifestyle wrote: I have a worksheet where cell "B1" automatically generates a unique number (i.e. like an invoice number). I would like to have a command button (as well using the File|Print) to print the worksheet as well as saving the worksheet to a unique filename which includes whatever is in cell A1 and A2 as well as the date that it was printed. i.e. A1 = PAS, B1 = 001 then the filename should be PAS001_ddmmyyy.xls. I do not want to save the whole workbook to the new filename but only the individual worksheet. Thank you |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save worksheet to a unique filename after worksheet is printed
Thank you Rob.
okrob wrote: Add this: ActiveWorkbook.Close False AFTER this line in the code: ActiveWorkbook.SaveAs FN Rob Maddoktor wrote: Thanks Rob ... works great. Can you tell me how close the new workbook automatically after printing that was created. okrob wrote: oops... I was trying the code out to see what the mmddyyy would get me and I forgot to change it back before I copied it to here... Suggest changing it to mmddyyyy for accurate date representation. Rob okrob wrote: You bet... Forgot that you wanted to print it. NickHK had it on the money... The default save location for excel is used though, not a specific directory. In most cases, the default is the my documents folder. Also, I just didn't figure you wanted the format to actually be mmddyyy This would give you a date of 18010718. Basically, the day, the month, the 2 digit year, and the day again. If that's the way you wanted it, I kinda took some liberty here and changed it for you. Anyway, here's my updated version: Sub wscopy() Call MD Dim tdate As String tdate = Format(Date, "ddmmyyy") Dim rng1 As String, rng2 As String, rng3 As String rng1 = Range("A1").Value rng2 = Range("A2").Value rng3 = Range("B1").Value Dim FN As Variant With ActiveSheet .Copy .PrintOut End With With Application FN = ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls") '<=== Change "YourPath" to where you save the files If FN < False Then ActiveWorkbook.SaveAs FN End If End With ' Unmark the next line to attach the file to an email using your default email client ' Application.Dialogs(xlDialogSendMail).Show End Sub Maddoktor wrote: Thanks for your help ... works great. Is it possible to save the worksheet in the background (don't want the Save As dialog box to appear) and then print it to the default printer? okrob wrote: Sorry, on the wscopy sub, you don't need to dim SBookName That was left over in my original workbook code. Rob okrob wrote: May be a little more than you asked for, but you can always remark/delete the stuff you don't need. Rob ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Sub MD() On Error GoTo done MkDir "C:\YourPath\" '<=== Change "YourPath" to where you save the files done: End Sub Sub wscopy() Call MD Dim SBookName Dim tdate As String tdate = Format(Date, "ddmmyyyy") Dim rng1 As String, rng2 As String, rng3 as String rng1 = Range("A1").Value rng2 = Range("A2").Value rng3 = Range("B1").Value Dim FN As Variant ActiveSheet.Copy With Application FN = .GetSaveAsFilename _ ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls") '<=== Change "YourPath" to where you save the files If FN < False Then ActiveWorkbook.SaveAs FN End If End With ' Unmark the next line to attach the file to an email using your default email client ' Application.Dialogs(xlDialogSendMail).Show End Sub ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++ Lifestyle wrote: I have a worksheet where cell "B1" automatically generates a unique number (i.e. like an invoice number). I would like to have a command button (as well using the File|Print) to print the worksheet as well as saving the worksheet to a unique filename which includes whatever is in cell A1 and A2 as well as the date that it was printed. i.e. A1 = PAS, B1 = 001 then the filename should be PAS001_ddmmyyy.xls. I do not want to save the whole workbook to the new filename but only the individual worksheet. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to show date printed, updated only when worksheet printed? | Excel Discussion (Misc queries) | |||
Auto save file copy with unique filename | Excel Worksheet Functions | |||
USE MACRO TO SAVE WORKSHEET SELECTING FILENAME FROM CELLS | Excel Programming | |||
How do i auto rename a worksheet to be the same filename as save f | Excel Worksheet Functions | |||
using macro to save worksheet with unique file name | Excel Programming |