A Complicated Conundrum...

[largeselection]

Let's see if this is even possible...Perhaps with a userfield or some such?...

I have to change the name of a file referenced in a macro as the days pass by. Rather than having to step into the code and manually changing the file name is there a way to have a prompt that will change it for me.

Here is the code to clarify:

Dim MyFile As Workbook
Set MyFile = ActiveWorkbook

Dim MyTarget As Workbook
Set MyTarget = Workbooks("Week 28 O.xls")

MyTarget.Activate
Range("D190").Select
ActiveWindow.SmallScroll Down:=-30
Range("A160:E160").Select
Range(Selection, Selection.End(xlDown)).Select............

I will have to change the week 28 to 29,30,31, etc. I was wondering if rather than steping into the code and physically changing the number from 28 to 29 would it be possible so that when I click my custom "run macro" button it prompts me for the correct number or file?

Or perhaps is it possible to program in something where it will reference the workbook name from a cell and I can program in some code that will create a cell that has the same name as the workbook.

What are your thoughts?

 

[gauntletxg]

For your first idea, here's a very simple starting off point.

Dim MyWeek As Integer
MyWeek = InputBox("Enter the week")

You can then use MyWeek where you would use your number. If this number is already updated on your workbook, you can use ActiveWorkbook.Name to return the filename. There's many ways to achieve you want, it all depends on how dynamic and ubiquitous you want it to be.

 

[sosullivan]

Hi largeselection,

Something like this possibly

Sub test()
Dim MyFile As Workbook
Dim Response As Long
Dim MyTarget As Workbook

Set MyFile = ActiveWorkbook
    Response = InputBox("Week Number...")
        MyBook = "Week " & Response & " O.xls"
Set MyTarget = Workbooks(MyBook)

MyTarget.Activate
Range("D190").Select
ActiveWindow.SmallScroll Down:=-30
Range("A160:E160").Select
Range(Selection, Selection.End(xlDown)).Select
End Sub

Hope this helps

Seamus

 

[Joe Was]

Sub myGetAFileNm()
'Standard module code, like: Module1.
'Get a file's name dialog.
'Does not open the file!
Dim myFileIndex&
Dim myFileNm As Variant
Dim myFileType$, myTitle$

On Error GoTo myErr

'Option: Set Default File type to use.
myFileType = "Excel Files (*.xls), *.xls"
myFileIndex = 1
myTitle = "Select File!"

myFileNm = Application.GetOpenFileName(FileFilter:=myFileType, _
FilterIndex:=myFileIndex, _
Title:=myTitle)

If myFileNm = False Then GoTo myEnd

'Use the selected Drive/Folder/File.Extension in "myFileNm" anyway you want.
MsgBox myFileNm
GoTo myEnd

myErr:
MsgBox "Folder not found!", vbCritical + vbOKOnly, "Error!"

myEnd:
End Sub

 

[largeselection]

Thank you all!!

All were great answers. I ended up using sosullivan's response. Worked exactly as I had hoped.



Someday perhaps i'll be as wise as you all in the mysteries of excel...

 

[sosullivan]

You're welcome. Glad I was able to help

Seamus