Need Macro help to ask for a file name 1

[ptlagow]

Hello,

I have a macro created that imports information into my workbook from a file whose name changes monthly. I would like to have a msg box ask the user to input the month and year needed. The file name is "Amortizing Advances August 2003.xlw". I am thinking that the macro needs to view the file as (text) "Amortizing Advances" (date input) [August 2003].xlw". Any thoughts would be greatly appreciated.

Peter

 

[SkipVought]

Hi,

bNoMonth = True
do while bNoMonth
 vMo= inputbox("What month")
 select case vMo
 case "Jan","Feb","Mar", 'etc
  bNoMonth = False
 case vbCancel
  exit sub
 end select
Loop
vYr = inputbox("What year")
if vYr = vbCancel the exit sub
if vYr < 1990 OR vYr > 2010 then exit sub
FName = sPath & &quot;Amortizing Advances &quot; & vMo &  &quot;  &quot; & vYr & &quot;.xlw&quot;

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ptlagow]

Thanks for the help, but something is not right. Could be the set up of the macro. (original below)

Worksheets("Sheet1&quot.Activate
Worksheets("Sheet1&quot.Cells.Clear
ChDir "H:\Treasury\Dept0034\Reporting\CMR Reports\Fy 2003"
Workbooks.Open FileName:="Amortizing Advances September 2003.xlw"
Worksheets("Projections&quot.Activate
Cells.Select
Selection.Copy
Workbooks("Mlymt.xlw&quot.Activate
Worksheets("Sheet1&quot.Activate
Cells.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1&quot.Select
Worksheets("Instructions & Macros&quot.Activate

When I inserted the code you posted it asked for the correct repsonses, but did not open the correct files.

Thanks
Peter

 

[SkipVought]

Do you have
sPath = "H:\Treasury\Dept0034\Reporting\CMR Reports\Fy 2003\"?

Make sure that the final \ is in there

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Nelviticus]

You probably need a ChDrive("H&quot before your ChDir. ChDir only works if the directory you're changing to is on the same drive as your default file location (in Tools/Options/General).

If you want to do it properly using variables just use ChDir(sPath) - you can pass the full path to ChDir, it'll just ignore everything but the first letter.

N.

 

[ptlagow]

Thanks to both of you for your help.
I added the code changes and when I run the macro it asks which month I am looking for, but it still does not open the selected workbook. I have attached the entire code for any thoughts.

Worksheets("Sheet1&quot.Activate
Worksheets("Sheet1&quot.Cells.Clear
sPath = "H:\Treasury\Dept0034\Reporting\CMR Reports\Fy 2003\"
bNoMonth = True
Do While bNoMonth
vMo = InputBox("What month&quot
Select Case vMo
Case "January", "February", "March", "April", "May", "June", "July", "August"
bNoMonth = False
Case vbCancel
Exit Sub
End Select
Loop
vYr = InputBox("What year&quot
If vYr = vbCancel Then Exit Sub
If vYr < 1990 Or vYr > 2010 Then Exit Sub
FName = sPath & &quot;Amortizing Advances &quot; & vMo & &quot; &quot; & vYr & &quot;.xlw&quot;
Cells.Select
Selection.Copy
Workbooks(&quot;Mlymt.xlw&quot.Activate
Worksheets(&quot;Sheet1&quot.Activate
Cells.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(&quot;A1&quot.Select
Worksheets(&quot;Instructions & Macros&quot.Activate

thanks-peter

 

[SkipVought]

Hi,

You have not opened workbook FName

Workbooks.Open FName

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ptlagow]

Ok. I must be a box of rocks..., but when I inserted the open command it opens a blank .txt file. Where should I have inserted the code?

Peter

 

[SkipVought]

once U have the month and year formatted AND before you start referencing workbook/worksheet/range and other Excel objects, duh?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ptlagow]

Skip,

Thanks again. Works like a charm!

Peter