Using a string variable to change a file name

G

Gaz

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
 
H

Homey

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.

|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
|
 
J

joel

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
 
G

Gaz

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Filter 2
Help with activating window from Application.GetOpenFilename 4
Help me5 1
Help again!!! 1
Windows XP Office 2003 Run-time error '9' 2
Error Opening Workbook 3
Help making this macro dynamic :) 1
Runtime 1004 Error Help Request 1

Top