Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2011
    Posts
    42

    Unanswered: changing excel heading from access vba code

    Good Day,
    I have an excel sheet (book1). some data are exported from access to that excel file (sheet1). export is working fine but I need to change the heading in the first row cells A1, B1,C1

    how to do that by vba code in access 2007

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Using Automation you can create an instance of Excel and work with it:
    Code:
    Sub UseRemoteExcelObject()
    '
    ' This needs a reference to Excel (here for Office 2003):
    ' Tools --> References --> Microsoft Excel 11.0 Object Library.
    '
        Dim xlapp As Excel.Application
        Set xlapp = New Excel.Application
    '
    ' It's also possible to use Late Binding.
    ' (This does not need a reference to Excel but is less efficient).
    '
    '    Dim xlApp As Object
    '    xlapp = CreateObject("Excel.Application")
    
        With xlapp
        
        ' Useful when you're debugging.
        '
            .Visible = True
        
        ' Open the Excel file.
        '
            .Workbooks.Open "C:\Custom Error Codes.xls"
        
        ' Select the proper sheet.
        '
            .Sheets("Sheet2").Select
        
        ' Select the upper-left cell (A1).
        '
            .Range("A1").Select
        
        ' Write something into it.
        '
            .Selection = "Something"
        
        ' Save the file.
        '
            .ActiveWorkbook.Save
        
        ' Then close it.
        '
            .Workbooks.Close
        
        ' Close Excel. !!! This is very important !!!
        ' If you do not property close the instance of Excel it will remain
        ' locked in memory with no visible interface (only Task Manager to kill it).
        '
            .Quit
        End With
        
        ' Clean up
        '
        Set xlapp = Nothing
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Mar 2011
    Posts
    42
    Thaaaaaaaaaaaaaanks boss

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome
    Have a nice day!

  5. #5
    Join Date
    Mar 2011
    Posts
    42
    Sinndho, It's more than perfect but excel is opening and showing the selected sheet
    any possibility to open it hidden?

  6. #6
    Join Date
    Mar 2011
    Posts
    42
    Dear Sinndho,
    I've changed the .visible=true to =false and the excel didn't open
    sorry for disturb, so kind of you

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you change .Visible to False Excel opens but you do not see it. If you want to be sure of it:

    1. Stop the code after the line:
    Code:
            .Workbooks.Open "C:\Custom Error Codes.xls"
            Stop ' <--
    2. Open the Task Manager (Ctl+Alt+Del). You'll notice that there is an instance of Excel running.

    3. To be more than sure, Kill this instance (from the Task Manager) then come back into your application and try to resume the execution of the function on the next line:
    Code:
            .Sheets("Sheet2").Select
    You'll receive an error because xlapp is now set to Nothing (the pointer became invalid):
    Run-time error '462':
    The remote server machine does not exist or is unavailable
    Have a nice day!

  8. #8
    Join Date
    Mar 2011
    Posts
    42
    Thanks for your advice,
    I appriciate alot your efforts

Posting Permissions

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