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.
Microsoft ExcelMicrosoft AccessVB ScriptVBA

Avatar of undefined
Last Comment
SASnewbie

8/22/2022 - Mon
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
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
SASnewbie

ASKER
The notes are updated for the corresponding Position No and EMPLID.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Rgonzo1971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SASnewbie

ASKER
Thanks! I'll give it a try!
SASnewbie

ASKER
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")
Rgonzo1971

Can you debug.print?
.Fields("EFF DT")
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SASnewbie

ASKER
I'll try it now
Rgonzo1971

Is the field Name right?
Rgonzo1971

In your table it is EFF_DT pls correct line 19
Your help has saved me hundreds of hours of internet surfing.
fblack61
SASnewbie

ASKER
the field was EFF_DT...

It works beautifully!

Thanks!!