Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

VBA with Access and Excel Help

  1. Dec 20, 2011 #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
  2. jcsd
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook

Can you offer guidance or do you also need help?
Draft saved Draft deleted