CODEWITHSUNDEEP
vbaexcel
Aravinda Sandaruwan
Aravinda Sandaruwan

Reputation: 11

How to use a variable to refer a closed workbook in excel

I have so many data files to use as source file. but I want to get selected data from those files, when I select a file name using a combo box. see attached picture. When I select a Shipment ID from the drop down list, I want to get some data from a file which has same ID number. I have used below code to do that.

enter image description here

Sub GetDataFromClosedBook()
Dim SO As String
Dim Qty As String
Dim ID As String

ID = Worksheets("Sheet1").Cells(1, "O").Value
MsgBox (ID)
'data location & range to copy
SO = "='D:\Excel Software\Shipment Tracking\[7811.xlsx]Shipment - 7811 - Connected Ord'!$A$1:$C$50" 
Qty = "='D:\Excel Software\Shipment Tracking\[7811.xlsx]Shipment - 7811 - Connected Ord'!$I$1:$I$50"
'link to worksheet
With ThisWorkbook.Worksheets(1).Range("A1:C50") 
.Formula = SO
'convert formula to text
.Value = .Value

End With

With ThisWorkbook.Worksheets(1).Range("E1:E50") 
.Formula = Qty
'convert formula to text
.Value = .Value

End With
End Sub

now i want to use "ID" variable for the file name and sheet name. how can I do that.

Upvotes: 0

Views: 434

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

Untested:

Sub GetDataFromClosedBook()

    Const FILE_INFO As String = "='D:\Excel Software\Shipment Tracking\" & _
                              "[<id>.xlsx]Shipment - <id> - Connected Ord'!"
    Dim SO As String
    Dim Qty As String
    Dim ID As String, fn

    ID = Worksheets("Sheet1").Cells(1, "O").Value
    'replace ID in file/sheet path
    fn = Replace(FILE_INFO, "<id>", ID)

    'data location & range to copy
    SO = fn & "$A$1:$C$50" 
    Qty = fn & "$I$1:$I$50"
    'link to worksheet
    With ThisWorkbook.Worksheets(1).Range("A1:C50") 
       .Formula = SO
       'convert formula to text
       .Value = .Value
    End With

    With ThisWorkbook.Worksheets(1).Range("E1:E50") 
       .Formula = Qty
       'convert formula to text
       .Value = .Value
    End With

End Sub

EDIT: adding as a note since I had no idea Excel did this...

If the sheet name in your formula is not found in the referenced file, and that file has only one worksheet, Excel will automatically change the sheet name in your formula to the name of the sheet in the file. It will not warn you about this, so if the name of the sheet really needs to match, you need to find a way to detect that has happened.

If there's no match and the source file has >1 sheet, then Excel will prompt you for which sheet should be used.

Upvotes: 1

Related Questions