VBA with Access and Excel Help

  • Thread starter x62350
  • Start date
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

Want to reply to this thread?

"VBA with Access and Excel Help" You must log in or register to reply here.

Related Threads for: VBA with Access and Excel Help

Physics Forums Values

We Value Quality
• Topics based on mainstream science
• Proper English grammar and spelling
We Value Civility
• Positive and compassionate attitudes
• Patience while debating
We Value Productivity
• Disciplined to remain on-topic
• Recognition of own weaknesses
• Solo and co-op problem solving