PDA

View Full Version : Retrieve price from another wb instead of different sheet? code complete except exter



lostinvb
03-14-2019, 01:34 PM
I would like to open an invoice and use the macro to match an item# in column A to the wbPrice wsItems column A

The following code does all this among the same workbook but I would rather have it look externally.

"wsInvoice" instead will just be the active sheet and would search wbPrice-wsItems with the path C:\FileServer\CompanyDocs\My Excel\Price.xls

please help




Private Sub CommandButton1_Click()
Dim wsItems As Worksheet, wsInvoice As Worksheet '??????
Dim iInvoice As Long, iLastInvoice As Long
Dim iPrice As Long

Application.ScreenUpdating = False

Set wsItems = Worksheets("Items") ' path would be: C:\FileServer\CompanyDocs\My Excel\Price.xls
Set wsInvoice = Worksheets("Invoice") ' this would be obsolete ... would just be referencing active sheet

iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row

For iInvoice = 1 To iLastInvoice
iPrice = 0

On Error Resume Next ' in case not found
iPrice = Application.WorksheetFunction.Match(wsInvoice.Cells(iInvoice, 1).Value, wsItems.Columns(1), 0)
On Error GoTo 0

If iPrice > 0 Then
wsInvoice.Cells(iInvoice, 8).Value = wsItems.Cells(iPrice, 8).Value
Else
wsInvoice.Cells(iInvoice, 8).Value = ""
End If

Next iInvoice

Application.ScreenUpdating = True

End Sub

Paul_Hossler
03-14-2019, 03:12 PM
Not tested so you'll have to play around with this a bit




Option Explicit
Private Sub CommandButton1_Click()
Dim wbPrice As Workbook
Dim wsPrice As Worksheet

Dim wsItems As Worksheet, wsInvoice As Worksheet '??????
Dim iInvoice As Long, iLastInvoice As Long
Dim iPrice As Long

Application.ScreenUpdating = False

Set wsInvoice = ThisWorkbook.ActiveSheet

Workbooks.Open Filename:="C:\FileServer\CompanyDocs\My Excel\Price.xls"
Set wbPrice = ActiveWorkbook
Set wsPrice = ActiveSheet

ThisWorkbook.Activate

iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row

For iInvoice = 1 To iLastInvoice
iPrice = 0

On Error Resume Next ' in case not found
iPrice = Application.WorksheetFunction.Match(wsInvoice.Cells(iInvoice, 1).Value, wsPrice.Columns(1), 0)
On Error GoTo 0

If iPrice > 0 Then
wsInvoice.Cells(iInvoice, 8).Value = wsItems.Cells(iPrice, 8).Value
Else
wsInvoice.Cells(iInvoice, 8).Value = "Not Found"
End If
Next iInvoice

wbPrice.Close False

Application.ScreenUpdating = True

End Sub

lostinvb
03-14-2019, 03:41 PM
Paul, you are always my hero

corrected the following and the test run worked fine. (will run a couple more tests)


If iPrice > 0 Then
wsInvoice.Cells(iInvoice, 8).Value = wsPrice.Cells(iPrice, 8).Value
Else
wsInvoice.Cells(iInvoice, 8).Value = "Not Found"
End If

Paul_Hossler
03-14-2019, 03:48 PM
Paul, you are always my hero

<blush>

Good catch - hope the rest of the tests go OK

BTW, I've always found that I do better (fewer bugs) when I have to have multiple workbooks open to always Set …. them and to refer to them explicitly

I've seen macros trying to keep track of what the ActiveWorkbook is and it's just too much work -- let the computer keep track

lostinvb
03-14-2019, 05:15 PM
gotcha ....... I get lost with syntax when trying to specify but also in this case the invoices all have different names so naming them. But if it was a static invoice would the following be correct?


Set wsInvoice = "C:\FileServer\CompanyDocs\My Excel\Invoice.xls"

instead of:



Set wsInvoice = ThisWorkbook.ActiveSheet

(I hope this is what you were refering too)

Paul_Hossler
03-14-2019, 05:43 PM
Not really.

In this ...





Set wsInvoice = ThisWorkbook.ActiveSheet
Workbooks.Open Filename:="C:\FileServer\CompanyDocs\My Excel\Price.xls"
Set wbPrice = ActiveWorkbook
Set wsPrice = ActiveSheet





The WB containing the macro has the invoice WS selected, so I Set wsInvoice to that sheet

Opening another WB makes that the ActiveWorkbook so I Set wbPrice to that WB so I don't get confused between which WB is active; I can just refer to wbPrice and be sure with no assumptions

wbPrice has an ActiveWorksheet so I set wsPrice to THAT to be sure




But if it was a static invoice would the following be correct?




Set wsInvoice = "C:\FileServer\CompanyDocs\My Excel\Invoice.xls"



Probably not. You can't Set something to a non-Object, and
"C:\FileServer\CompanyDocs\My Excel\Invoice.xls" is a string

You can do something like



Set wsInvoice = ThisWorkbook.Worksheets ("Smith Invoice 2019-03-12")