Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic SaveAs Worksheet Name
This is a small part of a larger project that extracts data from a lis to populate a form. Then it copies the form to a new sheet in a ne workbook. I then want it to save the new workbook as the worksheet nam in a directory that I have already choosen. It then goes back an clears the form for the next use. And I then wish it to email the ne workbook to three people with the subject being the file name. Soooo if you can help with some of this let me know. I have most of i working but I don't know how to get the save as sheet name to work. Lin ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic SaveAs Worksheet Name
Assuming:
sName is the name of the worksheet sDir is the full path of the directory where you want the workbook to go, for example, "C:\Documents and Settings\shockley\Desktop\" (and be sure to put the backslash on the end) Then the command would simply be: ActiveWorkbook.SaveAs (sDir & sName & ".xls") Or, if you have assigned the workbook to an object variable "wbk" wbk.SaveAs (sDir & sName & ".xls") You may also want to preceed the save command with a command that insures the current drive is the correct one. This is what I use: ChDrive Left(sDir,1) And then, if desired, once the workbook is saved, change back to the previous drive. HTH, Shockley "Linc" wrote in message ... This is a small part of a larger project that extracts data from a list to populate a form. Then it copies the form to a new sheet in a new workbook. I then want it to save the new workbook as the worksheet name in a directory that I have already choosen. It then goes back and clears the form for the next use. And I then wish it to email the new workbook to three people with the subject being the file name. Soooo if you can help with some of this let me know. I have most of it working but I don't know how to get the save as sheet name to work. Linc ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic SaveAs Worksheet Name
Thanks for the response. I ultimately figured out that the string I needed was Activeworkbook.SaveAs "NameofFile_" & Cells(1, 12) This added the counter number to the name of the file and saved it. So the file Incidnet.xls became Incident_1002.xls with 1002 coming from the cell in row one column 12. Thanks for your help too. Linc ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic SaveAs Worksheet Name
Glad you got it to work!
Shockley "Linc" wrote in message ... Thanks for the response. I ultimately figured out that the string I needed was Activeworkbook.SaveAs "NameofFile_" & Cells(1, 12) This added the counter number to the name of the file and saved it. So the file Incidnet.xls became Incident_1002.xls with 1002 coming from the cell in row one column 12. Thanks for your help too. Linc ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
howto: saveas with file name from a cell in the current worksheet | New Users to Excel | |||
howto: saveas with file name from a cell in the current worksheet | New Users to Excel | |||
SAveAs worksheet | Excel Worksheet Functions | |||
Automatic updating of a rollup worksheet when a new worksheet is a | Excel Worksheet Functions | |||
SaveAs problem, renames Worksheet too | Excel Programming |