A Complicated Conundrum...

largeselection

Active Member
Joined
Aug 4, 2008
Messages
358
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?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
For your first idea, here's a very simple starting off point.

Code:
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.
 
Upvote 0
Hi largeselection,

Something like this possibly

Code:
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top