Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178

    Exclamation Unanswered: remove empty lines from txt file

    Hi Everyone
    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?

    thanks in advance
    ActionAnt

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

    Code:
    Sub runExcelMacro()
        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)
        
        xlsheet.Range("A1:A100").Select
        
        xlBook.Application.Run ("SPECNAMEforWLreport.XLS!NAMESPEC")
        xlApp.Visible = True
        xlApp.UserControl = True
        
        Set xlsheet = Nothing
        Set xlBook = Nothing
        Set xlApp = Nothing
        
    End Sub
    HTH
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •