[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

VBS not opening file and copying range

Hello Experts,
I have some vbs which is supposed to open a password protected file, copy a range from another file and then close and save the target file.  I cannot see why it is not working.  Can anyone spot it?  The code works up until the point where it is supposed to open and modify the file.  Thank you in advance.
strProtectedFile = "C:\srvr\rie-bin\CLIPr\Nav.xls"
strProtectedFilePassword = "1234"

		Set oProtectedFile = xlApp.Workbooks.Open(strProtectedFile,  strProtectedFilePassword, False)
		oProtectedFile.Worksheets("Sheet1").Visible = True
		oBookToProcess.WorkSheets(1).Range("A2:A11").Copy oProtectedFile.WorkSheets("Sheet1").Range("N8:N17")
		oProtectedFile.Worksheets("Sheet1").Visible = False
		xlApp.DisplayAlerts = False
		oProtectedFile.Close True
		xlApp.DisplayAlerts = True

Open in new window

0
davidam
Asked:
davidam
  • 2
  • 2
1 Solution
 
SiddharthRoutCommented:
That is because the syntax is incorrect.

Try this

Dim xlApp, xlSheet, oProtectedFile
Dim strProtectedFilePassword

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

strProtectedFile = "C:\srvr\rie-bin\CLIPr\Nav.xls"
strProtectedFilePassword = "1234"

Set oProtectedFile = xlApp.Workbooks.Open(strProtectedFile,0,false,5, strProtectedFilePassword,"")
Set xlSheet = oProtectedFile.Worksheets("Sheet1")
xlSheet.Visible = True

Open in new window


Sid
0
 
davidamAuthor Commented:
No success yet.  I have looked around as much as I can and I have not seen this syntax...it is always just the three items.  Can you tell me what ,0,false,(read only, right),5, and "" are indidcating? Thanks.
0
 
davidamAuthor Commented:
Sorry...it did work.  Thank you!!
I would still like to know what the syntax is about if you have a moment.
0
 
SiddharthRoutCommented:
Sure :)

The syntax is

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

If you open Excel and see it's help file you will get the explanation for each parameter in it :)

Sid
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now