Register to reply

VBA with Access and Excel Help

by x62350
Tags: access, excel
Share this thread:
Dec20-11, 10:58 AM
P: 1
This is a VBA problem inside access involving an excel sheet.

I have an Access Table "sOrderTable" with [Unique ID], [Resource Group ID], [Priority] columns.

I am trying to Identify "Unique IDs" in Column A of the EXCEL SHEET and where they match [Unique ID] in the ACCESS TABLE matching and pasting[Resource Group ID] and [Priority] from ACCESS into columns AP and AQ in EXCEL.

The current error is 13 type mismatch on the row defining uID. I have tried labeling uID as String, Integer, Long and changing the range from A2:A to just A2

'Open Target File
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open(sTargetCombine)
Set xlSheet = xlBook.Worksheets("Tracking")

'Select Data to copy paste and compare
Dim rID As Integer 'Resource ID
Dim pID As Integer 'Priority ID
Dim uID As String 'Unique Id

'Select First Column
xlSheet.Range(Selection, Selection.End(xlDown)).Select
i = Selection.Rows.Count

For i = 2 To Rows.Count

uID = xlSheet.Range("A2:A" + Trim(Str(i))).Value
rID = DLookup("[Resource Group ID]", sOrderTable, "[Unique ID] =" & uID)
pID = DLookup("[Priority]", sOrderTable, "[Unique ID] =" & uID)


xlSheet.Range("AP2" + Trim(Str(i))) = rID
xlSheet.Range("AQ2" + Trim(Str(i))) = pID
Phys.Org News Partner Science news on
Physical constant is constant even in strong gravitational fields
Montreal VR headset team turns to crowdfunding for Totem
Researchers study vital 'on/off switches' that control when bacteria turn deadly

Register to reply

Related Discussions
Getting into a UK uni via an Access Course Academic Guidance 9
Fortran: help with I/O in Direct Access and Seq. access Programming & Computer Science 0
Access PF General Discussion 10