VBS not opening file and copying range

Posted on 2011-04-19
Last Modified: 2012-05-11
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

Question by:davidam
    LVL 30

    Accepted Solution

    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


    Author Comment

    No success yet.  I have looked around as much as I can and I have not seen this is always just the three items.  Can you tell me what ,0,false,(read only, right),5, and "" are indidcating? Thanks.

    Author Comment

    by:davidam did work.  Thank you!!
    I would still like to know what the syntax is about if you have a moment.
    LVL 30

    Expert Comment

    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 :)


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now