how can I make a macro prompt for a portion of a file name?
 

I am trying to modify the macro below so that it will prompt the user for the
4 digit year and two digit month, the rest of the path and filename will
remain static... The users will need to run this on a monthly basis, we
don't want them changing the path or "Costs_Plant_HWM.txt" part of the
statement. Help?
Dim FileNum As Long, i As Long
Dim y As Variant
Dim lastrow As Integer
Range("a1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
Range("a1").Select
Selection.End(xlToRight).Select
last_col = ActiveCell.Column
FileNum = FreeFile
Open "c:\200706_Costs_Plant_HCM.txt" For Append As #FileNum
For i = 1 To lastrow
With Application.WorksheetFunction
y = .Transpose(.Transpose(Range(Cells(i, 1), Cells(i, last_col))))
End With
Print #FileNum, "~" + Join(y, "~") + "~"
Next
Close #FileNum

how can I make a macro prompt for a portion of a file name?
 

Create an Input Box like something like this

Set MyInput = InputBox("Enter 4 digit year and 2 digit month")

Then in your code insert MyInput in the place that you want it to be

"David Pelizzari, IS Manager" wrote:

I am trying to modify the macro below so that it will prompt the user for the
4 digit year and two digit month, the rest of the path and filename will
remain static... The users will need to run this on a monthly basis, we
don't want them changing the path or "Costs_Plant_HWM.txt" part of the
statement. Help?
Dim FileNum As Long, i As Long
Dim y As Variant
Dim lastrow As Integer
Range("a1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
Range("a1").Select
Selection.End(xlToRight).Select
last_col = ActiveCell.Column
FileNum = FreeFile
Open "c:\200706_Costs_Plant_HCM.txt" For Append As #FileNum
For i = 1 To lastrow
With Application.WorksheetFunction
y = .Transpose(.Transpose(Range(Cells(i, 1), Cells(i, last_col))))
End With
Print #FileNum, "~" + Join(y, "~") + "~"
Next
Close #FileNum

how can I make a macro prompt for a portion of a file name?
 

Thanks, AKphidelt, however, much of the code below is "borrowed", I can
figure out the input, but how would I craft the Open line?

"AKphidelt" wrote:

Create an Input Box like something like this

Set MyInput = InputBox("Enter 4 digit year and 2 digit month")

Then in your code insert MyInput in the place that you want it to be

"David Pelizzari, IS Manager" wrote:

I am trying to modify the macro below so that it will prompt the user for the
4 digit year and two digit month, the rest of the path and filename will
remain static... The users will need to run this on a monthly basis, we
don't want them changing the path or "Costs_Plant_HWM.txt" part of the
statement. Help?
Dim FileNum As Long, i As Long
Dim y As Variant
Dim lastrow As Integer
Range("a1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
Range("a1").Select
Selection.End(xlToRight).Select
last_col = ActiveCell.Column
FileNum = FreeFile
Open "c:\200706_Costs_Plant_HCM.txt" For Append As #FileNum
For i = 1 To lastrow
With Application.WorksheetFunction
y = .Transpose(.Transpose(Range(Cells(i, 1), Cells(i, last_col))))
End With
Print #FileNum, "~" + Join(y, "~") + "~"
Next
Close #FileNum

how can I make a macro prompt for a portion of a file name?
 

Insert the following code immediately after the FileNum=FreeFile
statement...

YearMonth = Trim$(InputBox("Enter the year followed by the " & _
"2-digit month", "Get New YearMonth"))
Loop Until YearMonth Like "######" Or YearMonth = ""
If YearMonth = "" Then Exit Sub
' You should do some error checking here to make
' sure the entry has a year part that makes sense
' to your process and that the month part is a
' number less than 13.

and change the Open statement to this...

Open YearMonth & "_Costs_Plant_HCM.txt" For Append As #FileNum

Remember to Dim the YearMonth variable as a String. The code as written will
keep popping the InputBox in your users face until he/she enters a 6-digit
number or hits OK without entering anything. If he/she enters nothing, the
macro (an assumed Sub, not Function) will end. I showed a section where you
should put some kind of error checking so you can filter out non-sense
entries (a year that doesn't make sense or a month number not between 01 and
12).

Rick


"David Pelizzari, IS Manager"
wrote in message
...
I am trying to modify the macro below so that it will prompt the user for
the
4 digit year and two digit month, the rest of the path and filename will
remain static... The users will need to run this on a monthly basis, we
don't want them changing the path or "Costs_Plant_HWM.txt" part of the
statement. Help?
Dim FileNum As Long, i As Long
Dim y As Variant
Dim lastrow As Integer
Range("a1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
Range("a1").Select
Selection.End(xlToRight).Select
last_col = ActiveCell.Column
FileNum = FreeFile
Open "c:\200706_Costs_Plant_HCM.txt" For Append As #FileNum
For i = 1 To lastrow
With Application.WorksheetFunction
y = .Transpose(.Transpose(Range(Cells(i, 1), Cells(i, last_col))))
End With
Print #FileNum, "~" + Join(y, "~") + "~"
Next
Close #FileNum

how can I make a macro prompt for a portion of a file name?
 

Thanks, Rick, that did it! I left out the error checking, I will let the
bean counters figure it out, they can always do it again.

"Rick Rothstein (MVP - VB)" wrote:

Insert the following code immediately after the FileNum=FreeFile
statement...

YearMonth = Trim$(InputBox("Enter the year followed by the " & _
"2-digit month", "Get New YearMonth"))
Loop Until YearMonth Like "######" Or YearMonth = ""
If YearMonth = "" Then Exit Sub
' You should do some error checking here to make
' sure the entry has a year part that makes sense
' to your process and that the month part is a
' number less than 13.

and change the Open statement to this...

Open YearMonth & "_Costs_Plant_HCM.txt" For Append As #FileNum

Remember to Dim the YearMonth variable as a String. The code as written will
keep popping the InputBox in your users face until he/she enters a 6-digit
number or hits OK without entering anything. If he/she enters nothing, the
macro (an assumed Sub, not Function) will end. I showed a section where you
should put some kind of error checking so you can filter out non-sense
entries (a year that doesn't make sense or a month number not between 01 and
12).

Rick


"David Pelizzari, IS Manager"
wrote in message
...
I am trying to modify the macro below so that it will prompt the user for
the
4 digit year and two digit month, the rest of the path and filename will
remain static... The users will need to run this on a monthly basis, we
don't want them changing the path or "Costs_Plant_HWM.txt" part of the
statement. Help?
Dim FileNum As Long, i As Long
Dim y As Variant
Dim lastrow As Integer
Range("a1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
Range("a1").Select
Selection.End(xlToRight).Select
last_col = ActiveCell.Column
FileNum = FreeFile
Open "c:\200706_Costs_Plant_HCM.txt" For Append As #FileNum
For i = 1 To lastrow
With Application.WorksheetFunction
y = .Transpose(.Transpose(Range(Cells(i, 1), Cells(i, last_col))))
End With
Print #FileNum, "~" + Join(y, "~") + "~"
Next
Close #FileNum