Make a macro look to a workbook on local network for data

1

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. 

Answer
Discuss

Discussion

Thanks Don
I'll try this tomorrow but in the meantime, what would happen if the macro was run from 2 different workbooks at the same time, both extracting prices simultaneously from the same 'price chart workbook' on the server? 
JonP (rep: 33) Jul 7, '16 at 5:30 pm
I think that you just get an error. The macro runs really fast though so I don't think you should run into this issue unless you have a lot of people trying to get prices at the same time.
If you want to see what happens, put it on the network, get the macro working, have someone else open the file, then you try to run the macro and see what happens. I am not running on a local network right now so i can't test that.
don (rep: 1482) Jul 7, '16 at 7:43 pm
Add to Discussion

Answers

0
Selected Answer

Generic code to open and get data from another workbook

(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.

Discuss

Discussion

Thanks Don
Works Perfect :)
JonP (rep: 33) Jul 8, '16 at 3:25 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login