Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing contents of a cell to a macro
I am creating a worksheet model which will be used repetitively. I have the
macro to start the new worksheet and pre-fill some of the cells. After manually completing the rest of the information on the worksheet, I want to run a macro which will save that worksheet to a filename that exists in one of the cells. Here is the SaveAs Filename macro: ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\myusername\My Documents\filename.xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False I want to be able to replace "filename" with the value in cell A5 for instance. How can I do this? Thank you. Learning In Florida |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing contents of a cell to a macro
mycellfilename=Range("A5").Value
ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\myusername\My Documents\" & mycellfilename & ".xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "LivingIn32751" wrote: I am creating a worksheet model which will be used repetitively. I have the macro to start the new worksheet and pre-fill some of the cells. After manually completing the rest of the information on the worksheet, I want to run a macro which will save that worksheet to a filename that exists in one of the cells. Here is the SaveAs Filename macro: ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\myusername\My Documents\filename.xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False I want to be able to replace "filename" with the value in cell A5 for instance. How can I do this? Thank you. Learning In Florida |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing contents of a cell to a macro
Try range("A1").value if the path and filename are in the cell. If only the filename is in the cell, you can probably use something like: "C:\Documents and Settings\myusername\My Documents\" & range('A1").value This all presumes the filename is in cell A1 of the active sheet. You could make the code more robust by naming the cell containing the filename and referencing it by name rather than address. Something like: "C:\Documents and Settings\myusername\My Documents\" & range('filename") where filename is the name of the cell with the filename. Good luck. Ken Norfolk, Va On Sep 11, 12:36 pm, LivingIn32751 wrote: I am creating a worksheet model which will be used repetitively. I have the macro to start the new worksheet and pre-fill some of the cells. After manually completing the rest of the information on the worksheet, I want to run a macro which will save that worksheet to a filename that exists in one of the cells. Here is the SaveAs Filename macro: ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\myusername\My Documents\filename.xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False I want to be able to replace "filename" with the value in cell A5 for instance. How can I do this? Thank you. Learning In Florida |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing contents of a cell to a macro
Both answers I have received LOOK good, but they don't seem to be working.
I'm getting "expression expected" errors. I suspect the concatenation character. Perhaps I need more quotes or parentheses, but try as I may, I'm not getting past the errors. I've eliminated the spaces between the concatenated operands to no avail. I've added parens to no avail. Should I be using single of double quotes? 'Tis a puzzlement! -- Learning In Florida "Ken" wrote: Try range("A1").value if the path and filename are in the cell. If only the filename is in the cell, you can probably use something like: "C:\Documents and Settings\myusername\My Documents\" & range('A1").value This all presumes the filename is in cell A1 of the active sheet. You could make the code more robust by naming the cell containing the filename and referencing it by name rather than address. Something like: "C:\Documents and Settings\myusername\My Documents\" & range('filename") where filename is the name of the cell with the filename. Good luck. Ken Norfolk, Va On Sep 11, 12:36 pm, LivingIn32751 wrote: I am creating a worksheet model which will be used repetitively. I have the macro to start the new worksheet and pre-fill some of the cells. After manually completing the rest of the information on the worksheet, I want to run a macro which will save that worksheet to a filename that exists in one of the cells. Here is the SaveAs Filename macro: ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\myusername\My Documents\filename.xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False I want to be able to replace "filename" with the value in cell A5 for instance. How can I do this? Thank you. Learning In Florida |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing contents of a cell to a macro
Try declaring a variable, then building the filename into the
variable, and finally using the variable in the Activeworkbook.saveas line. You can also get rid of a lot of parameters since you are using the defaults. Something like: Sub Macro1() Dim fn As String fn = "C:\Documents and Settings\myusername\My Documents\filename.xls" ActiveWorkbook.SaveAs Filename:=fn End Sub You can use debug.print or a msgbox to verify that fn is a valid filename and that it is what you want. Good luck. Ken On Sep 11, 2:00 pm, LivingIn32751 wrote: Both answers I have received LOOK good, but they don't seem to be working. I'm getting "expression expected" errors. I suspect the concatenation character. Perhaps I need more quotes or parentheses, but try as I may, I'm not getting past the errors. I've eliminated the spaces between the concatenated operands to no avail. I've added parens to no avail. Should I be using single of double quotes? 'Tis a puzzlement! -- Learning In Florida "Ken" wrote: Try range("A1").value if the path and filename are in the cell. If only the filename is in the cell, you can probably use something like: "C:\Documents and Settings\myusername\My Documents\" & range('A1").value This all presumes the filename is in cell A1 of the active sheet. You could make the code more robust by naming the cell containing the filename and referencing it by name rather than address. Something like: "C:\Documents and Settings\myusername\My Documents\" & range('filename") where filename is the name of the cell with the filename. Good luck. Ken Norfolk, Va On Sep 11, 12:36 pm, LivingIn32751 wrote: I am creating a worksheet model which will be used repetitively. I have the macro to start the new worksheet and pre-fill some of the cells. After manually completing the rest of the information on the worksheet, I want to run a macro which will save that worksheet to a filename that exists in one of the cells. Here is the SaveAs Filename macro: ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\myusername\My Documents\filename.xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False I want to be able to replace "filename" with the value in cell A5 for instance. How can I do this? Thank you. Learning In Florida- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to clear range contents when cell contents are changed by us | Excel Programming | |||
Macro to paste in the active cell the contents of a cell from another file?? | Excel Programming | |||
VBA - Passing Cell Contents Into Comment | Excel Programming | |||
Macro to remove contents of cell and move all other contents up one row | Excel Discussion (Misc queries) | |||
passing spreadsheet cell data to macro | Excel Programming |