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
|