Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a string variable to change a file name
I need to open and paste information from four different files each day of
the week (ie there is a "MON PERFORM and a TUE PERFORM etc..."- I have written this initially seven times in seven separate macros (which works) but thought of passing the day value as a string and using a CommandClick() proceedure from seven separate buttons to change the "day" each time but it doesn't work as it stands now (I've never pased an argument before so this is new territory for me) - have I done something daft? Appreciate any help on this - thanks Gaz. Sub Day_Dump(day As String) ' ' Workbooks.Open Filename:=ThisWorkbook.Path & "\" & day & " PERFORM.csv" Cells.Select Selection.Copy Windows("LSM DUMP v2.3.xls").Activate Sheets("LSM Performance Report Dump").Select Range("A1").Select ActiveSheet.Paste Windows(day & " PERFORM.csv").Activate Application.CutCopyMode = False ActiveWindow.Close Windows("LSM DUMP v2.3.xls").Activate Workbooks.Open Filename:=ThisWorkbook.Path & "\" & day & " PROMIS.csv" Cells.Select Selection.Copy Windows("LSM DUMP v2.3.xls").Activate Sheets("LSM Promis Dump").Select Range("A1").Select ActiveSheet.Paste Windows(day & " PROMIS.csv").Activate Application.CutCopyMode = False ActiveWindow.Close Windows("LSM DUMP v2.3.xls").Activate Workbooks.Open Filename:=ThisWorkbook.Path & "\" & day & " JAMS.csv" Cells.Select Selection.Copy Windows("LSM DUMP v2.3.xls").Activate Sheets("LSM Jams Dump").Select Range("A1").Select ActiveSheet.Paste Windows(day & " JAMS.csv").Activate Application.CutCopyMode = False ActiveWindow.Close Windows("LSM DUMP v2.3.xls").Activate Workbooks.Open Filename:=ThisWorkbook.Path & "\" & day & " BOX.csv" Cells.Select Selection.Copy Windows("LSM DUMP v2.3.xls").Activate Sheets("LSM Box Monitor Dump").Select Range("A1").Select ActiveSheet.Paste Windows(day & " BOX.csv").Activate Application.CutCopyMode = False ActiveWindow.Close Windows("LSM DUMP v2.3.xls").Activate ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("OEE Input Data").Select Calculate End Sub Private Sub CommandButton1_Click() Call Day_Dump("MON") End Sub Private Sub CommandButton1_Click() Call Day_Dump("TUE") End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a string variable to change a file name
your code looks ok to me except you have 2 Private Sub
CommandButton1_Click() at bottom. each sub must have diffent name. also donot use day as variable. this an excel function. it work but not a good idea. always use name not used by excel like myday. "Gaz" wrote in message ... |I need to open and paste information from four different files each day of | the week (ie there is a "MON PERFORM and a TUE PERFORM etc..."- I have | written this initially seven times in seven separate macros (which works) | but thought of passing the day value as a string and using a CommandClick() | proceedure from seven separate buttons to change the "day" each time but it | doesn't work as it stands now (I've never pased an argument before so this | is new territory for me) - have I done something daft? | | Appreciate any help on this - thanks Gaz. | | | Sub Day_Dump(day As String) | ' | ' | Workbooks.Open Filename:=ThisWorkbook.Path & "\" & day & " PERFORM.csv" | | Cells.Select | Selection.Copy | Windows("LSM DUMP v2.3.xls").Activate | Sheets("LSM Performance Report Dump").Select | Range("A1").Select | ActiveSheet.Paste | Windows(day & " PERFORM.csv").Activate | Application.CutCopyMode = False | ActiveWindow.Close | Windows("LSM DUMP v2.3.xls").Activate | | Workbooks.Open Filename:=ThisWorkbook.Path & "\" & day & " PROMIS.csv" | | Cells.Select | Selection.Copy | Windows("LSM DUMP v2.3.xls").Activate | Sheets("LSM Promis Dump").Select | Range("A1").Select | ActiveSheet.Paste | Windows(day & " PROMIS.csv").Activate | Application.CutCopyMode = False | ActiveWindow.Close | Windows("LSM DUMP v2.3.xls").Activate | | Workbooks.Open Filename:=ThisWorkbook.Path & "\" & day & " JAMS.csv" | | Cells.Select | Selection.Copy | Windows("LSM DUMP v2.3.xls").Activate | Sheets("LSM Jams Dump").Select | Range("A1").Select | ActiveSheet.Paste | Windows(day & " JAMS.csv").Activate | Application.CutCopyMode = False | ActiveWindow.Close | Windows("LSM DUMP v2.3.xls").Activate | | Workbooks.Open Filename:=ThisWorkbook.Path & "\" & day & " BOX.csv" | | Cells.Select | Selection.Copy | Windows("LSM DUMP v2.3.xls").Activate | Sheets("LSM Box Monitor Dump").Select | Range("A1").Select | ActiveSheet.Paste | Windows(day & " BOX.csv").Activate | Application.CutCopyMode = False | ActiveWindow.Close | Windows("LSM DUMP v2.3.xls").Activate | | ActiveWindow.ScrollWorkbookTabs Position:=xlLast | Sheets("OEE Input Data").Select | Calculate | End Sub | | | Private Sub CommandButton1_Click() | Call Day_Dump("MON") | End Sub | | Private Sub CommandButton1_Click() | Call Day_Dump("TUE") | End Sub | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a string variable to change a file name
Day is a reseved name in excel and can't be used for a variable. From Sub Day_Dump(day As String) To Sub Day_Dump(Myday As String) Then change all occurances of day in the subroutine. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167136 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a string variable to change a file name
Got this sorted,
Didn't now day was a reserved variable but the code works anyway - it was how I had set up (or not set up) the code for the individual buttons on the form. Thanks for the replies - I'll change the day to Myday just in case! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying To Append String Variable To Another String Variable | Excel Programming | |||
Setting a string variable as the file name | Excel Discussion (Misc queries) | |||
Getting text file into a VBA string variable | Excel Programming | |||
How to activate a file when the filename is represented by a string variable | Excel Programming | |||
Using a variable string in a file save path | Excel Programming |