I found this code that when you CTRL+G from Visual Basic it displays all my
printers:
Sub PrinterSelect_macro()
Dim wshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim iCount As Integer
Dim sCurrentprinter As String
sCurrentprinter = Application.ActivePrinter
Set wshNetwork = CreateObject("WScript.Network")
Set oDrives = wshNetwork.EnumNetworkDrives
Set oPrinters = wshNetwork.EnumPrinterConnections
For iCount = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
Next
Range("AI35") = sCurrentprinter 'I added this line and it pastes my HP
DeskJet in cell AI35
End Sub

I added the line where it pastes the printer name in AI35 (same sheet as
code). What I want it to do is, paste the next printer in AI36, then the
following in AI37 and so on. When I CTRL+G and run macro it shows 5
printers, I would like to allow for around 20 (AI35:AI54). I tried several
ways to loop it and move down to the next cell but I cannot get it to work.
So if I had 10 printers (network included) it would have all 10 listed from
cells AI35:AI44. Thank you for your help!

Mike Olson

hi,
you have to have the paste part inside the loop for it to work the way you
want it. i'm guessing that it just pasted the last printer.
add this to the code.
at the top with the other dims....dim PP as range
with the other sets.....set pp = range("A135")
then inside the for next loop put this after the line
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
pp.value = sCurrentprinter
set pp = pp.offset(1,0) 'move down 1

untested so use at your own risk.
As to why your are getting 5 printers instead of the expected 20, i dont'
know. could be the way your network is set up. see IT.

Regards
FSt1

"mikeolson" wrote:

I found this code that when you CTRL+G from Visual Basic it displays all my
printers:
Sub PrinterSelect_macro()
Dim wshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim iCount As Integer
Dim sCurrentprinter As String
sCurrentprinter = Application.ActivePrinter
Set wshNetwork = CreateObject("WScript.Network")
Set oDrives = wshNetwork.EnumNetworkDrives
Set oPrinters = wshNetwork.EnumPrinterConnections
For iCount = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
Next
Range("AI35") = sCurrentprinter 'I added this line and it pastes my HP
DeskJet in cell AI35
End Sub

I added the line where it pastes the printer name in AI35 (same sheet as
code). What I want it to do is, paste the next printer in AI36, then the
following in AI37 and so on. When I CTRL+G and run macro it shows 5
printers, I would like to allow for around 20 (AI35:AI54). I tried several
ways to loop it and move down to the next cell but I cannot get it to work.
So if I had 10 printers (network included) it would have all 10 listed from
cells AI35:AI44. Thank you for your help!

Mike Olson

Getting closer. That populated my HP DeskJet into 5 cells (I have 5 printers
that show up), it just repeated the same on over. I wasn't expecting 20
printers, just want to allow for that incase it picks up a bunch of network
printers. So, now to get it to list each separate printer...any ideas?

Mike Olson

"FSt1" wrote:

hi,
you have to have the paste part inside the loop for it to work the way you
want it. i'm guessing that it just pasted the last printer.
add this to the code.
at the top with the other dims....dim PP as range
with the other sets.....set pp = range("A135")
then inside the for next loop put this after the line
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
pp.value = sCurrentprinter
set pp = pp.offset(1,0) 'move down 1

untested so use at your own risk.
As to why your are getting 5 printers instead of the expected 20, i dont'
know. could be the way your network is set up. see IT.

Regards
FSt1

"mikeolson" wrote:

I found this code that when you CTRL+G from Visual Basic it displays all my
printers:
Sub PrinterSelect_macro()
Dim wshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim iCount As Integer
Dim sCurrentprinter As String
sCurrentprinter = Application.ActivePrinter
Set wshNetwork = CreateObject("WScript.Network")
Set oDrives = wshNetwork.EnumNetworkDrives
Set oPrinters = wshNetwork.EnumPrinterConnections
For iCount = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
Next
Range("AI35") = sCurrentprinter 'I added this line and it pastes my HP
DeskJet in cell AI35
End Sub

I added the line where it pastes the printer name in AI35 (same sheet as
code). What I want it to do is, paste the next printer in AI36, then the
following in AI37 and so on. When I CTRL+G and run macro it shows 5
printers, I would like to allow for around 20 (AI35:AI54). I tried several
ways to loop it and move down to the next cell but I cannot get it to work.
So if I had 10 printers (network included) it would have all 10 listed from
cells AI35:AI44. Thank you for your help!

Mike Olson

With these changes, I got these results, but the results wouldn't be useable:
'Sub PrinterSelect_macro()
Dim wshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim iCount As Integer
Dim pp As Range
Dim sCurrentprinter As String
sCurrentprinter = Application.ActivePrinter
Set wshNetwork = CreateObject("WScript.Network")
Set oDrives = wshNetwork.EnumNetworkDrives
Set oPrinters = wshNetwork.EnumPrinterConnections
Set pp = Range("AI35")
For iCount = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
sCurrentprinter = "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
pp.Value = sCurrentprinter
Set pp = pp.Offset(1, 0) 'move down 1
Next
End Sub
'
that code gives this: in the proper cells:
AI35 Printer Port Microsoft Document Imaging Writer Port: = Microsoft
Office Document Image Writer
AI36 Printer Port LPT1: = HP DeskJet 697C
AI37 Printer Port USB001 = \\NOTEBOOK\HP Deskjet 3900 Series
and so on.
The problem is, the name that I need to appear in AI36 is: HP DeskJet 697C
on LPT1:
and in AI37 it should be: \\NOTEBOOK\HP Deskjet 3900 Series on Ne03:

Then the cells would have a useable printer name that I can put in my code
(already functions manually, I am trying to populate all the available
printers in those cells)

Thanks again.


"FSt1" wrote:

hi,
you have to have the paste part inside the loop for it to work the way you
want it. i'm guessing that it just pasted the last printer.
add this to the code.
at the top with the other dims....dim PP as range
with the other sets.....set pp = range("A135")
then inside the for next loop put this after the line
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
pp.value = sCurrentprinter
set pp = pp.offset(1,0) 'move down 1

untested so use at your own risk.
As to why your are getting 5 printers instead of the expected 20, i dont'
know. could be the way your network is set up. see IT.

Regards
FSt1

"mikeolson" wrote:

I found this code that when you CTRL+G from Visual Basic it displays all my
printers:
Sub PrinterSelect_macro()
Dim wshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim iCount As Integer
Dim sCurrentprinter As String
sCurrentprinter = Application.ActivePrinter
Set wshNetwork = CreateObject("WScript.Network")
Set oDrives = wshNetwork.EnumNetworkDrives
Set oPrinters = wshNetwork.EnumPrinterConnections
For iCount = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
Next
Range("AI35") = sCurrentprinter 'I added this line and it pastes my HP
DeskJet in cell AI35
End Sub

I added the line where it pastes the printer name in AI35 (same sheet as
code). What I want it to do is, paste the next printer in AI36, then the
following in AI37 and so on. When I CTRL+G and run macro it shows 5
printers, I would like to allow for around 20 (AI35:AI54). I tried several
ways to loop it and move down to the next cell but I cannot get it to work.
So if I had 10 printers (network included) it would have all 10 listed from
cells AI35:AI44. Thank you for your help!

Mike Olson

hi,
try changing this line...
'sCurrentprinter = "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
to this line.....
sCurrentprinter = oPrinters.Item(iCount + 1) & " at " & _
oPrinters.Item(iCount)

regards
FSt1
"mikeolson" wrote:

With these changes, I got these results, but the results wouldn't be useable:
'Sub PrinterSelect_macro()
Dim wshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim iCount As Integer
Dim pp As Range
Dim sCurrentprinter As String
sCurrentprinter = Application.ActivePrinter
Set wshNetwork = CreateObject("WScript.Network")
Set oDrives = wshNetwork.EnumNetworkDrives
Set oPrinters = wshNetwork.EnumPrinterConnections
Set pp = Range("AI35")
For iCount = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
sCurrentprinter = "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
pp.Value = sCurrentprinter
Set pp = pp.Offset(1, 0) 'move down 1
Next
End Sub
'
that code gives this: in the proper cells:
AI35 Printer Port Microsoft Document Imaging Writer Port: = Microsoft
Office Document Image Writer
AI36 Printer Port LPT1: = HP DeskJet 697C
AI37 Printer Port USB001 = \\NOTEBOOK\HP Deskjet 3900 Series
and so on.
The problem is, the name that I need to appear in AI36 is: HP DeskJet 697C
on LPT1:
and in AI37 it should be: \\NOTEBOOK\HP Deskjet 3900 Series on Ne03:

Then the cells would have a useable printer name that I can put in my code
(already functions manually, I am trying to populate all the available
printers in those cells)

Thanks again.


"FSt1" wrote:

hi,
you have to have the paste part inside the loop for it to work the way you
want it. i'm guessing that it just pasted the last printer.
add this to the code.
at the top with the other dims....dim PP as range
with the other sets.....set pp = range("A135")
then inside the for next loop put this after the line
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
pp.value = sCurrentprinter
set pp = pp.offset(1,0) 'move down 1

untested so use at your own risk.
As to why your are getting 5 printers instead of the expected 20, i dont'
know. could be the way your network is set up. see IT.

Regards
FSt1

"mikeolson" wrote:

I found this code that when you CTRL+G from Visual Basic it displays all my
printers:
Sub PrinterSelect_macro()
Dim wshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim iCount As Integer
Dim sCurrentprinter As String
sCurrentprinter = Application.ActivePrinter
Set wshNetwork = CreateObject("WScript.Network")
Set oDrives = wshNetwork.EnumNetworkDrives
Set oPrinters = wshNetwork.EnumPrinterConnections
For iCount = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
Next
Range("AI35") = sCurrentprinter 'I added this line and it pastes my HP
DeskJet in cell AI35
End Sub

I added the line where it pastes the printer name in AI35 (same sheet as
code). What I want it to do is, paste the next printer in AI36, then the
following in AI37 and so on. When I CTRL+G and run macro it shows 5
printers, I would like to allow for around 20 (AI35:AI54). I tried several
ways to loop it and move down to the next cell but I cannot get it to work.
So if I had 10 printers (network included) it would have all 10 listed from
cells AI35:AI44. Thank you for your help!

Mike Olson

I tried that too, it works fine on the LPT1:, but for the USB001, it doesn't
show if it's Ne01 or Ne02 or Ne03 & so on. Thanks for your efforts!

Mike Olson

"FSt1" wrote:

hi,
try changing this line...
'sCurrentprinter = "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
to this line.....
sCurrentprinter = oPrinters.Item(iCount + 1) & " at " & _
oPrinters.Item(iCount)

regards
FSt1
"mikeolson" wrote:

With these changes, I got these results, but the results wouldn't be useable:
'Sub PrinterSelect_macro()
Dim wshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim iCount As Integer
Dim pp As Range
Dim sCurrentprinter As String
sCurrentprinter = Application.ActivePrinter
Set wshNetwork = CreateObject("WScript.Network")
Set oDrives = wshNetwork.EnumNetworkDrives
Set oPrinters = wshNetwork.EnumPrinterConnections
Set pp = Range("AI35")
For iCount = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
sCurrentprinter = "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
pp.Value = sCurrentprinter
Set pp = pp.Offset(1, 0) 'move down 1
Next
End Sub
'
that code gives this: in the proper cells:
AI35 Printer Port Microsoft Document Imaging Writer Port: = Microsoft
Office Document Image Writer
AI36 Printer Port LPT1: = HP DeskJet 697C
AI37 Printer Port USB001 = \\NOTEBOOK\HP Deskjet 3900 Series
and so on.
The problem is, the name that I need to appear in AI36 is: HP DeskJet 697C
on LPT1:
and in AI37 it should be: \\NOTEBOOK\HP Deskjet 3900 Series on Ne03:

Then the cells would have a useable printer name that I can put in my code
(already functions manually, I am trying to populate all the available
printers in those cells)

Thanks again.


"FSt1" wrote:

hi,
you have to have the paste part inside the loop for it to work the way you
want it. i'm guessing that it just pasted the last printer.
add this to the code.
at the top with the other dims....dim PP as range
with the other sets.....set pp = range("A135")
then inside the for next loop put this after the line
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
pp.value = sCurrentprinter
set pp = pp.offset(1,0) 'move down 1

untested so use at your own risk.
As to why your are getting 5 printers instead of the expected 20, i dont'
know. could be the way your network is set up. see IT.

Regards
FSt1

"mikeolson" wrote:

I found this code that when you CTRL+G from Visual Basic it displays all my
printers:
Sub PrinterSelect_macro()
Dim wshNetwork As Object
Dim oDrives As Object
Dim oPrinters As Object
Dim iCount As Integer
Dim sCurrentprinter As String
sCurrentprinter = Application.ActivePrinter
Set wshNetwork = CreateObject("WScript.Network")
Set oDrives = wshNetwork.EnumNetworkDrives
Set oPrinters = wshNetwork.EnumPrinterConnections
For iCount = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Printer Port " & oPrinters.Item(iCount) _
& " = " & oPrinters.Item(iCount + 1)
Next
Range("AI35") = sCurrentprinter 'I added this line and it pastes my HP
DeskJet in cell AI35
End Sub

I added the line where it pastes the printer name in AI35 (same sheet as
code). What I want it to do is, paste the next printer in AI36, then the
following in AI37 and so on. When I CTRL+G and run macro it shows 5
printers, I would like to allow for around 20 (AI35:AI54). I tried several
ways to loop it and move down to the next cell but I cannot get it to work.
So if I had 10 printers (network included) it would have all 10 listed from
cells AI35:AI44. Thank you for your help!

Mike Olson