Running Excel VB Code From Access

[Rob_U]

Hi

I have an issue with an access form I have created, which allows any csv file to be imported without any errors. The only issue is the csv file being imported, needs to have a field titled 'Ref' with auto populated numbers in the data before importing, which means I manually need to add this each time (I can't do this after importing, as this is required to keep the structure)



I've created a vb code in excel, which does this job for me successfully. However is there any way of running this vb code from access?
I know how to run a macro which is already saved in the excel file from access, but this needs to work with any csv file to import

 Sub Ref()

'Inserts an auto number
Dim r As Range
Columns(1).Insert Shift:=xlToRight
For Each r In ActiveSheet.UsedRange.Rows
Cells(r.Row, 1) = r.Row
Next

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Ref"

End Sub

Thank you for your help

[June7]

Bing: Access run Excel macro
Review http://datapigtechnologies.com/blog/...xcel-workbook/

[Rob_U]

Thank you for the link, but I already understand how to do that.
The issue is that the macro is not saved in the excel document, so the actual macro script needs to be located in access (I assume the only way?)
Is it possible to call a macro script saved in the access database to run on any excel document?

Thanks
Rob

[trevor40]

[QUOTE=Rob_U;276635]Thank you for the link, but I already understand how to do that.
The issue is that the macro is not saved in the excel document, so the actual macro script needs to be located in access (I assume the only way?)
Is it possible to call a macro script saved in the access database to run on any excel document?
the easiest way is to just use access vba to do it all,

first create a link to the excel file
then use the code you showed to update the sheet
the import the updated file

you will find examples of doing the above in my previous posts.
i have posted many answers on how to manipulate excel via access.

[June7]

trevor, AFAIK, cannot edit cells in a linked worksheet. Do you actually mean open objects in VBA?

Open Excel objects in VBA then can manipulate the objects to edit the content. Yes, this code can be dynamic to work on any worksheet that has the same structure expected by the code. Just need to dynamically specify the workbook path/filename.