How do I reference an external worksheet in VB?

JonHaywood

New Member
Joined
Jul 23, 2014
Messages
34
Hi
I'm struggling to make some VB code reference an external workbook. I can't pick out the most suitable method from the other threads out there.

I've created an 'Enquiry' worksheet which I plan to allow various users to open so that they can interrogate data held in another sheet.
To make it easier for me to set the enquiry sheet up, I've included a mock-up 'Data' sheet in the same workbook. Sheet2 of this workbook is the enquiry sheet, Sheet3 is the mock data.

The code I've created (or more accurately adapted from various sources... thanks to everyone who's contributed), works great, but now I want to move away from the mock-up data and make the enquiry look at and return the REAL data held in an external workbook.

If the real data is held on Sheet1 of 'C:\Myfiles\Group\Master Data.xlsm', what should I change each of the 'Sheet3' references to?

Also, will the external workbook have to be opened for the code to work? (Note: it is 2000+ rows now and will get larger over time)

Code:
Sub Showall()
 
 'show all filtered data and remove filter
 With Sheet3
 If Sheet3.AutoFilterMode Then
 Sheet3.Range("A4").AutoFilter
 End If
 End With
 End Sub
 
 
Sub ShowAllRecords()
'show data and keep filter
 If Sheet3.FilterMode Then
 Sheet3.ShowAllData
 End If
 
 'copy the filtered data
 RemoveFilter
 End Sub
 
 Sub RemoveFilter()
 
 'clear the contents
 Sheet2.Range("A8:O10000").ClearContents
 Sheet2.Range("B3:B6").ClearContents
 
 'copy and paste the range
 Sheet3.Range("Database").SpecialCells(xlCellTypeVisible).Copy
 Sheet2.Range("A8").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats, SkipBlanks:=True
 Sheet2.Range("A1").Select
 Application.CutCopyMode = False
 
 'Set Print Area
 Sheet2.PageSetup.printarea = Range("A1:O1", Range("A10000").End(xlUp)).Address
 
 End Sub
 
Sub CopyFilter()
 
 'clear the contents
 Sheet2.Range("A8:O10000").ClearContents
  
  'copy and paste the range
 Sheet3.Range("Database").SpecialCells(xlCellTypeVisible).Copy
 Sheet2.Range("A8").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats, SkipBlanks:=True
 Sheet2.Range("A1").Select
 Application.CutCopyMode = False
 
  'Set Print Area
 Sheet2.PageSetup.printarea = Range("A1:O1", Range("A10000").End(xlUp)).Address
 End Sub
 
 
Sub TwoCriteria()
'declare the variables
    Dim BU As String
    Dim GL As String
    Dim Area As String
    Dim Current As String
    Dim rng As Range
'error handler
    On Error GoTo errHandler:
'variables for sheet reference
  BU = Sheet2.Range("B3").Value
  GL = Sheet2.Range("B4").Value
  Area = Sheet2.Range("B5").Value
  Current = Sheet2.Range("B6").Value
'variable for sheet objects
    Set rng = Sheet3.Range("A4")
'add wildcard if valaues are empty
   If BU = "" Then BU = "*"
   If GL = "" Then GL = "*"
   If Area = "" Then Area = "*"
   If Current = "" Then Current = "*"
'run the filters
        rng.AutoFilter Field:=1, Criteria1:=BU & "*"
        rng.AutoFilter Field:=2, Criteria1:=GL & "*"
        rng.AutoFilter Field:=3, Criteria1:=Area & "*"
        rng.AutoFilter Field:=4, Criteria1:=Current & "*"
'copy the range
    CopyFilter
'show all the values
    Showall
    Sheet2.Range("A1").Select
'error block
    On Error GoTo 0
    Exit Sub
errHandler:
    MsgBox "There is no data"
    Showall
    Sheet2.Range("B3").Select
    Application.CutCopyMode = False
End Sub
I've read lots of different threads and tried lots of different ways, but none work properly.

Hope someone can help.

Thanks

Jon

Excel 2010
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,135
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
For the code you have, yes you will need to open the workbook.
 

JonHaywood

New Member
Joined
Jul 23, 2014
Messages
34
Thanks for the quick response Rory. I've seen examples of how to open and close external workbooks without making them visible, so I think I'll be able to do this.
But when you say, "For the code you have" ...does it mean there's a better way to interrogate the external sheet?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,135
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
It depends. There are various different ways but circumstances will dictate which is better.

My preference for things like this is to use ADO to query the other workbook and extract the data you want, but it does require your data to be in fairly consistent format - things like mixed data types in one column don't always work very well with ADO.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,572
Messages
5,487,634
Members
407,606
Latest member
PGMouton

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top