I have a macro in excel which I use to remove empty lines from a txt file.
I would like to run this from Access rather. I tried to copy the macro's code into Access, but that wouldn't work because the code refers to specific cells in the Excel Spreadsheet.
Can I get around this by specifying the nae of the Excel file in there somewhere? if so How?
If not that way, does anyone know an easier way how to do it from Access?
To run an Excel Macro in Access there are several things that need to firstly add a refrence to Excel, tools -> refrences
then fully qualify each of your statements
then you can run excel code from access
Here is a quick example
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("H:\Excel\FY0405\GENERAL\MACRO\SPECNAMEforWLreport.xls")
Set xlsheet = xlBook.Worksheets(1)
xlApp.Visible = True
xlApp.UserControl = True
Set xlsheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing