| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Passing text to VBA code
I have this line in several macros (7) to be exact.
Each one of these macros is called when another Macro is run (Changing something on a sheet). The macros are exactly the same except this line where this line says "Before_I_Update" Is there a way to pass "Before_I_Update" from the macro that is calling it? That way I can have only 1 macro that runs this code and pass the "string" to it. BTW I have three workbooks with the same amount of macros with this code. ThisWorkbook.Path & "\Backup\Loja\Work\Before_I_Update (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Passing text to VBA code
Sub TopLevelMacro()
Dim var As String var = "Before_I_Update" Call Macro1(var) Call macro2(var) 'etc End Sub Sub Macro1(inVal As String) ThisWorkbook.Path & "\Backup\Loja\Work\" & inVal & (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" end sub and so on -- __________________________________ HTH Bob "Ed Davis" wrote in message ... I have this line in several macros (7) to be exact. Each one of these macros is called when another Macro is run (Changing something on a sheet). The macros are exactly the same except this line where this line says "Before_I_Update" Is there a way to pass "Before_I_Update" from the macro that is calling it? That way I can have only 1 macro that runs this code and pass the "string" to it. BTW I have three workbooks with the same amount of macros with this code. ThisWorkbook.Path & "\Backup\Loja\Work\Before_I_Update (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Passing text to VBA code
I now have this code and I am getting a compile error on " & Format(Now,"
EXPECTED END OF STATEMENT. ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& (" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis "Bob Phillips" wrote in message ... Sub TopLevelMacro() Dim var As String var = "Before_I_Update" Call Macro1(var) Call macro2(var) 'etc End Sub Sub Macro1(inVal As String) ThisWorkbook.Path & "\Backup\Loja\Work\" & inVal & (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" end sub and so on -- __________________________________ HTH Bob "Ed Davis" wrote in message ... I have this line in several macros (7) to be exact. Each one of these macros is called when another Macro is run (Changing something on a sheet). The macros are exactly the same except this line where this line says "Before_I_Update" Is there a way to pass "Before_I_Update" from the macro that is calling it? That way I can have only 1 macro that runs this code and pass the "string" to it. BTW I have three workbooks with the same amount of macros with this code. ThisWorkbook.Path & "\Backup\Loja\Work\Before_I_Update (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Passing text to VBA code
Bob missed a " after inVal&:
ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& "(" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" Instead of ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& (" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" Stefi €žEd Davis€ť ezt Ă*rta: I now have this code and I am getting a compile error on " & Format(Now," EXPECTED END OF STATEMENT. ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& (" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis "Bob Phillips" wrote in message ... Sub TopLevelMacro() Dim var As String var = "Before_I_Update" Call Macro1(var) Call macro2(var) 'etc End Sub Sub Macro1(inVal As String) ThisWorkbook.Path & "\Backup\Loja\Work\" & inVal & (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" end sub and so on -- __________________________________ HTH Bob "Ed Davis" wrote in message ... I have this line in several macros (7) to be exact. Each one of these macros is called when another Macro is run (Changing something on a sheet). The macros are exactly the same except this line where this line says "Before_I_Update" Is there a way to pass "Before_I_Update" from the macro that is calling it? That way I can have only 1 macro that runs this code and pass the "string" to it. BTW I have three workbooks with the same amount of macros with this code. ThisWorkbook.Path & "\Backup\Loja\Work\Before_I_Update (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Passing text to VBA code
This is now the whole line of code and I am getting a syntax error on the
line. ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Backup\Sales\Work\"&inval&"(" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False -- Thank You in Advance Ed Davis "Stefi" wrote in message ... Bob missed a " after inVal&: ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& "(" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" Instead of ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& (" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" Stefi "Ed Davis" ezt írta: I now have this code and I am getting a compile error on " & Format(Now," EXPECTED END OF STATEMENT. ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& (" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis "Bob Phillips" wrote in message ... Sub TopLevelMacro() Dim var As String var = "Before_I_Update" Call Macro1(var) Call macro2(var) 'etc End Sub Sub Macro1(inVal As String) ThisWorkbook.Path & "\Backup\Loja\Work\" & inVal & (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" end sub and so on -- __________________________________ HTH Bob "Ed Davis" wrote in message ... I have this line in several macros (7) to be exact. Each one of these macros is called when another Macro is run (Changing something on a sheet). The macros are exactly the same except this line where this line says "Before_I_Update" Is there a way to pass "Before_I_Update" from the macro that is calling it? That way I can have only 1 macro that runs this code and pass the "string" to it. BTW I have three workbooks with the same amount of macros with this code. ThisWorkbook.Path & "\Backup\Loja\Work\Before_I_Update (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Passing text to VBA code
Coming back to the right threead:
a space has been inserted manually before and after InVal. VB editor sometimes doesn't recognize & as an operator that must be separated by a space from the operands: ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Backup\Sales\Work\" & inval & "(" & _ strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Stefi €žEd Davis€ť ezt Ă*rta: This is now the whole line of code and I am getting a syntax error on the line. ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Backup\Sales\Work\"&inval&"(" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False -- Thank You in Advance Ed Davis "Stefi" wrote in message ... Bob missed a " after inVal&: ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& "(" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" Instead of ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& (" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" Stefi "Ed Davis" ezt Ă*rta: I now have this code and I am getting a compile error on " & Format(Now," EXPECTED END OF STATEMENT. ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& (" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis "Bob Phillips" wrote in message ... Sub TopLevelMacro() Dim var As String var = "Before_I_Update" Call Macro1(var) Call macro2(var) 'etc End Sub Sub Macro1(inVal As String) ThisWorkbook.Path & "\Backup\Loja\Work\" & inVal & (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" end sub and so on -- __________________________________ HTH Bob "Ed Davis" wrote in message ... I have this line in several macros (7) to be exact. Each one of these macros is called when another Macro is run (Changing something on a sheet). The macros are exactly the same except this line where this line says "Before_I_Update" Is there a way to pass "Before_I_Update" from the macro that is calling it? That way I can have only 1 macro that runs this code and pass the "string" to it. BTW I have three workbooks with the same amount of macros with this code. ThisWorkbook.Path & "\Backup\Loja\Work\Before_I_Update (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Passing text to VBA code
I checked and no spaces are there, but I retyped it anyway and get a
expected end of statement on "(". -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... This is now the whole line of code and I am getting a syntax error on the line. ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Backup\Sales\Work\"&inval&"(" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False -- Thank You in Advance Ed Davis "Stefi" wrote in message ... Bob missed a " after inVal&: ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& "(" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" Instead of ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& (" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" Stefi "Ed Davis" ezt írta: I now have this code and I am getting a compile error on " & Format(Now," EXPECTED END OF STATEMENT. ThisWorkbook.Path & "\Backup\Sales\Work\" &inVal& (" & strSH & ")" & Format(Now," dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis "Bob Phillips" wrote in message ... Sub TopLevelMacro() Dim var As String var = "Before_I_Update" Call Macro1(var) Call macro2(var) 'etc End Sub Sub Macro1(inVal As String) ThisWorkbook.Path & "\Backup\Loja\Work\" & inVal & (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" end sub and so on -- __________________________________ HTH Bob "Ed Davis" wrote in message ... I have this line in several macros (7) to be exact. Each one of these macros is called when another Macro is run (Changing something on a sheet). The macros are exactly the same except this line where this line says "Before_I_Update" Is there a way to pass "Before_I_Update" from the macro that is calling it? That way I can have only 1 macro that runs this code and pass the "string" to it. BTW I have three workbooks with the same amount of macros with this code. ThisWorkbook.Path & "\Backup\Loja\Work\Before_I_Update (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Passing text to VBA code
Try this way:
Sub callermacro() Call calledmacro("Before_I_Update") End Sub Sub calledmacro(text_param As String) param_filename = ThisWorkbook.Path & "\Backup\Loja\Work\" & text_param & "(" & strSH & ")" & _ Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" End Sub Regards, Stefi €žEd Davis€ť ezt Ă*rta: I have this line in several macros (7) to be exact. Each one of these macros is called when another Macro is run (Changing something on a sheet). The macros are exactly the same except this line where this line says "Before_I_Update" Is there a way to pass "Before_I_Update" from the macro that is calling it? That way I can have only 1 macro that runs this code and pass the "string" to it. BTW I have three workbooks with the same amount of macros with this code. ThisWorkbook.Path & "\Backup\Loja\Work\Before_I_Update (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Passing text to VBA code
I am getting a compile error with this code also on " & Format(Now,"
EXPECTED END OF STATEMENT. -- Thank You in Advance Ed Davis "Stefi" wrote in message ... Try this way: Sub callermacro() Call calledmacro("Before_I_Update") End Sub Sub calledmacro(text_param As String) param_filename = ThisWorkbook.Path & "\Backup\Loja\Work\" & text_param & "(" & strSH & ")" & _ Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" End Sub Regards, Stefi "Ed Davis" ezt írta: I have this line in several macros (7) to be exact. Each one of these macros is called when another Macro is run (Changing something on a sheet). The macros are exactly the same except this line where this line says "Before_I_Update" Is there a way to pass "Before_I_Update" from the macro that is calling it? That way I can have only 1 macro that runs this code and pass the "string" to it. BTW I have three workbooks with the same amount of macros with this code. ThisWorkbook.Path & "\Backup\Loja\Work\Before_I_Update (" & strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls" -- Thank You in Advance Ed Davis |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text format - setting text colour with code | Excel Discussion (Misc queries) | |||
| Macro - Fixed text code needs replacing with variable text | Excel Discussion (Misc queries) | |||
| Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
| Passing a row to a function | Excel Worksheet Functions | |||
| Passing a UDF as an argument to a UDF | Excel Discussion (Misc queries) | |||
![Ed Davis[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
