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

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

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

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"
<(E-Mail Removed)> wrote in message
news:F2F8BBF4-6805-4A4A-A0C5-(E-Mail Removed)...
>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

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"
> <(E-Mail Removed)> wrote in message
> news:F2F8BBF4-6805-4A4A-A0C5-(E-Mail Removed)...
> >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
>
>