Update Excel worksheet with results from Access table using VBA

SASnewbie
SASnewbie used Ask the Experts™
on
Hello Experts,
I need assistance in writing VBA to transfer data from an access table to specific cells in an existing Excel file. This process is updating the worksheet with results from Access.
Below is an example of what I am trying to do, update the worksheet in the M&I Notes cells with the comment “ADD “ & EFF_DT.
Access table:

POSITION NO      EMPLID      FULL NAME            POSITION TYPE      EFF_DT
1234567                     1000001      LAST, FIRST            Team Member      3/6/2016
2345678                     1000002      LAST, SECOND    Team Member      3/6/2016
3456789                     1000003      LAST, THIRD            Team Member      3/10/2016
4567891                     1000004      LAST, FOURTH     Team Member      3/14/2016
5678912                     1000005      LAST, SIXTH            Team Member      3/14/2016

Excel Workbook:
                  
Position Type      M&I Notes      POSITION NO      EMPLID      FULL NAME
Team Member                             1234567                     1000001      LAST, FIRST
Team Member                             2345678                     1000002      LAST, SECOND
Team Member                             3456789                   1000003      LAST, THIRD
Team Member                             4567891                     1000004      LAST, FOURTH
Team Member                             5678912                     1000005      LAST, FIFTH

Ultimate results:
            
Position Type      M&I Notes      POSITION NO      EMPLID      FULL NAME
Team Member       ADD 3/6/2016      1234567              1000001      LAST, FIRST
Team Member       ADD 3/6/2016      2345678              1000002      LAST, SECOND
Team Member       ADD 3/10/2016      3456789              1000003      LAST, THIRD
Team Member       ADD 3/14/2016      4567891              1000004      LAST, FOURTH
Team Member       ADD 3/14/2016      5678912              1000005      LAST, FIFTH

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Hi,

Wouldn't be easier to create a query to have the wanted result and then Export the result to Excel thanks

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "myQuery1", outputFileName , True

Open in new window


Regards
SASnewbieData Analyst - Project Management Office

Author

Commented:
Thank you for responding so quickly.

The example I provided of the Excel workbook is a pared down version. The actual worksheet has 73 columns, where the columns are located as such:

T: Position No
X: EMPLID
Z: FULL NAME
D: Position Type
H: M&I Notes
SASnewbieData Analyst - Project Management Office

Author

Commented:
The notes are updated for the corresponding Position No and EMPLID.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016
Commented:
You could try something like this

Sub Macro()
Set appXL = CreateObject("Excel.Application")
appXL.Visible = True
Set fileXL = appXL.Workbooks.Open("c:\myXlFile.xlsm")
Set db = CurrentDb
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)
With rs
If Not (rs.BOF And rs.EOF) Then
  rs.MoveFirst
  While Not rs.EOF
    On Error Resume Next
    strPosNr = .Fields("POSITION NO")
    strEmplID = .Fields("EMPLID")
    Res = 0
    On Error Resume Next
    Res = appXL.Evaluate("=MATCH(" & strPosNr & "&" & strEmplID & ",T:T&X:X,0)")
    On Error GoTo 0
    If Res Then
        fileXL.sheets("Sheet1").Range("H" & Res).Value = "ADD " & Format(.Fields("EFF DT"), "mm/dd/yyyy")
    End If 
    rs.MoveNext
  Wend
End If
End With
rs.Close
Set rs = Nothing
fileXL.Close True
appXL.Quit
Set appXL = Nothing
End Sub

Open in new window

Regards
SASnewbieData Analyst - Project Management Office

Author

Commented:
Thanks! I'll give it a try!
SASnewbieData Analyst - Project Management Office

Author

Commented:
Hi,
I receiving the error "Run-Time error '3265': Item not found in this collection on the following line:

fileXL.sheets("Master List - ALL").Range("B" & Res).Value = "ADD " & Format(.Fields("EFF DT"), "mm/dd/yyyy")
Top Expert 2016

Commented:
Can you debug.print?
.Fields("EFF DT")
SASnewbieData Analyst - Project Management Office

Author

Commented:
I'll try it now
Top Expert 2016

Commented:
Is the field Name right?
Top Expert 2016

Commented:
In your table it is EFF_DT pls correct line 19
SASnewbieData Analyst - Project Management Office

Author

Commented:
the field was EFF_DT...

It works beautifully!

Thanks!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial