Link to home
Start Free TrialLog in
Avatar of SASnewbie
SASnewbie

asked on

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.
Avatar of Rgonzo1971
Rgonzo1971

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
Avatar of SASnewbie

ASKER

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
The notes are updated for the corresponding Position No and EMPLID.
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks! I'll give it a try!
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")
Can you debug.print?
.Fields("EFF DT")
I'll try it now
Is the field Name right?
In your table it is EFF_DT pls correct line 19
the field was EFF_DT...

It works beautifully!

Thanks!!