Set Dim workbook name via 'InputBox'

[heathball]

I have a long macro, mostly recorded VBA.

It opens/closes various files as it proceeds
I need to select the correct workbook/sheet that refers back to..........

The MAIN file the macro operates on, the name of which changes every so often.

If i have an inputbox option at the start of the macro, to name the current workbook, which today, is.....
Bono.xlsb
and reuse it within the macro, when i am selecting the workbook, without having to type it in every time, its happy days




So at the start of the macro, i want this message box ONLY ONCE, and i type in the name of the new MAIN workbook

Perhaps a filepath could be entered into this box


So that when i want to do something like this......
Workbooks("Bono.xlsb").Activate
Sheets("top10hits").Select

It cannot be about "Bono.xlsb" because the file in 3 days time has a new name. It could be "Madonna" for example

what i type into this box, will replace "Bono.xlsb" in ''''''''Workbooks("Bono.xlsb").Activate''''''''''

so something like this
eg
Dim MAIN as Workbook
Set MAIN = InputBox("name this workbook, as 'Bono' is so yesterday", "InputBox Example")
Set MAIN = "Madonna.xlsb" - (imagined i typed this into the box)
then in the macro, it solves the issue of selecting the MAIN workbook, when i write something like this.......
Workbooks(MAIN).Activate
Sheets("top10hits").Select

but the macro is actually interpreting
Workbooks("Madonna.xlxb").Activate
Sheets("top10hits").Select
because i typed "Madonna.xlsb" into the Inputbox


in other words...
MAIN = what i type into the Inputbox, either name.fileextension or filepath


After searching, I still haven't found what I'm looking for.
is this possible?

i will also add, using the same filename each time, would be a last resort, due to other events and happenings, which would make it tedious at best.
Any ideas will be appreciated.

 

[6StringJazzer]

You are showing Activate, which will activate a workbook only if it's already open. Are you expecting the workbook to already be open? Or you do need to open the workbook that the user names?

I would use something like this to present a file picker dialog to the user. Asking the user to type things in is too error-prone.

' Ask user for file and open it
' Return the open workbook
' If user aborts, result is Nothing
Public Function WorkbookSelectedByUser(Optional Prompt As String = "Please select a file to open") As Workbook

   Dim UserFileName As Variant ' Variant because we are using to capture value
                               ' return by GetOpenFilename, which can return False
   
   UserFileName = Application.GetOpenFilename( _
                        Title:=Prompt, _
                        FileFilter:="Excel Files *.xls;*.xlsx (*.xls;*.xlsx),")

   If UserFileName = False Then ' must compare to False because it's a Variant
      MsgBox "No file specified."
   Else
      On Error GoTo OpenFailed
      Set WorkbookSelectedByUser = Workbooks.Open(FileName:=UserFileName)
   End If
   
   Exit Function
   
OpenFailed:

   MsgBox "Failed to open source file specified by user." & vbCrLf & _
          "Name: " & UserFileName & vbCrLf & _
          Err.number & ": " & Err.Description
          
   Exit Function

End Function

I still haven't found what I'm looking for.

I see what you did there

 

[heathball]

Thanks Jeff.
I see there are some interesting things in this code of yours. i will look through this and test it.

Because you wrote this

You are showing Activate, which will activate a workbook only if it's already open. Are you expecting the workbook to already be open? Or you do need to open the workbook that the user names?​

I want to make sure there is clarity (i find it hard to write these threads, and i don't think i excel at it)
The workbook is always open, 99% of the maco applies to it.

The point i was trying to make, is to avoid me typing in the name of the file, as it changes its name frequently ( a new file is created, different data, but columns etc the same, and the same macro is applied)

one possible solution is a (find/replace --- old name/new name) applied to the macro module box, at the time i create a new file.​

and create a message to remind myself to do this at the start of the macro!


But, i was seeking a nifty VBA answer on how i can use DIM and SET and Inputbox to have the ""workbook.select/activate" option always find its way back to the new file i have created, with the new updated name. hence the idea with the Input box.

is what i am trying to achieve clear?
Or if you have dealt with this in your code and i cannot see it, let me know.

In other words, i am not seeking to do any of these things with the input box
1. open a file
2. name a file
3. rename a file

 

[heathball]

I see with
Application.GetOpenFilename
this is rather interesting, i am reading about it. It looks relavent to what i am seeking

 

[6StringJazzer]

If the file is already open I would not recommend the code I provided. That is intended to open a file.

You may have some confusion about how Set works.

Dim MAIN as Workbook
Set MAIN = InputBox("name this workbook, as 'Bono' is so yesterday", "InputBox Example")

Set causes an object variable to point to an instance of an object. You can Set a workbook object to another workbook object, but InputBox returns a string. Your Set will not work by setting a workbook object to a string. You have to do this:

Dim MAIN as Workbook
Set MAIN = Workbooks(InputBox("name this workbook, as 'Bono' is so yesterday", "InputBox Example"))

Workbooks is a collection of open Workbooks. If you use the name of a workbook as the index into the collection, it will return a Workbook object.

ALso, this is not doing what you think it will:

Workbooks(MAIN).Activate
Sheets("top10hits").Select

Workbooks does not accept a Workbook as an index. You can just use the Workbook object directly:

MAIN.Activate

I do not recommend using defaults after you Activate, but rather explicit qualifications.

MAIN.Sheets("top10hits").Select

 

[heathball]

Thanks very much, Jeff. Beautifully explained.
I am sure i will make some headway with this.
Cheers