Workbook Reference

M

markoakes

Active Member
Joined
Jan 5, 2004
Messages
325
I have a varible that refers to a cell on a worksheet in another workbook using the following code.

myInfo=Workbooks ("Report 3-3-04.xls"). Sheets ("Info"). Range ("B4")

This works fine, the problem I am having is the date in the name of the Workbook "Report 3-3-04.xls" changes. Is there a way that I can reference the "Report" Workbook no mater what the date is?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
You can put

MyWorkBook = Application.GetOpenFilename

To have the user browse for the file and get the name from there.
 
Upvote 0
The "Report" Workbook will already be open along with several other workbooks, none with Report in the name. I am trying to always refer back to the "Report" Workbook even if the last part of the name changes (the Date).
 
Upvote 0
Hi Mark,

One approach would be to test the open workbooks for a matching name. Note that there is no error-checking here (what if there is more than workbook with Report in the title, what if the sheet name has changed etc).
Code: 
Sub Test()
    Dim wbk As Workbook, wbkReport As Workbook
    Dim myInfo As Variant
    
    For Each wbk In Workbooks
        If wbk.Name Like "Report*" Then
            Set wbkReport = wbk
            Exit For
        End If
    Next wbk
    'look for an open workbook with Report
    
    If Not wbkReport Is Nothing Then
        myInfo = wbkReport.Sheets("Info").Range("B4")
    End If
    'if one exists then use it
    
End Sub
HTH
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Referencing a workbook with a variable file name
Replies
1
Views
295
RoryA
RoryA
N
  • Question
Migrating a data pull
Replies
0
Views
88
Natemoney2
N
S
  • Solved
VBA Code to select a variable folder path, find multiple .xls then copy same named worksheets into new workbook.
Replies
4
Views
433
SteveD01
S
M
  • Question
VBA hides/unhides columns based upon pivot table and value
Replies
0
Views
134
mapatton
M
B
  • Question
Auto calculation off on single sheet even after saving workbook?
Replies
4
Views
177
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,214,918
Messages
6,122,252
Members
449,075
Latest member
staticfluids

Share this page

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