none
C# Excel - barcode "cell" in Excel header RRS feed

  • Question

  • Hi, I have a requirement to have a cell (or whatever it will be called) - in the Excel Header - with a barcode font (Code128) which will receive a number every time a new sheet is created and thus show the number as a barcode on every page of the report, in the header. Now of course I have googled quite a bit for this but find nothing of any use, as far as I can see.

    Can anyone enlighten me how this is done? It must be possible to display a variable, or a cell in the Header with a barcode that can be changed? (or an image - but if an image then how will it read the Barcode number?). Thanks for any help you can "render" (joke here).  

      
    Thursday, June 11, 2015 9:28 AM

Answers

  • HI,

    You have a more space in the Barcode_Header. You may delete the space to get the right bar code

    string Barcode_Header = "&\"Code 128\" &32 &" + dokIDBarCode;

    being

     string Barcode_Header = "&\"Code 128\"&32 " + dokIDBarCode;

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Sansevieria Friday, June 26, 2015 12:54 PM
    Friday, June 26, 2015 1:47 AM
    Moderator

All replies

  • Hello,

    I believe you would have a better chance for responses in the Excel for developers forum and if you like I can move your question to that forum.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    Thursday, June 11, 2015 9:35 AM
  • Yes please do. Thanks
    Thursday, June 11, 2015 1:15 PM
  • I have a requirement to have a cell (or whatever it will be called) - in the Excel Header - with a barcode font (Code128) which will receive a number every time a new sheet is created and thus show the number as a barcode on every page of the report, in the header. This would be quite easy if it was in a cell on the sheet but of course it is not - it must be contained within the Header and on the right hand side of it as well i.e. the right header should be right justified over to the right margin, not starting in the middle of the page as it seems to do. Now of course I have googled quite a bit for all of this but find nothing of any use, as far as I can see.

    Can anyone enlighten me how this is done? I have tried several combinations of Header symbols to no avail. It must be possible to display a variable, or a cell in the Header with a barcode that can be changed? (or an image - but if an image then how will it read the Barcode number?). Thanks for any help you can "render" (joke here).  

    Attempt:

    xlWS.PageSetup.RightHeader =

    "&"+ ""+ "Code128"+ ""+ "&16"+ dokNr;


    • Edited by Sansevieria Thursday, June 11, 2015 2:59 PM
    • Merged by L.HlModerator Monday, June 15, 2015 9:05 AM repeat thread
    Thursday, June 11, 2015 2:48 PM
  • Hi,

    >>the right header should be right justified over to the right margin, not starting in the middle of the page as it seems to do

    I have add headers for a sheet, the right headers are justified by the right margin, you may check the picture.

    Could you please check that if you add the header manually, what it will be, If it still starts from the middle, you may turn to TechNet forum for Excel for more professional and effective help, for the forum is for discussions about questions about Excel development. If not, could you please share the workbook by OneDrive if it is convenient

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 12, 2015 9:58 AM
    Moderator
  • Hi, Lan, thanks for your reply. The code I have corrected should be as follows, although I don't believe even so that it is translating it into Barcode font. I have corrected the escape characters for C#/Excel. The attempt below is to write the number in dokNr variable as a Barcode - and thus to display that in the right header. 

    xlWS.PageSetup.RightHeader = "&\"Code128\" &16" + dokNr;
    Now, how can I set that then over by the right margin - in (C# Excel) code? I have tried messing about with margins but that sems to mess things up even more. The number that is contained in the variable dokNr should in theory appear in Barcode font, but it doesn't when tested (thus far anyway).


    Friday, June 12, 2015 2:11 PM
  • Hi,

    Using C# code to set the page header, you may refer to the below codes.

                StringBuilder text1 = new StringBuilder();
                text1.Append(@"&""Code 128,Regular""");
                text1.Append(@""+ dokNr + "");
                ws.PageSetup.RightHeader = text1.ToString();

    The code will build a right header with a number being Code 128 font.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, June 15, 2015 8:38 AM
    Moderator
  • Hello Lan, superb, that works - I am getting a bar code, albeit rather small, at the top right corner of my Excel Header when printing. Now, I would like to increase the size so that hopefully it can be read by a bar code scanner and I have tired to find the other possible options to replace "Regular" in your text (i.e. I want something much larger and if possible to be able to actually increase or rather adjust up/down the font size by numbers as well).

    Can you tell me where this is documented (or give me a few clues) as to how I can increase the size of the Barcode "Image" by changing the font size?

      
    Monday, June 15, 2015 11:30 AM
  • Hi,

    You may could try the following code to increase the font size

      text1.Append(@"&""Code 128,Regular""");
      text1.Append(@"&16 "+ nbr + "");
    Hope this could help you

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 17, 2015 1:59 AM
    Moderator
  • Thank you very much. I will give that a go. Looks good.

    By the way I am getting all my text for menu items in Chinese (I assume). I assume this is just a temporary Microsoft glitch? 

    Thursday, June 18, 2015 3:01 PM
  • Hi,

    Since your initial issue has solved, for the new question, I will suggest you open a new case , so the community members will be involved and help you on the new case.

    Thanks for your understanding.

    Best Regards,

    Lan 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 23, 2015 4:33 PM
    Moderator
  • Hello Lan, I have another relatively small problem with the barcode in that I gave your suggestion in to the code but I am getting a much tinier beginning few bars to the normal Barcode, as you can see below. i think it is inserting the font size firstly and then the bar code (which is now encoded as a bar code) - which then appears correctly.

    Here is my slightly altered code from yours above (I have been experimenting to see what happens - your code brought the same results however). Can you see if there is anything wrong with the macro code - I still need to print the barcode in a large font but it seems that the macro code throws the number 32 into the equation which results in the tiny few bars at the beginning. The current line of code I am using is that line without C# // comment Outs. I have a feeling I am 1 escape character away from a solution! Hopefully you can see it.

    //StringBuilder Barcode_Header = new StringBuilder();     // Als RightHeader.
    //Barcode_Header.Append(@"&""Code 128,Regular""");        // Barcode = Code128
    //Barcode_Header.Append(@"&32 " + dokIDBarCode + "");     // Fontgrosse = 32 jetzt - 16 ist immer noch zu klein.
    
    // "&\"Calibri\" &8 Anlage zum Lademittel-Abschlusskonto:" + ktoNr + " per " + Stichtag + " zu Dokument-ID " + dokIDString;
    string Barcode_Header = "&\"Code 128\" &32 &" + dokIDBarCode;     // Fontgrosse = 32 jetzt - 16 ist immer noch zu klein.
    //string Barcode_Header = "&\"Code 128\"" + dokIDBarCode; 



    • Edited by Sansevieria Wednesday, June 24, 2015 11:14 AM
    Wednesday, June 24, 2015 11:04 AM
  • Hello I have a relatively small problem with placing a barcode in an Excel Header (from C# code) but I am getting a much tinier beginning few bars to the normal barcode, as you can see below. I think it is inserting the font size firstly or some special character and then the bar code (which is now encoded as a bar code) - which then appears correctly.

    Here is my code (I have been experimenting to see what happens. Can anyone see if there is anything wrong with the macro code - I still need to print the barcode in a large font but it seems that the macro code throws the number 32 (or some special (Office-produced?) character) into the equation which results in the tiny few bars at the beginning or some control characters from Excel are being thrown into the equation. The current line of code I am using is that line without the C# // comment outs. I have a feeling I am 1 escape character away from a solution! Hopefully you can see it. The variable "dokIDBarCode" containing the characters for the barcode is already encoded in Code 128 for barcode font.

    If anyone can provide a VBA solution that also would be good as hopefully I can transpose it into C#. In fact - how would this be done in VBA? That might give me a clue. C# corrected code would be great as well if possible.

    //StringBuilder Barcode_Header = new StringBuilder(); // Als RightHeader. //Barcode_Header.Append(@"&""Code 128,Regular"""); // Barcode = Code128 //Barcode_Header.Append(@"&32 " + dokIDBarCode + ""); // Fontgrosse = 32 jetzt - 16 ist immer noch zu klein. // THIS WORKS "&\"Calibri\" &8 Anlage zum Lademittel-Abschlusskonto:" + ktoNr + " per " + Stichtag + " zu Dokument-ID " + dokIDString;

    The following string does not work - why?

    string Barcode_Header = "&\"Code 128\" &32 " + dokIDBarCode; // Fontgrosse = 32 jetzt - 16 ist immer noch zu klein.




    • Edited by Sansevieria Thursday, June 25, 2015 1:01 PM
    • Merged by L.HlModerator Friday, June 26, 2015 1:51 AM releated Case
    Thursday, June 25, 2015 12:55 PM
  • HI,

    You have a more space in the Barcode_Header. You may delete the space to get the right bar code

    string Barcode_Header = "&\"Code 128\" &32 &" + dokIDBarCode;

    being

     string Barcode_Header = "&\"Code 128\"&32 " + dokIDBarCode;

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Sansevieria Friday, June 26, 2015 12:54 PM
    Friday, June 26, 2015 1:47 AM
    Moderator
  • Hallo Lan,

    your solution did actually help but I have gone another way now and simply put the barcode and other Header Information in cells in an inserted row, copied them over crossways firstly to every new page after a vertical page break and then copied/inserted the whole row thus made after every horizontal page break and it now works fine. I took the barcode and other header Information out of the print header and placed them all on worksheet rows repeating tem according to the number of pages I had. I didn't have to worry then (after the first calculations) about how many pages crossways I had as I was then copying the whole row down the page every time. I also enlarged the barcode to be over 3 cells by merging them and then the barcode produced could be read by a barcode reader.  Problem finally solved.

    Motto: When one has problems reading a barcode in a header - put it in a cell on the worksheet (Code 128 and size etc.) and repeat this row as often as necessary as the first row on every new page, if one wishes such of course.

     

    Friday, June 26, 2015 12:53 PM