Hi,

I was trying to use ActiveX to read from an Excel Workbook that has 32 
Sheets but the process appears to "randomly" stall on some workstations.

I would like to try using ODBCDBE but some of the data I need to read is 
on the first row of the page so there are no column headings. 
Unfortunately I don't have any control as to the format of these 
spreadsheets.

I have successfully opened and read data from multiple sheets with a 
test workbook but can not access data on the first row.

Is there any way that I can somehow read this first row of data?

Thanks... Jonathan

On 1/16/2018 10:23 AM, Jonathan Leeming wrote:
> Hi,
> 
> I was trying to use ActiveX to read from an Excel Workbook that has 32 
> Sheets but the process appears to "randomly" stall on some workstations.
> 
> I would like to try using ODBCDBE but some of the data I need to read is 
> on the first row of the page so there are no column headings. 
> Unfortunately I don't have any control as to the format of these 
> spreadsheets.
> 
> I have successfully opened and read data from multiple sheets with a 
> test workbook but can not access data on the first row.
> 
> Is there any way that I can somehow read this first row of data?
> 
> Thanks... Jonathan
> 
Another problem that I am having is that the Excel file is password 
protected... If I unprotect it I can read it but if protected I can not. 
  It is created with Excel 2013 and the connection string I'm using is:

  cExcelFile := ".\2018 Time Sheet - Shannon Rose.xlsx"

    cConnect := 'DBE=ODBCDBE;DSN=Excel 
Files;ReadOnly=1;UID=;PWD=Strength2831;DBQ='
    cConnect += cExcelFile

If anyone has any ideas on a solution, it would be appreciated!

Thanks... Jonathan

On 1/16/2018 11:56 AM, Jonathan Leeming wrote:
> On 1/16/2018 10:23 AM, Jonathan Leeming wrote:
>> Hi,
>>
>> I was trying to use ActiveX to read from an Excel Workbook that has 32 
>> Sheets but the process appears to "randomly" stall on some workstations.
>>
>> I would like to try using ODBCDBE but some of the data I need to read 
>> is on the first row of the page so there are no column headings. 
>> Unfortunately I don't have any control as to the format of these 
>> spreadsheets.
>>
>> I have successfully opened and read data from multiple sheets with a 
>> test workbook but can not access data on the first row.
>>
>> Is there any way that I can somehow read this first row of data?
>>
>> Thanks... Jonathan
>>
> Another problem that I am having is that the Excel file is password 
> protected... If I unprotect it I can read it but if protected I can not. 
>   It is created with Excel 2013 and the connection string I'm using is:
> 
>   cExcelFile := ".\2018 Time Sheet - Shannon Rose.xlsx"
> 
>     cConnect := 'DBE=ODBCDBE;DSN=Excel 
> Files;ReadOnly=1;UID=;PWD=Strength2831;DBQ='
>     cConnect += cExcelFile
> 
> If anyone has any ideas on a solution, it would be appreciated!
> 
> Thanks... Jonathan
> 
Hi Yet Again!

It appears that hoping the PWD would work for the spreadsheet password 
was a bit of fantasy!  However it appears that if I first execute the 
following before the ODBCDBE connection it will allow an ODBCDBE 
connection to a password protected spreadsheet:

    cExcelFile := "C:\Temp\2018 Time Sheet - Shannon RoseP.xlsx"

    oXls := CreateObject("Excel.Application")	 Active X Object

    oBook := oXls:Workbooks:Open(cExcelFile,,,,"Strength2831")

The OBBCDBE connection string is:

    cConnect := 'DBE=ODBCDBE;DSN=Excel Files;ReadOnly=1;UID=;pwd=;DBQ='
    cConnect += cExcelFile

    oSession := DacSession():new(cConnect)

Kind of "Goofy" but the only work around I have determined thus far.

Unfortunately it is taking around 7 seconds to create & open via ActiveX 
and then establish the ODBC connection. Once that is done it take about 
.5 second to open each Sheet and around .1 seconds to read the data in 
cells I1:N8 (a 6 x 8 area) for a total of around 22 seconds.  Disabling 
AV does not impact the timings.

Regards... Jonathan

Afaik and as I read docs quite a long time ago, Microsoft does not support ADO
drivers ODBC to Excel password protected files.

Also #notmydpt : why in the hell exchanging 32 tables of data via Excel
workbook and not via sql that is both easier and better protected ?



Jonathan Leeming wrote in message
news:3539b072$37827704$6490e@news.alaska-software.com...
>On 1/16/2018 11:56 AM, Jonathan Leeming wrote:
>> On 1/16/2018 10:23 AM, Jonathan Leeming wrote:
>>> Hi,
>>>
>>> I was trying to use ActiveX to read from an Excel Workbook that has 32 
>>> Sheets but the process appears to "randomly" stall on some workstations.
>>>
>>> I would like to try using ODBCDBE but some of the data I need to read 
>>> is on the first row of the page so there are no column headings. 
>>> Unfortunately I don't have any control as to the format of these 
>>> spreadsheets.
>>>
>>> I have successfully opened and read data from multiple sheets with a 
>>> test workbook but can not access data on the first row.
>>>
>>> Is there any way that I can somehow read this first row of data?
>>>
>>> Thanks... Jonathan
>>>
>> Another problem that I am having is that the Excel file is password 
>> protected... If I unprotect it I can read it but if protected I can not. 
>>   It is created with Excel 2013 and the connection string I'm using is:
>> 
>>   cExcelFile := ".\2018 Time Sheet - Shannon Rose.xlsx"
>> 
>>     cConnect := 'DBE=ODBCDBE;DSN=Excel 
>> Files;ReadOnly=1;UID=;PWD=Strength2831;DBQ='
>>     cConnect += cExcelFile
>> 
>> If anyone has any ideas on a solution, it would be appreciated!
>> 
>> Thanks... Jonathan
>> 
>Hi Yet Again!
>
>It appears that hoping the PWD would work for the spreadsheet password 
>was a bit of fantasy!  However it appears that if I first execute the 
>following before the ODBCDBE connection it will allow an ODBCDBE 
>connection to a password protected spreadsheet:
>
>    cExcelFile := "C:\Temp\2018 Time Sheet - Shannon RoseP.xlsx"
>
>    oXls := CreateObject("Excel.Application")	 Active X Object
>
>    oBook := oXls:Workbooks:Open(cExcelFile,,,,"Strength2831")
>
>The OBBCDBE connection string is:
>
>    cConnect := 'DBE=ODBCDBE;DSN=Excel Files;ReadOnly=1;UID=;pwd=;DBQ='
>    cConnect += cExcelFile
>
>    oSession := DacSession():new(cConnect)
>
>Kind of "Goofy" but the only work around I have determined thus far.
>
>Unfortunately it is taking around 7 seconds to create & open via ActiveX 
>and then establish the ODBC connection. Once that is done it take about 
>.5 second to open each Sheet and around .1 seconds to read the data in 
>cells I1:N8 (a 6 x 8 area) for a total of around 22 seconds.  Disabling 
>AV does not impact the timings.
>
>Regards... Jonathan

Found in old code:

- got protected xls
- opened with "Excel.Application" and provided password
- stil with Excel.Application saved it into temporary table without password
- closed file
- and opened via ODBC and rolled thru data 
- closed ODBC
- deleted temporary file

is kindof dirty but it worked



Matej Jurac wrote in message
news:78de4183$1e443e4a$13cc3@news.alaska-software.com...
>Afaik and as I read docs quite a long time ago, Microsoft does not support ADO
>drivers ODBC to Excel password protected files.
>
>Also #notmydpt : why in the hell exchanging 32 tables of data via Excel
>workbook and not via sql that is both easier and better protected ?
>
>
>
>Jonathan Leeming wrote in message
>news:3539b072$37827704$6490e@news.alaska-software.com...
>>On 1/16/2018 11:56 AM, Jonathan Leeming wrote:
>>> On 1/16/2018 10:23 AM, Jonathan Leeming wrote:
>>>> Hi,
>>>>
>>>> I was trying to use ActiveX to read from an Excel Workbook that has 32 
>>>> Sheets but the process appears to "randomly" stall on some workstations.
>>>>
>>>> I would like to try using ODBCDBE but some of the data I need to read 
>>>> is on the first row of the page so there are no column headings. 
>>>> Unfortunately I don't have any control as to the format of these 
>>>> spreadsheets.
>>>>
>>>> I have successfully opened and read data from multiple sheets with a 
>>>> test workbook but can not access data on the first row.
>>>>
>>>> Is there any way that I can somehow read this first row of data?
>>>>
>>>> Thanks... Jonathan
>>>>
>>> Another problem that I am having is that the Excel file is password 
>>> protected... If I unprotect it I can read it but if protected I can not. 
>>>   It is created with Excel 2013 and the connection string I'm using is:
>>> 
>>>   cExcelFile := ".\2018 Time Sheet - Shannon Rose.xlsx"
>>> 
>>>     cConnect := 'DBE=ODBCDBE;DSN=Excel 
>>> Files;ReadOnly=1;UID=;PWD=Strength2831;DBQ='
>>>     cConnect += cExcelFile
>>> 
>>> If anyone has any ideas on a solution, it would be appreciated!
>>> 
>>> Thanks... Jonathan
>>> 
>>Hi Yet Again!
>>
>>It appears that hoping the PWD would work for the spreadsheet password 
>>was a bit of fantasy!  However it appears that if I first execute the 
>>following before the ODBCDBE connection it will allow an ODBCDBE 
>>connection to a password protected spreadsheet:
>>
>>    cExcelFile := "C:\Temp\2018 Time Sheet - Shannon RoseP.xlsx"
>>
>>    oXls := CreateObject("Excel.Application")	 Active X Object
>>
>>    oBook := oXls:Workbooks:Open(cExcelFile,,,,"Strength2831")
>>
>>The OBBCDBE connection string is:
>>
>>    cConnect := 'DBE=ODBCDBE;DSN=Excel Files;ReadOnly=1;UID=;pwd=;DBQ='
>>    cConnect += cExcelFile
>>
>>    oSession := DacSession():new(cConnect)
>>
>>Kind of "Goofy" but the only work around I have determined thus far.
>>
>>Unfortunately it is taking around 7 seconds to create & open via ActiveX 
>>and then establish the ODBC connection. Once that is done it take about 
>>.5 second to open each Sheet and around .1 seconds to read the data in 
>>cells I1:N8 (a 6 x 8 area) for a total of around 22 seconds.  Disabling 
>>AV does not impact the timings.
>>
>>Regards... Jonathan

On 1/31/2018 12:55 AM, Matej Jurac wrote:
> Found in old code:
> 
> - got protected xls
> - opened with "Excel.Application" and provided password
> - stil with Excel.Application saved it into temporary table without password
> - closed file
> - and opened via ODBC and rolled thru data
> - closed ODBC
> - deleted temporary file
> 
> is kindof dirty but it worked
> 
> 
> 
> Matej Jurac wrote in message
> news:78de4183$1e443e4a$13cc3@news.alaska-software.com...
>> Afaik and as I read docs quite a long time ago, Microsoft does not support ADO
>> drivers ODBC to Excel password protected files.
>>
>> Also #notmydpt : why in the hell exchanging 32 tables of data via Excel
>> workbook and not via sql that is both easier and better protected ?
>>
>>
>>
>> Jonathan Leeming wrote in message
>> news:3539b072$37827704$6490e@news.alaska-software.com...
>>> On 1/16/2018 11:56 AM, Jonathan Leeming wrote:
>>>> On 1/16/2018 10:23 AM, Jonathan Leeming wrote:
>>>>> Hi,
>>>>>
>>>>> I was trying to use ActiveX to read from an Excel Workbook that has 32
>>>>> Sheets but the process appears to "randomly" stall on some workstations.
>>>>>
>>>>> I would like to try using ODBCDBE but some of the data I need to read
>>>>> is on the first row of the page so there are no column headings.
>>>>> Unfortunately I don't have any control as to the format of these
>>>>> spreadsheets.
>>>>>
>>>>> I have successfully opened and read data from multiple sheets with a
>>>>> test workbook but can not access data on the first row.
>>>>>
>>>>> Is there any way that I can somehow read this first row of data?
>>>>>
>>>>> Thanks... Jonathan
>>>>>
>>>> Another problem that I am having is that the Excel file is password
>>>> protected... If I unprotect it I can read it but if protected I can not.
>>>>    It is created with Excel 2013 and the connection string I'm using is:
>>>>
>>>>    cExcelFile := ".\2018 Time Sheet - Shannon Rose.xlsx"
>>>>
>>>>      cConnect := 'DBE=ODBCDBE;DSN=Excel
>>>> Files;ReadOnly=1;UID=;PWD=Strength2831;DBQ='
>>>>      cConnect += cExcelFile
>>>>
>>>> If anyone has any ideas on a solution, it would be appreciated!
>>>>
>>>> Thanks... Jonathan
>>>>
>>> Hi Yet Again!
>>>
>>> It appears that hoping the PWD would work for the spreadsheet password
>>> was a bit of fantasy!  However it appears that if I first execute the
>>> following before the ODBCDBE connection it will allow an ODBCDBE
>>> connection to a password protected spreadsheet:
>>>
>>>     cExcelFile := "C:\Temp\2018 Time Sheet - Shannon RoseP.xlsx"
>>>
>>>     oXls := CreateObject("Excel.Application")	 Active X Object
>>>
>>>     oBook := oXls:Workbooks:Open(cExcelFile,,,,"Strength2831")
>>>
>>> The OBBCDBE connection string is:
>>>
>>>     cConnect := 'DBE=ODBCDBE;DSN=Excel Files;ReadOnly=1;UID=;pwd=;DBQ='
>>>     cConnect += cExcelFile
>>>
>>>     oSession := DacSession():new(cConnect)
>>>
>>> Kind of "Goofy" but the only work around I have determined thus far.
>>>
>>> Unfortunately it is taking around 7 seconds to create & open via ActiveX
>>> and then establish the ODBC connection. Once that is done it take about
>>> .5 second to open each Sheet and around .1 seconds to read the data in
>>> cells I1:N8 (a 6 x 8 area) for a total of around 22 seconds.  Disabling
>>> AV does not impact the timings.
>>>
>>> Regards... Jonathan

Hi Matej,

Your suggestion looks like something I tried... Worked... but I found 
that reading the spreadsheet data via ODBC was much slower than ActiveX 
and since I found that I needed to use the ActiveX approach for the 
password issue I just stick to ActiveX.  The ODBC attempt was made for 
comparison.

As for not using SQL the 32 tab pages of the spreadsheet are daily time 
sheets completed in the "field".  The data is not in simple table form 
but rather formatted for ease of use by the staff completing them.

Thanks, Jonathan

jonathan.leeming@the-family-centre.com
Edmonton, Alberta, Canada