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
 

Rostuse

New Member
Joined
Jan 15, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
You are welcome. See if the code below circumvents the limitation of your printer.
Thank you @GWteB, but it gives me an error with this code.

I do not want to disturb you anymore, thank you :)
 

Attachments

  • error 1004.png
    error 1004.png
    4.2 KB · Views: 3
  • error.png
    error.png
    33.9 KB · Views: 3

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
That's some kind of odd since I have tested the code. It works for me and we are both using the same Excel version. Try to change the line on which the error occurs:

Rich (BB code):
oWs.Parent.SaveAs Filename:=sFile, FileFormat:=xlTextMSDOS , CreateBackup:=False

EDIT: What does the sFile variable look like?
 

Rostuse

New Member
Joined
Jan 15, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
That's some kind of odd since I have tested the code. It works for me and we are both using the same Excel version. Try to change the line on which the error occurs:

Rich (BB code):
oWs.Parent.SaveAs Filename:=sFile, FileFormat:=xlTextMSDOS , CreateBackup:=False

EDIT: What does the sFile variable look like?
Its the same GWteB, the output file opens with excel again, no as txt file.

Thank you !
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    89.8 KB · Views: 3

GWteB

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

the output file opens with excel again, no as txt file.
That is as intended. A new workbook with a blank worksheet is created to copy the range A1:A9 accross.
If your source worksheet is saved as a text file it would contain also the data of the other cells (in column C are populated cells according to your formulas), and that is unwanted.
Note that it is hardly noticeable that notepad.exe is started and sends its data to the printer. I tested the code by setting a PDF printer as default so that I could see if it was actually printing (after all, I don't have a ZPL printer).
 
Last edited:

Rostuse

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

ADVERTISEMENT

That is as intended. A new workbook with a blank worksheet is created to copy the range A1:A9 accross.
If your source worksheet is saved as a text file it would contain also the data of the other cells (in column C are populated cells according to your formulas), and that is unwanted.
Note that it is hardly noticeable that notepad.exe is started and sends its data to the printer. I tested the code by setting a PDF printer as default so that I could see if it was actually printing (after all, I don't have a ZPL printer).
I tried even with Microsoft Print to PDF as default, but still the same.
My office 2013 is in Italian language, maybe that changes something?
Thank you either way GWteB, I really appreciate your help, and sorry for taking your time.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
My office 2013 is in Italian language, maybe that changes something?
That shouldn't make any difference.
I would like you to go into the VBE, adjust the code as per below, open the Immediate Window (CTRL G key), step through the code (F8 key) and report back to what the sFile variabele evaluates.
Rich (BB code):
                sFile = Environ("tmp") & "\ZPLcode_" & Replace(Now, ":", "") & ".txt"
Debug.Print sFile        ' <<<<<< add this line here
 

GWteB

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

ADVERTISEMENT

In addition to my previous post and however unlikely, it could be that the Windows OS in your system cannot find the executable Notepad.exe (but does not report it).
Usually this file is located in the folder C:\windows\system32. You could try to amend the involved code accordingly:
VBA Code:
                Shell ("C:\windows\system32\notepad.exe /p " & sFile)
 

Rostuse

New Member
Joined
Jan 15, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
That shouldn't make any difference.
I would like you to go into the VBE, adjust the code as per below, open the Immediate Window (CTRL G key), step through the code (F8 key) and report back to what the sFile variabele evaluates.
Rich (BB code):
                sFile = Environ("tmp") & "\ZPLcode_" & Replace(Now, ":", "") & ".txt"
Debug.Print sFile        ' <<<<<< add this line here
Done.

But I tried to look for the Folder ZPLcode_ and the file, they are not there.

Maybe because I am in domain?

Cant the file path be changed in C for example?

Thanks
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    174.8 KB · Views: 2

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Aha, many apologies!
I should have known that using the Now statement, depending on locale, could result in prohibited characters in the file name.
Replace this line
VBA Code:
sFile = Environ("tmp") & "\ZPLcode_" & Replace(Now, ":", "") & ".txt"
by this line
VBA Code:
sFile = Environ("tmp") & "\" & ValidateFileName("ZPLcode_" & Now & ".txt")

and paste the following in your code module. That way your issue should be busted. If not, let me know.

VBA Code:
Public Function ValidateFileName(ByVal argFileName As String) As String

    Const cUnwanted As String = "<>""/:\|?*"

    Dim sResult As String, i As Long
    sResult = argFileName
    For i = 1 To Len(cUnwanted)
        sResult = Replace(sResult, Mid(cUnwanted, i, 1), "_")
    Next
    ValidateFileName = sResult
End Function
 
Solution

Rostuse

New Member
Joined
Jan 15, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
Aha, many apologies!
I should have known that using the Now statement, depending on locale, could result in prohibited characters in the file name.
Replace this line
VBA Code:
sFile = Environ("tmp") & "\ZPLcode_" & Replace(Now, ":", "") & ".txt"
by this line
VBA Code:
sFile = Environ("tmp") & "\" & ValidateFileName("ZPLcode_" & Now & ".txt")

and paste the following in your code module. That way your issue should be busted. If not, let me know.

VBA Code:
Public Function ValidateFileName(ByVal argFileName As String) As String

    Const cUnwanted As String = "<>""/:\|?*"

    Dim sResult As String, i As Long
    sResult = argFileName
    For i = 1 To Len(cUnwanted)
        sResult = Replace(sResult, Mid(cUnwanted, i, 1), "_")
    Next
    ValidateFileName = sResult
End Function
You are genius!!! Thank you so much @GWteB I don't know how to thank you, you really made it work !!! <3 :*
Thank you again !
 

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,941
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