Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBscript Excel print problem

Status
Not open for further replies.

columbo1977

Programmer
May 9, 2006
49
GB
Hey All

I am having a problem, the code I have here was working last night only it wasnt opening the Excel documents as they are passworded. Now it isnt saying "Cannot use paranthisis when using a sub"??

can anyone help me get this to loop through the folder and open passworded excel documents and print them.
Code:
strDirectory = "File Path"

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(strDirectory)
Set colItems = objFolder.Items
For Each objItem in colItems
objItem.InvokeVerbEx("Print")
Next

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objMainFolder = objFSO.GetFolder(strDirectory)
Set colSubfolders = objMainFolder.Subfolders
For Each objSubfolder in colSubfolders
strFolder = objSubfolder.Path
GetFiles strFolder 
Next

sub GetFiles(byval strDirectory)
Set objWeeFolder = objShell.Namespace(strDirectory)
Set colWeeItems = objWeeFolder.Items
For Each objWeeItem in colWeeItems
objWeeItem.InvokeVerbEx("Print")
Next
end sub

the code I was using for individual files was

Code:
Dim AppExcel 
Set AppExcel = CreateObject("Excel.application")
AppExcel.Workbooks.Open"File path and name.xls",,,,"password"
AppExcel.Visible = True
appExcel.ActiveWindow.SelectedSheets.PrintOut
Appexcel.Quit
Set appExcel = Nothing

Thanks for looking


 


Hi,

Now it isnt saying "Cannot use paranthisis when using a sub"??
Then what IS it "saying"???

What statement is it saying it on?

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Sorry for the late reply.

This is what i have now and it is having trouble witht he filename, It doesnt work unless I put a specific name in but I need it to loop through all files so I can put a name in .

Code:
Dim strPath 'As String
Dim appExcel 'As Object
Dim strFolderPath 'As String

strFolderPath = "G:\Banda\FA BA Shared Drive\Harrogate\To Print\"
strPath = "*.xls" 'Set the path.

'strPath = Dir(strPath, vbNormal)

Set appExcel = CreateObject("Excel.Application")

appExcel.Visible = False
appExcel.DisplayAlerts = False

Do While strPath <> ""
'appExcel.Workbooks.Open strFolderPath & strPath

AppExcel.Workbooks.Open strFolderPath & strPath,0,,,"resources"
AppExcel.Visible = True
appExcel.ActiveWindow.SelectedSheets.PrintOut

'strPath = Dir

Loop

appExcel.Quit
Set appExcel = Nothing

Any ideas?
 
I need it to loop through all files
I'd use a Files collection with the FileSystemObject

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
I'm sorry but I'm no expeert in this, can you please show me on the code how to achieve this?

Thanks

Graham
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top