To continue my question about extracting the correct price from a chart according to various variables, please now help with what to do to make the macro look to a workbook on the local network for the price charts.
Many thanks in advance.
To continue my question about extracting the correct price from a chart according to various variables, please now help with what to do to make the macro look to a workbook on the local network for the price charts.
Many thanks in advance.
(doesn't matter if it's on your computer or the network)
Sub open_workbook()
Dim Wb1 As Workbook
'Dont show the user what is happening - great idea since the macro will open and close a workbook.
Application.ScreenUpdating = False
'Open the other workbook
'Input the FULL path to the file, including its extension
Set Wb1 = Workbooks.Open("C:\sample.xls")
'Do something in the other workbook - this just copies some cells.
Wb1.Sheets(1).Range("A1:A10").Copy
'Close the workbook from which we just got some data and make sure not to save it in case accidental changes were made to it.
Wb1.Close SaveChanges:=False
'Do something with the data that we got - pastes it into our current workbook.
'If you want to do something other than copy/paste, put this line ABOVE
'the previous line of code, the one that closes the workbook.
ThisWorkbook.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteAll
'Let the user see what happens in Excel once more - very important to turn this back on here.
Application.ScreenUpdating = True
End Sub
I heavily commented the above macro so you can clearly see what is happening when you paste it into the VBA editor in Excel.
Here is the Macro that should work for your specific situation:
Sub price_calc()
Dim Wb1 As Workbook
Application.ScreenUpdating = False
blind_type = Cells(Selection.Row, 1).Value
fabric_range = Cells(Selection.Row, 2).Value
qty = Cells(Selection.Row, 3).Value
fab_width = Cells(Selection.Row, 4).Value
fab_height = Cells(Selection.Row, 6).Value
If blind_type = "Crank" Then
If fabric_range = "Thames" Then
ws_name = "Crank-Op Thames"
ElseIf fabric_range = "Medway" Then
ws_name = "Crank-Op Medway"
End If
ElseIf blind_type = "Chain" Then
If fabric_range = "Thames" Then
ws_name = "Chain-Op Thames"
ElseIf fabric_range = "Medway" Then
ws_name = "Chain-Op Medway"
End If
End If
Set Wb1 = Workbooks.Open("C:\path\to\file\test.xlsm")
row_location = Application.WorksheetFunction.Match(fab_height, Wb1.Sheets(ws_name).Range("A1:A16")) + 1
col_location = Application.WorksheetFunction.Match(fab_width, Wb1.Sheets(ws_name).Range("A1:Q1")) + 1
unit_price = Sheets(ws_name).Cells(row_location, col_location).Value
Wb1.Close SaveChanges:=False
Selection.Value = unit_price
Application.ScreenUpdating = True
End Sub
Change C:\path\to\file\test.xlsm to the path to your file on the network.
One thing to note is that you will not be able to access the file on the network while another user has it open. This is usually a problem if someone is editing the file by hand and forgets to close it.