How do I reference an external worksheet in VB?

[JonHaywood]

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)

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

 

[RoryA]

For the code you have, yes you will need to open the workbook.

 

[JonHaywood]

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]

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.