Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    130

    Unanswered: Output Access to Excel with changes to Spreadsheet

    I am using the following to output an A2K2 Report to a simple Excel SS

    DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, stOutPutName

    What I want to do is Open the XLS file, Delete Row 1 change the font and then save as a simple txt file. Using acFormatTXT seems to skip a few rows. Regardless, my intent is to do this from within A2K2.

    Is it possible? If no one knows, can someone point me in the correct direction?

    Thanks
    Winston

  2. #2
    Join Date
    Mar 2004
    Location
    UK
    Posts
    30
    it is possible. you can either write the macro in excel and then call it from access like this
    Code:
    'declarations
    Dim obExcel As Object
    'sets the Macro spreadsheet as the object
    Set obExcel = GetObject(c:\Macro.xls")
    'runs the excel format to tidy up download
    With obExcel.Application
        .DisplayAlerts = False
        .Windows("Macro.xls").Visible = True
        .Run "'Macro.xls'!GNSPER08" 'in this case GNSPER08 is the function name
        .Quit
    End With
    Set obExcel = Nothing
    or you can reference the Excel Objects dll from access by opening a module and going to Tools | References and use the soemthing like the following

    Code:
    Dim appExcel As New Excel.Application
    Dim wkbExcel As Excel.Workbook
    Dim wksExcel As Excel.Worksheet
    'hides the instance of excel
    appExcel.Visible = False
    'creates new workbook object
    Set wkbExcel = appExcel.Workbooks.Add
    'creates new worksheet
    Set wksExcel = wkbExcel.Worksheets.Add
    'names the new worksheet
    wksExcel.Name = "RawData"
    'initialises with loop for the worksheet object
    With wksExcel
        'adds something to the first cell
        .Range("A1") = "this is a test"
        'saves to temp folder
        .SaveAs "C:\Temp\Bob.xls"
    End With
    
    'closes excel instance
    appExcel.Quit
    'clear up
    Set appExcel = Nothing
    Set wkbExcel = Nothing
    Set wksExcel = Nothing
    in this case anything in the with loop is excel VBA.

    Hope this helps

  3. #3
    Join Date
    Apr 2004
    Posts
    130
    Thank you... I used some of your code and some other. But... I have a ss which runs a macro at open. Opens another ss, formats, saves as txt and closes. Works just fine when opened from within Excel. When I run it from within Access, it doesn't run the macro. Access vba as follows:

    Workbooks.Open FileName:="C:\FormatFile.XLS", ReadOnly:=True

    The "ReadOnly:=True" is there because it seems to get locked each time I run it, but that isn't a problem, so I will go on.

    The Macro in the Excel file "FormatFile.XLS" looks like this:

    Private Sub Auto_Open()
    Call FormatFile
    End Sub

    Function FormatFile()
    'format stuff
    end function

    Can't seem to figure out why it doesn't auto run the macro.
    Anyone have any ideas?
    thanks,
    Winston

  4. #4
    Join Date
    Mar 2004
    Location
    UK
    Posts
    30

    Wink

    try deleting the auto open code and put the formatfile function in a module then use this code in access to run the macro. i have attached a zip with a sample in it
    theres just one form with the code on a button click event. i added 97 2000 & 2002 versions. it worked in all three

    . hope it helps.
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2004
    Posts
    130
    Thanks, I got it working with your code exactly. The first sample. I am not an excel expert and perhaps that was my problem. Only problem left as follows:

    ss#1 is the one with the macro/vba which opens ss#2 created from A2K2 report. Works just fine. A2K2 opens ss#1, formats ss#2 properly and then closes ss#2. Only problem is that excel asks if I want to save ss#1 changes. Is there a way to turn this off (of course just for ss#1)?
    thanks,
    Winston

  6. #6
    Join Date
    Apr 2004
    Posts
    130
    OK, with a bit of messing around, I got it to work. Thanks for all your help. I guess I need to learn something about Excel... not my normal area, but your help was invaluable.
    Thanks again,
    Winston

Posting Permissions

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