I made a VBA script that will read values from one sheet and create a "label" on another sheet.
This label is supposed to be printed on a special paper that is split in three parts.
Since I live in Sweden we use the A4 paper size (297x210 mm). The lables are supposed to be 99x210 mm.
This means each value needs to be printed on the exact position on the paper.
I do this for my company, thus all coputers are exactly the same.
Same model, same version of Windows, same version of Excel.
This is a smal part of the code (what is relevant to the positioning of text)
For i = 2 To Lastrow ' Location name Sheets("Etikett").Range("A" & intRad) = Sheets("Bins").Range("A" & i) With Sheets("Etikett").Range("A" & intRad & ":K" & intRad) .MergeCells = True .Font.Color = clr .Font.Size = 150 .Font.Bold = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .BorderAround Weight:=xlThick .Borders.Color = clr .Borders(xlEdgeLeft).Weight = xlThick ' this may look odd but is needed .Borders(xlEdgeRight).Weight = xlThick End With 'Checknumber Sheets("Etikett").Range("B" & intRad + 1) = Sheets("Bins").Range("B" & i) With Sheets("Etikett").Range("B" & intRad + 1 & ":D" & intRad + 1) .MergeCells = True .Font.Color = clr .Font.Size = 100 .NumberFormat = "00" .Font.Bold = True .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenter End With ' old location If Sheets("Bins").Range("E" & i) <> "" Then Sheets("Etikett").Range("K" & intRad + 1) = Sheets("Bins").Range("E" & i) With Sheets("Etikett").Range("K" & intRad + 1) .MergeCells = True .Font.Color = clr .Font.Size = 8 .Font.Bold = True .VerticalAlignment = xlBottom .HorizontalAlignment = xlLeft End With End If ' copy already premade barcode or generate barcode if not premade If Sheets("Bins").Cells(i, 2) < 100 Then Sheets("0-99").Select shp = "B" & Right("0" & Sheets("Bins").Cells(i, 2), 2) Sheets("0-99").Shapes(shp).Select Else Sheets("VBA").Select ThisWorkbook.ActiveSheet.Shapes.SelectAll Selection.Delete Code128Generate_v2 30, 0, 40, 2.5, ThisWorkbook.ActiveSheet, Sheets("Bins").Cells(i, 2), 200 ThisWorkbook.ActiveSheet.Shapes.SelectAll Selection.ShapeRange.Group.Select End If 'color the barcode Selection.ShapeRange.Line.ForeColor.RGB = clr Selection.Copy Sheets("Etikett").Select Sheets("Etikett").Range("G" & intRad + 1 & ":J" & intRad + 1).MergeCells = True ' Set rowheights Sheets("Etikett").Rows(intRad).RowHeight = 135 Sheets("Etikett").Rows(intRad + 1).RowHeight = 115 If Etikettcount Mod 3 = 0 Then ' if it's the last label on paper, no space is needed between this and the next. Range("G" & intRad + 1).Select intRad = intRad - 1 Else Sheets("Etikett").Rows(intRad + 2).RowHeight = 25 Range("G" & intRad + 1).Select End If ActiveSheet.Paste ' paste barcode Etikettcount = Etikettcount + 1 intRad = intRad + 3 End If Next i Keep in mind this is not all the code, but this is what copies the text and barcodes and places them on the sheet.
On my computer the output is as expected:
print output
On other computers the last character is slightly cut off and the vertical alignment is not correct.
As I wrote earlier I need the blank space between the lables to be at about 99 mm from the top and then 99 mm between them.
I have uploaded the full file if someone want to test it here: http://hoppvader.nu/docs/Streckkod.xlsm
Note that it's only module3 that is used, module2 is if you choose a checknumber "Checksiffra" other than 00-99.
Any help is appreciated on why it only works on my computer.
5 Answers
Answers 1
The output can be affected by many things like the printer's resolution, the desktop's resolution, the font or the size of the cells.
For instance, when I draw a 10cm by 10cm square shape on a new sheet, the printed result is a 10.5cm x 9.5cm rectangle even though the scaling is disabled in the page setup and in the advanced options.
To get an accurate output, one solution would be to draw the content on a Chart Sheet since any drawing on this type of sheet is printed to the exact size provided in centimeter :
Here's an example to add a Chart sheet and to create the labels:
Sub DrawLabel() ' add new empty Chart sheet ' Dim ch As Chart Set ch = ThisWorkbook.Charts.Add() ch.ChartArea.ClearContents ch.ChartArea.Format.Fill.Visible = msoFalse ch.ChartArea.Format.line.Visible = msoFalse ' setup page as A4 with no margin ' ch.PageSetup.PaperSize = xlPaperA4 ch.PageSetup.Orientation = xlPortrait ch.PageSetup.LeftMargin = 0 ch.PageSetup.TopMargin = 0 ch.PageSetup.RightMargin = 0 ch.PageSetup.BottomMargin = 0 ch.PageSetup.HeaderMargin = 0 ch.PageSetup.FooterMargin = 0 DoEvents ' force update ' ' add labels AddText ch, x:=0.5, y:=0.5, w:=19.9, h:=4.6, Color:=vbRed, Border:=3, Size:=150, Text:="DB136C" AddText ch, x:=2.5, y:=5.1, w:=5, h:=4, Color:=vbRed, Border:=0, Size:=100, Text:="79" AddText ch, x:=0.5, y:=10, w:=19.9, h:=4.6, Color:=vbGreen, Border:=3, Size:=150, Text:="DB317A" AddText ch, x:=2.5, y:=14.6, w:=5, h:=4, Color:=vbGreen, Border:=0, Size:=100, Text:="35" AddText ch, x:=0.5, y:=19.5, w:=19.9, h:=4.6, Color:=vbBlack, Border:=3, Size:=150, Text:="AA102A" AddText ch, x:=2.5, y:=24.1, w:=5, h:=4, Color:=vbBlack, Border:=0, Size:=100, Text:="10" End Sub Private Sub AddText(self As Chart, x#, y#, w#, h#, Color&, Border#, Size#, Text$) With self.Shapes.AddTextBox( _ msoTextOrientationHorizontal, _ Application.CentimetersToPoints(x) - 8, _ Application.CentimetersToPoints(y) - 8, _ Application.CentimetersToPoints(w), _ Application.CentimetersToPoints(h)) .line.Weight = Border .line.ForeColor.RGB = Color .line.Visible = Border <> 0 .TextFrame.VerticalAlignment = xlVAlignCenter .TextFrame.HorizontalAlignment = xlHAlignCenter .TextFrame2.TextRange.Font.Name = "Calibri" .TextFrame2.TextRange.Font.Size = Size .TextFrame2.TextRange.Font.Bold = msoTrue .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = Color .TextFrame2.TextRange.Text = Text End With End Sub Answers 2
Check the column width and compare your computers column widths with the others if they are different this is probably a font version issue:
- Check which font you use in your sheet.
- Check which version of the font is on your computer and which is on the other computer.
- Additionally check the font versions of Calibri and Cambria on both computers (because these are Microsoft's defaults).
Make sure you install the same font version on all computers!
The issue here is probably that Excel determines the column width by the character width (see Description of how column widths are determined in Excel). So if the font changes the column width changes.
I had some issues like that a while ago when Microsoft Update delivered a wrong font file that had a different character width. If one of these wrong files is on yours or one of the others computer then the column width is calculated wrong.
Also see: Why is Excel column Pixel width different on different machines, but same OS, same resolution, same Excel verison, etc.?
Answers 3
there should be an option when you go to print: "scale to fit" It might be in advanced options. On a mac I had to click "Show Details"
I used to so vba. And am a computer programmer. But the problem does not seem to be a code issue.
ps- you could probably find a way to enable "scale to fit" via macro. Here are some resources to check for a programming solution: https://www.ozgrid.com/forum/forum/help-forums/excel-general/5968-force-printing-macro-to-fit-page
https://www.experts-exchange.com/questions/28156905/VBA-Print-Code-Print-Area-Fit-on-one-page.html
excerpt from the above link solutioned by Patrick Matthews
With Worksheets("name").PageSetup .Zoom = False .FitToPagesTall = 1 .FitToPagesWide = 1 End With Responding to @Andreas, how about the code snippet?
additionally, remove the .FitToPagesTall
With Worksheets("name").PageSetup .Zoom = False .FitToPagesWide = 1 End With hopefully then it will not align vertically but still align horizontally.
Answers 4
I'd start by checking whether all print settings are set as desired (printers also have internal "default" print settings that can interfere with desired print). And if the font you're using is installed at work computers.
Then, I'd add following VBA code, to make sure the Excel print settings are the same on all computers (this is only to give you a hint and is a small portion of what can be set)
With Sheets("Etikett").PageSetup .PaperSize = xlPaperA4 .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .FitToPagesWide = 1 .FitToPagesTall = 1 End With And if the above does not help... Read below.
MS Office prints are a bit tricky thing. The problem is that Excel (and not only Excel) is not capable of generating print previews on its own, so what it does? It sends all the data to the printer (so it is the printer that does the job) and just "forwards" the results back. This is usually not a problem and goes unnoticed until someone tries to design pixel-perfect prints.
I had similar issue in my company, where we were using address labels for a post-mail stickers. After our printer broke down and we got ourselves a shiny new one (entirelly different model, brand etc) our templates were a mess and had to be re-aligned.
So all-in-all it could boil down to printer drivers...
Answers 5
It sounds like using the same file on a different computer is not your issue. The file is only one of many factors that affect a document's printed end-result.
Windows printer driver could be different versions on each of the computers (ie., was updated on one but not the other)
Windows printer settings could be slightly different between each of the computers.
I know you are adamant that the two computers are identical and that these settings cannot be changed, but difference like this on seemingly identical workstations happen all the time caused by any number of unexpected variables. (ie., "One time when a Windows Update was pushed to the two machines, one of them was accidentally powered off and didn't properly retrieve or install the update.")
There are a ton of buried printer settings and other variables that coulf be the potential cause of your issue, hidden away at various levels. (ie., system-level, device-level, application-level)
Below how to find three sets of properties that are likely the offender. Check all three places, from both computers, and compare the settings.
Device Manager
Hit the
Windows Key, type device mangerand push EnterDouble click
Imaging Devicesthen right-click your desired printer and choosePropertiesClick the
Drivertab, and take note of theDriver DateandDriver Version- Repeat these steps on the other computer to compare the driver date & version.
If they do not match, make them match. If you don't have access to any of these areas, or are unsure which option to change, check with your I.T. department.
Printer Manufacturer Settings
Hit the
Windows Key, type printersand push EnterRight-click the desired printer and choose
Printer PreferencesThe layout of this window depends on your printer's manufacturer. Check through all values on all tabs, looking for differences between the two settings on the two machines.
Printer Properties
Go to
Control Panel→Hardware and Sound→Devices and PrintersRight-click the desired printer and choose
Printer PropertiesCheck through all values on all tabs, looking for differences between the two settings on the two machines.
Finally, print a test page on the desired printer from both computers and inspect them closely for any differences (including version numbers).
0 comments:
Post a Comment