Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: access vba to write excel header and footer

    Hi Gurus,

    I need to use access vba to write excel header and footer. When I execute the following code, it opened up the excel file. If I did view -> header and footer, I can see the code did write the header and footer. When I try to close the excel, it asked me to save the file. If I did not save the file, the next time I opened it, the header and footer disappeared.
    If I comment out the line xlBook.Application.Visible = True, it did not open the excel file and when I open the excel file manually, there is no header and footer.

    I need the code to save the header and footer behind the scene without opening it. How to change the code?

    Your help will be greatly appreciated!

    Thank you!

    Private Sub cmdentermain_Click()
    Dim ExcelApp As New Excel.Application
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim varInquiryID As Long
    Dim varCentreFooter As String
    Dim sHeader As String


    Set xlApp = CreateObject("Excel.Application")

    xlApp.DisplayAlerts = False
    'Set reference to Workbook object
    Set xlBook = xlApp.Workbooks.Open("c:\file1.xls")


    varCentreFooter = " project # "

    Set xlSheet = xlBook.Sheets("file1")
    xlSheet.PageSetup.CenterFooter = varCentreFooter

    xlSheet.PageSetup.CenterHeader = "&BProject requirement&B" & vbCrLf & "Test it"

    sHeader = " &BFile Number:&B " & "TestFile" & Chr(10) & _
    " &BOriginator:&B " & "Test view" & Chr(10) & _
    " &BOrigination Date:&B " & Format(Date, "Short Date")

    xlSheet.PageSetup.RightHeader = sHeader




    xlApp.DisplayAlerts = False
    xlApp.ActiveWorkbook.Save
    xlBook.Application.Visible = True
    'xlApp.ActiveWorkbook.Close



    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Have you tried

    xlApp.ActiveWorkbook.Close SaveChanges:=True

    This should eliminate the request to save the file with or without Excel being visible.

    HTH

    MTB

  3. #3
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by MikeTheBike View Post
    Hi

    Have you tried

    xlApp.ActiveWorkbook.Close SaveChanges:=True

    This should eliminate the request to save the file with or without Excel being visible.

    HTH

    MTB
    MTB,

    Thanks again for helping. You have a very kind heart in helping others.

    I commented out
    xlApp.ActiveWorkbook.Save
    xlBook.Application.Visible = True

    and add the line you suggested

    xlApp.ActiveWorkbook.Close SaveChanges:=True

    It still comes out a windows Save as dialog box.
    I am using Access 2000.
    Any suggestion will be greatly appreciate!

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Are you realy getting the SAVE AS dialogue box or just a request to save the file. I can see not reason whay you would get the Save ASs dialogue as you have not opened a template !?

    Needles to say it works for me. My only suggested left is, try this

    xlBook.Close SaveChanges:=True

    After that I'm stumped!!

    Ho, perhaps you should close the instance of Excel after closing the file.

    xlApp.Quit ??

    MTB

  5. #5
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by MikeTheBike View Post
    Hi

    Are you realy getting the SAVE AS dialogue box or just a request to save the file. I can see not reason whay you would get the Save ASs dialogue as you have not opened a template !?

    Needles to say it works for me. My only suggested left is, try this

    xlBook.Close SaveChanges:=True

    After that I'm stumped!!

    Ho, perhaps you should close the instance of Excel after closing the file.

    xlApp.Quit ??

    MTB
    I did get a Save as box as the screen shot in the attached file.
    Attached Files Attached Files

  6. #6
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by MikeTheBike View Post
    Hi

    Are you realy getting the SAVE AS dialogue box or just a request to save the file. I can see not reason whay you would get the Save ASs dialogue as you have not opened a template !?

    Needles to say it works for me. My only suggested left is, try this

    xlBook.Close SaveChanges:=True

    After that I'm stumped!!

    Ho, perhaps you should close the instance of Excel after closing the file.

    xlApp.Quit ??

    MTB
    MTB,

    I do implement the code with xlBook.Close SaveChanges:=True
    and xlApp.Quit but still get the windows save as dialog box.

    I think some how the access vba open it behind the scene without releasing it and cause the file to be read only. So it always ask to save as and to replace the existing file.

    Why you don't have problem but I got the dialog box every time? Is it the version of Access difference or the windows operating system make the difference?

    Any idea on how to release the Excel by vba code?

    Thank you very much!

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Sorry, I cannot replicate this behaviour, and I have no idea why, so I cannot help any further.
    I would be interested if anyone else could solve it though.

    MTB

Posting Permissions

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