Open a password protected excel workbook from second workbook to fetch data using dynamic connection

[kaustav choudhury]

Hi,

I have two excel applications. I wish to fetch data from excel application A
to excel application B using recordsets. My excel application A is password
protected. How do I fetch the data without actually opening application A
(using recordsets in excel application B).

Any help is most welcome.

Thanks,

Kaustav

 

[Guest]

I have done something like you with ADODB

In the Reference add : "Microsoft ActiveX Data Objects 2.5 Library
In your function of B add :
Dim cntXLSFile As ADODB.Connectio
Dim rstQuery As ADODB.Recordse
Dim strTmp As Strin

'sets the ODBC driver to read the Excel file (without opening it
Set cntXLSFile = New ADODB.Connectio
cntXLSFile.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=0;DBQ=" & [The Full File Name of A
'opens a connection to the Excel fil
cntXLSFile.Ope

'gets the marketing organisation, exercise year and report type from the FMRG templat
Set rstQuery = cntXLSFile.Execute("SELECT * FROM [The Named Range];"
If Not rstQuery is Nothing The
If Not rstQuery.EOF The
rstQuery.MoveFirs
strTmp = rstQuery.Fields("[The Field Name]").Valu
rstQuery.Clos
Set rstQuery = Nothin
End I
cntXLSFile.Clos
Set cntXLSFile = Nothin

To use it you must organize your data as follo
first row : list of the fields name (1 field by column [The Field Name]
and the data belo
A named range must be defined for the data including the field's name row ([The Named Range])

I hope this will help you
NicoB

 

[kaustav choudhury]

Hi NicoB,

I have done something very similar to what you have proposed. My problem
is that my first application from where I plan to fetch the data is
password protected. When I supply the password in the connection string
it displays an error like "Runtime Error-'2147217843(8004e4d)' Cannot
start your application. The workgroup information file is missing or
opened exclusively by another user." (The application is closed
actually) and when I dont it displays an error like "Runtime
Error-'2147467259(80004005)' could not decrypt file. ".

How do I fetch data from the application now. Hope I have made myself
clear on the issue.

Thanks for showing interest.

Kaustav