VBA with Access and Excel Help


by x62350
Tags: access, excel
x62350
x62350 is offline
#1
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("A1").Select
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)

Next

xlSheet.Range("AP2" + Trim(Str(i))) = rID
xlSheet.Range("AQ2" + Trim(Str(i))) = pID
Phys.Org News Partner Science news on Phys.org
SensaBubble: It's a bubble, but not as we know it (w/ video)
The hemihelix: Scientists discover a new shape using rubber bands (w/ video)
Microbes provide insights into evolution of human language

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