Update Excel worksheet with results from Access table using VBA

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.
SASnewbieData Analyst - Project Management OfficeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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 OfficeAuthor 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 OfficeAuthor Commented:
The notes are updated for the corresponding Position No and EMPLID.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Rgonzo1971Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SASnewbieData Analyst - Project Management OfficeAuthor Commented:
Thanks! I'll give it a try!
SASnewbieData Analyst - Project Management OfficeAuthor 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")
Rgonzo1971Commented:
Can you debug.print?
.Fields("EFF DT")
SASnewbieData Analyst - Project Management OfficeAuthor Commented:
I'll try it now
Rgonzo1971Commented:
Is the field Name right?
Rgonzo1971Commented:
In your table it is EFF_DT pls correct line 19
SASnewbieData Analyst - Project Management OfficeAuthor Commented:
the field was EFF_DT...

It works beautifully!

Thanks!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.