Reference unknown opened workbook

[Ruca13]

Hello everyone.

Everyday I run a report that integrates into a database. Since the file was different everyday, I had the macro stored in the personal workbook.

I am now trying to move the macro to the database, so that the personal workbook would only have the code to call the macro in the database.

My problem is that the report name can be different everyday. Without having a fixed report name, I was not able to figure out how to define it as a workbook variable since now the "main" macro is in a different location.

Does anyone have any suggestion on how I can store the report as a variable or some other idea?

Thank you for your help,

Rui

 

[Joe4]

Does the report name follow any kind of pattern, i.e. have the date a part of it?

 

[Ruca13]

It always starts with "SqlExport" and it is an .xls file.

I left that information out in the first explanation because if for some reason I have two versions of the report opened than the wildcard won't know which is the report I want to run.

One idea that crossed my min would be to define the variable in the personal workbook (that what I am doing now, setting the report as the activeworkbook) and then "transfer" that variable to the macro I am calling in the database, but I have no idea on how this can be achieved.

Thank you for your reply

 

[Joe4]

How about id the macro has some sort of file browser that allows you to select the file you want, and then uses that selection in the rest of the code?
Would something like that work?

 

[mikerickson]

Are you looking to select between closed files or between opened Excel workbooks?

 

[Ruca13]

Both workbooks will be opened

 

[mikerickson]

If you create a userform with one listbox and two command buttons (butOK and butCancel) and this code

' in code module for Userform1

Option Explicit
Public selWorkbook As Workbook

Private Sub butCancel_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Hide
End Sub

Private Sub ListBox1_Click()
    With ListBox1
        If .ListIndex = -1 Then
            Set selWorkbook = Nothing
        Else
            Set selWorkbook = Application.Workbooks(.List(.ListIndex))
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    Dim oneBook As Workbook
    For Each oneBook In Application.Workbooks
        With oneBook
            If .Name <> Me.Name And .Name <> "PersonalMacroWorkbook" Then
                ListBox1.AddItem .Name
            End If
        End With
    Next oneBook
End Sub

Public Function SelectedWorkbook() As Workbook
    Me.Show
    Set SelectedWorkbook = UserForm1.selWorkbook
    Unload UserForm1
End Function

the function Userform1.SelectedWorkbook will allow the user to select from among the open workbooks.
Note that it returns a Workbook Object

' demo code in normal module
Sub test()
    Dim myBook As Workbook
    Set myBook = UserForm1.SelectedWorkbook
    If myBook Is Nothing Then
        MsgBox "no book selected"
    Else
        MsgBox myBook.Name
    End If
End Sub

 

[Ruca13]

Thank you Mike.

I would prefer not to use the userform as I feel that it will break the workflow since the macro will be run multiples times a day.

But looking at the code it gave me the idea to use the workbook collection to see which files are opened, and if there is more than one with the partial name, it stops the macro so we can close the one we don't want to run.

Thank you for your help.

 

[mikerickson]

Glad it helped.