PrintOut Loop

Rostuse

New Member
Joined
Jan 15, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
Dears,

I'm making a simple macro for ZPL printer. (But I'm not so good with it, it is my first time actually)
I have a code of 9 lines that needs to be printed (2nd sheet)
On the 1st sheet I have a Code of the product and Serial Number, start from X and stop until X.

The macro I am trying to make is to print the first serial number inserted, after that add +1 on the same and print it all until it meets the Stop Until X number.

VBA Code:
Sub Print_Loop()

Do Until Sheets("Foglio1").Range("A2") = Sheets("Foglio1").Range("A3")
    Sheets("Foglio2").Range("A1:A9").PrintOut
    MsgBox "Want to Continue?", vbOKCancel
    Sheets("Foglio1").Range("A2") = Sheets("Foglio1").Range("A2") + 1
Loop

End Sub

I will really appreciate for your help.

Thanks
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Rostuse

New Member
Joined
Jan 15, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
Here is the XL2BB Mini-sheet report.

Thanks in advance. (And sorry for double post, I cant edit the first one.)

ZPL2 - Copy.xlsm
E
6
Foglio1


ZPL2 - Copy.xlsm
E
17
Foglio2
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Not sure, but how about

VBA Code:
Sub Rostuse()

    Dim i As Long

    For i = Sheets("Foglio1").Range("A2") To Sheets("Foglio1").Range("A3")
        Sheets("Foglio2").Range("A1:A9").PrintOut
        If MsgBox("Want to Continue?", vbOKCancel) = vbCancel Then
            Exit For
        End If
    Next i
End Sub
 

Rostuse

New Member
Joined
Jan 15, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
Not sure, but how about

VBA Code:
Sub Rostuse()

    Dim i As Long

    For i = Sheets("Foglio1").Range("A2") To Sheets("Foglio1").Range("A3")
        Sheets("Foglio2").Range("A1:A9").PrintOut
        If MsgBox("Want to Continue?", vbOKCancel) = vbCancel Then
            Exit For
        End If
    Next i
End Sub
Thank you for your fast response @GWteB, but unfortunately did not add +1 on the value, it printed the documents I set 5 in this case, but with the same value.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Had to guess since your post #2 examples are empty ;)
On your worksheet select the area you want to post and capture that range using te XL2BB tool.
 

Rostuse

New Member
Joined
Jan 15, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
Had to guess since your post #2 examples are empty ;)
On your worksheet select the area you want to post and capture that range using te XL2BB tool.
I didn't knew that sorry, thank you @GWteB !

Test.xlsm
AB
1COMC0350080Code
21N. Serie (Start)
35N. Serie (Stop)
4
5
6
7
Foglio1

Test.xlsm
A
1^XA
2^BY5,2,5^FO15,15^BQN,3,10
3^FDCOMC0350080-1^FS
4^FWB
5^CFO,30
6^FT280,230^FDCOMC0350080^FS
7^FT310,230^FD1^FS
8^FO240,20^GB0,210,2,b,0^FS
9^XZ
Foglio2
Cell Formulas
RangeFormula
A3A3=C2&Foglio1!$A$1&C4&Foglio1!$A$2&C6
A6A6=C7&Foglio1!$A$1&C8
A7A7=C9&Foglio1!$A$2&C6
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I see, try

VBA Code:
Sub Rostuse_r2()

    Dim i As Long

    For i = Sheets("Foglio1").Range("A2") To Sheets("Foglio1").Range("A3")
        Sheets("Foglio2").Range("A1:A9").PrintOut
        If MsgBox("Want to Continue?", vbOKCancel) = vbCancel Then
            Exit For
        End If
        Sheets("Foglio1").Range("A2") = i + 1
    Next i
End Sub
 

Rostuse

New Member
Joined
Jan 15, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
I see, try

VBA Code:
Sub Rostuse_r2()

    Dim i As Long

    For i = Sheets("Foglio1").Range("A2") To Sheets("Foglio1").Range("A3")
        Sheets("Foglio2").Range("A1:A9").PrintOut
        If MsgBox("Want to Continue?", vbOKCancel) = vbCancel Then
            Exit For
        End If
        Sheets("Foglio1").Range("A2") = i + 1
    Next i
End Sub
Thank you so much @GWteB, this is it !
 

Rostuse

New Member
Joined
Jan 15, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
I see, try

VBA Code:
Sub Rostuse_r2()

    Dim i As Long

    For i = Sheets("Foglio1").Range("A2") To Sheets("Foglio1").Range("A3")
        Sheets("Foglio2").Range("A1:A9").PrintOut
        If MsgBox("Want to Continue?", vbOKCancel) = vbCancel Then
            Exit For
        End If
        Sheets("Foglio1").Range("A2") = i + 1
    Next i
End Sub
PS: Now I learned that Zebra printer prints the ZPL code only from notepad.exe :(
Is there a way to save the output in txt file instead and than send the txt to the default printer?

Thank you again !
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome. See if the code below circumvents the limitation of your printer.

VBA Code:
Sub Rostuse_r3()

    Dim i As Long, oWs As Worksheet, sFile As String, rRng As Range

    With ThisWorkbook

        Set rRng = .Sheets("Foglio2").Range("A1:A9")

        For i = .Sheets("Foglio1").Range("A2") To .Sheets("Foglio1").Range("A3")

            With Application

                .ScreenUpdating = False
                .DisplayAlerts = False

                Set oWs = .Workbooks.Add.Worksheets(1)
                rRng.Copy
                oWs.Range("A1").PasteSpecial Paste:=xlPasteValues
                .CutCopyMode = False

                sFile = Environ("tmp") & "\ZPLcode_" & Replace(Now, ":", "") & ".txt"
                oWs.Parent.SaveAs Filename:=sFile, FileFormat:=xlText, CreateBackup:=False
                oWs.Parent.Close

                Shell ("notepad.exe /p " & sFile)

                .DisplayAlerts = True
                .ScreenUpdating = True
            End With

            If MsgBox("Want to Continue?", vbOKCancel) = vbCancel Then
                Kill sFile
                Exit For
            Else
                Kill sFile
            End If

            .Sheets("Foglio1").Range("A2") = i + 1
        Next i
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,940
Members
416,953
Latest member
prakashkumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top