Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL VB noobie needs help - macro opening file already in use prompt | Excel Programming | |||
Macro to Prompt for File -- Neophite Needs HELP | Excel Discussion (Misc queries) | |||
CLOSE ALL macro for XL2K (but with prompt to save for each file)? | Excel Programming | |||
Prompt for file name in a macro | Excel Discussion (Misc queries) | |||
Macro: Prompt user for xls file to add chart | Excel Programming |