Take textbox input and print off document

[francozola25]

Hello i was wondering if someone could help me.

I have a userform with textbox called code.

What i want is for my user to input a code into the textbox and then open a document from the following:

Documents.Open(\\myserver\Documents\Codes\ (file://\\myserver\Documents\Codes\) & Code & ".Doc"

I want to have Code as a variable that will change everytime from the input into the textbox

The document that is opened is then sent to the printer

[CreganTur]

If you are opening documents that already exist, would it not be better to use a combo box- that way you can predefine all of the 'codes' that the user can select. If you don't do this, then you will need some way to evaluate if a 'code' represents the name of an exisitng doc so that it can reject 'codes' that are not valid.

Are you wanting the document to be printed without opening it, or are you wanting the document to be opened so changes can be made and then printed, or do you want it to open and print?

[francozola25]

hey thanks for your reply

a drop down would be great containing the filenames in the folder but would this be easy to do.

I just want to print the document, not make any changes.

[macropod]

Hi francozola,

In that case, there seems to be no need to open said document - so you could use:
Documents.Item("\\myserver\Documents\Codes\ (file://myserver/Documents/Codes/)" & Code & ".Doc").PrintOut

[francozola25]

Thanks for that.

How could i set the drop down value to take the contents of a specific folder. What i want is when i click the drop down (code), it will contain the filenames of folder \\myserver\code (file://\\myserver\code) document. I only want it to display pdf files in the drop down.

Many Thanks

[macropod]

Hi francozola,

You like to make things complicated, don't you?!!

Here's some code to populate 'Dropdown1':
Sub Dropdown1Populate()
Dim fs As Object
Dim i As Integer
Dim j As Integer
Set fs = Application.FileSearch
j = 25
ActiveDocument.FormFields("Dropdown1").DropDown.ListEntries.Clear
With fs
.LookIn = "\\myserver\code\ (file://\\myserver\code\)"
.FileName = "*.pdf"
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
If .FoundFiles.Count < j Then j = .FoundFiles.Count
For i = 1 To j
ActiveDocument.FormFields("Dropdown1").DropDown.ListEntries.Add _
Name:=Split(.FoundFiles(i), "\")(UBound(Split(.FoundFiles(i), "\")))
Next i
End If
End With
End SubNote the 25-entry limit in the code. That's because 25 is the Dropdown max.

[francozola25]

Hey Macropod,

When on my userform i have a combobox Dropdown1, do i input this code by right clicking the combobox and clicking view code therefore entering the code in the Private Sub Dropdown1_Change().

Is there anyway i could get more than 25? ie. using something different like a listbox.

thanks

[macropod]

Hi francozola,

The code I posted is for a Dropdown formfield, not for a combobox on a userform. Your post did say 'drop down', not 'combobox' or 'listbox' ...

The 25-entry limit applies to Dropdown formfields - I don't know of any limit for comboboxes or listboxes.

Although the code for populating comboboxes & listboxes is differernt, the code I posted has done most of the work for you. To use the code with a combobox or listbox, I'd suggest populating a variable-size array, then reading the array contents with the code associated with the combobox or listbox.

[francozola25]

Apologies i should of been clear at the start.

how would populate you populate the variable size array?

[francozola25]

What i have is the following


Private Sub UserForm_Initialize()
'Files in folder listed in Listbox1
Dim FSO As Object, fld As Object, Fil As Object
Dim SubFolderName As String
Dim i As Integer
Set FSO = CreateObject("Scripting.FileSystemObject")
Me.ComboBox1.Clear 'clear previous entries
SubFolderName = "T:\"
Set fld = FSO.GetFolder(SubFolderName)
For Each Fil In fld.Files
i = i + 1
Me.ComboBox1.AddItem Fil.Name

Next Fil

End Sub



I just need to implement the bit that only .pdf files to be considered.

[macropod]

Hi francozola,

You like to make things complicated, don't you?!!

Here's some code to populate 'Dropdown1':
Sub Dropdown1Populate()
Dim fs As Object
Dim i As Integer
Dim j As Integer
Set fs = Application.FileSearch
j = 25
ActiveDocument.FormFields("Dropdown1").DropDown.ListEntries.Clear
With fs
.LookIn = "\\myserver\code\ (file://\\myserver\code\)"
.FileName = "*.pdf"
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
If .FoundFiles.Count < j Then j = .FoundFiles.Count
For i = 1 To j
ActiveDocument.FormFields("Dropdown1").DropDown.ListEntries.Add _
Name:=Split(.FoundFiles(i), "\")(UBound(Split(.FoundFiles(i), "\")))
Next i
End If
End With
End SubNote the 25-entry limit in the code. That's because 25 is the Dropdown max.
FWIW a better way to extract the filename would be:
ActiveDocument.FormFields("Dropdown1").DropDown.ListEntries.Add Name:=Dir(.FoundFiles(i))

[macropod]

Hi francozola,

You could populate a variable-size array with code like:
Dim FilArry()
For Each Fil In fld.Files
i = i + 1
ReDim Preserve FilArry(i)
FilArry(i) = Fil.Name
Next Fil

[francozola25]

thanks i am trying to print this out from another module but i keep getting error about text converters or that i done have rights to the G:\?

It doesn't make sense



Set cDoc = Documents.Open("G:\" & UserForm1.ComboBox1, ReadOnly:=True, Visible:=False)
cDoc.PrintOut

[macropod]

Hi francozola,

You haven't specified the ComboBox property that you want to use. Try:
Set cDoc = Documents.Open("G:\" & UserForm1.ComboBox1.Text, ReadOnly:=True, Visible:=False)
cDoc.PrintOutor:


Set cDoc = Documents.Open("G:\" & UserForm1.ComboBox1.Value, ReadOnly:=True, Visible:=False)

cDoc.PrintOut

[francozola25]

No it is still coming up with the same problem.

It doesn't like UserForm1.ComboBox1.Text or UserForm1.ComboBox1.Value

Will it would matter that the following code


Private Sub UserForm_Initialize()
'Files in folder listed in Listbox1
Dim FSO As Object, fld As Object, Fil As Object
Dim SubFolderName As String
Dim i As Integer
Set FSO = CreateObject("Scripting.FileSystemObject")
Me.ComboBox1.Clear 'clear previous entries
SubFolderName = "G:\"
Set fld = FSO.GetFolder(SubFolderName)
For Each Fil In fld.Files
i = i + 1
Me.ComboBox1.AddItem Fil.Name

Next Fil

End Sub



is the code on UserForm1

then i have a macro in a module that calls like so




Set cDoc = Documents.Open("G:\" & UserForm1.ComboBox1.Value, ReadOnly:=True, Visible:=False)

cDoc.PrintOut

[macropod]

Hi francozola,

I'm not sure what you're doing, but if you put the following code in a standard module:
Option Explicit
Public ComboTxt As String

Sub Load_Userform()
UserForm1.Show
End Sub

Sub Test()
MsgBox ComboTxt
End Suband the following code behind your userform:
Option Explicit
Private Sub ComboBox1_Change()
ComboTxt = ComboBox1.Text
End Sub

Private Sub UserForm_Initialize()
'Files in folder listed in Listbox1
Dim FSO As Object, fld As Object, Fil As Object
Dim SubFolderName As String
Dim i As Integer
Set FSO = CreateObject("Scripting.FileSystemObject")
Me.ComboBox1.Clear 'clear previous entries
SubFolderName = "G:\"
Set fld = FSO.GetFolder(SubFolderName)
For Each Fil In fld.Files
i = i + 1
Me.ComboBox1.AddItem Fil.Name
Next Fil
End Subthen running the 'Load_Userform' macro, choose a file, then run the 'Test' macro (even after closing the userform) should return the name of the selected file.

Taking this one step further, you should then be able to use:
Set cDoc = Documents.Open("G:\" & ComboTxt, ReadOnly:=True, Visible:=False)
cDoc.PrintOut