locked
Excel VBA generated header not the same on different PC RRS feed

  • Question

  • Hi all,

    Recently I got a problem which indeed gives me headache.

    I got an Excel macro book, which write out invoice by a click of button, but my colleagues complains that all staffs got different problems about the header section.

    It runs fine on my PC, the header looks nice and fit, but not on my colleague's PC, the header goes weird.

    This is the nice header generated on my PC.


    Nice header

    And his one is the weird one from my colleague's PC

    Although the 2 invoice is from the same macro, and I have confirmed the result file has the same header margin and fonts (Of course because both 2 are from the same macro which has fonts and size defined).

    Could anyone please help, I have no clues of why such problem happens, I have tested from Excel 2010 to 2019, with combinations from Windows 7 to Windows 10 1903.

    Thursday, July 4, 2019 4:42 PM

All replies

  • Hello Takagi,

    First of all, it is almost impossible to provide help on forums for developers without seeing the real code. 

    Second, Excel provides the macro recorder which generates the required VBA code for you while you are doing manual actions in the application. I'd suggest using it on a problematic machine and then checking the results. Following that way you may find the cause on your own.


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Thursday, July 4, 2019 9:09 PM
  • Hello Takagi,

    First of all, it is almost impossible to provide help on forums for developers without seeing the real code. 

    Second, Excel provides the macro recorder which generates the required VBA code for you while you are doing manual actions in the application. I'd suggest using it on a problematic machine and then checking the results. Following that way you may find the cause on your own.


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Thank you.

    I took the screenshots to home and I have no code in my hands when post, here is the section which generates the header:

     Application.PrintCommunication = False
            With .PageSetup
                .PrintArea = ""
                .Zoom = 100
                .CenterHorizontally = True
                .PaperSize = xlPaperA4
                
                .LeftMargin = Application.CentimetersToPoints(1.2)
                .RightMargin = Application.CentimetersToPoints(1.2)
                .TopMargin = Application.CentimetersToPoints(3.6)
                .BottomMargin = Application.CentimetersToPoints(2.9)
                .HeaderMargin = Application.CentimetersToPoints(0.8)
                .FooterMargin = Application.CentimetersToPoints(0.8)
               
                .CenterHeaderPicture.Filename = "Path to company logo.png"
                .CenterHeader = "&G"
                
                .LeftHeader = Chr(10) & Chr(10) & Chr(10) & Chr(10) & Chr(10) & "&""Verdana""&9   Client : " & clnFullName & "御中"
                .RightHeader = Chr(10) & Chr(10) & Chr(10) & Chr(10) & "&""Verdana""&9Date : " & Format(Date, "d-mmm-yyyy") & "&KFFFFFF__" & Chr(10) & "&K000000Inv no. : " & invNumber & "&KFFFFFF__"
                
                .CenterFooter = "&""Verdana""&9My company Name" & Chr(10) & _
                        "My company address" & Chr(10) & Chr(10) & "&10&P / &N"
            
                .LeftFooter = vbNullString
                .RightFooter = vbNullString
                               

                    .Zoom = False
                    .FitToPagesWide = 1
                    .FitToPagesTall = 0

            End With
            Application.PrintCommunication = True

    Unfortunately I do not have control of my colleagues PC during working hours, and there is no way I can use his PC for few hours to trace the problem.

    Friday, July 5, 2019 1:20 AM