Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: OleProperty UserId Password Excel


This question is answered.


Permlink Replies: 2 - Last Post: Oct 27, 2016 8:10 AM Last Post By: Andrew McIsaac
Andrew McIsaac

Posts: 93
Registered: 3/12/13
OleProperty UserId Password Excel  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 25, 2016 10:37 PM
Good evening,

I have a program that reads values of an Excel File, that works fine.
Added to this is the Excel file is now a .iqy ( web updated file ) - unfortunately this is the only type of file I can get live data, at this point.
I can read a cell location of the file using CreateOleObject.

The Issue is, this file is protected, and I would like the program to be able to open the file through total program control.
The file would not even need to be visible, as I can display the Excel info in the program.

I do not know how to set user Id and password with the OleProperties...
Code I have so far :

#include "ComObj.hpp" // Excel Variant ....
...

Variant var_Excel, var_Sheet;

System::WideString filename = L"P:
full directory
file_name.iqy";
var_Excel=CreateOleObject("Excel.Application");
//var_Excel.OlePropertyGet("Application").OlePropertyGet("Workbooks").OleProcedure("Open" , filename, "user_idc", "password"); // tried with userid, pass still fails : Bad variable type
var_Excel.OlePropertyGet("Application").OlePropertyGet("Workbooks").OleProcedure("Open" , filename);
var_Sheet = var_Excel.OlePropertyGet("ActiveSheet");

AnsiString temp;

temp=var_Sheet.OlePropertyGet("Cells").OlePropertyGet("Item", 1 ,1);
ShowMessage(temp); // This works to show the content of one cell...

var_Excel.OlePropertySet("DisplayAlerts",false); // Don't Want to be asked to Save each Time
var_Excel.OlePropertyGet("Application").OleProcedure("Quit");

When I run this, I get the LOGIN prompt for the Excel File - which I would like to bypass.

Any ideas?
Thanks in advance,
Andrew

Remy Lebeau (Te...


Posts: 9,447
Registered: 12/23/01
Re: OleProperty UserId Password Excel
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 26, 2016 9:57 AM   in response to: Andrew McIsaac in response to: Andrew McIsaac
Andrew wrote:

var_Excel.OlePropertyGet("Application").OlePropertyGet("Workbooks").
OleProcedure("Open" , filename, "user_idc", "password"); // tried with
userid, pass still fails : Bad variable type

The ordering of parameter values is important. The 2nd and 3rd parameters
of Open() are UpdateLinks and ReadOnly, respectively. The 5th parameter
is the password. There is no username parameter. Read the documentation:

Workbooks.Open Method (Excel)
https://msdn.microsoft.com/en-us/library/office/ff194819.aspx

When I run this, I get the LOGIN prompt for the Excel File

As you should be, per the above documentation:

A string that contains the password required to open a protected workbook.
If this argument is omitted and the workbook requires a password, the user
is prompted for the password.

--
Remy Lebeau (TeamB)
Andrew McIsaac

Posts: 93
Registered: 3/12/13
Re: OleProperty UserId Password Excel  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 27, 2016 8:09 AM   in response to: Andrew McIsaac in response to: Andrew McIsaac
Excellent.

I had difficulties finding documentation previously, thank you for the link.
Will now continue to fix program.

Thank you for all your help.
Andrew

Edited by: Andrew McIsaac on Oct 27, 2016 8:09 AM
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02