Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

[SMARTII]

Using Alpha 11
Can't find the way to choose the 3rd Sheet!!
The Spreadsheet has 3 sheets or "tabs" as follows:
Unit List / MSO's / Ready
I want to only import the columns & rows of the "Results" Sheet, aka the 3rd Sheet, but
Operation IMPORT EXCEL AlphaDAO Import Genie
The import type:
MS-Excel V3.0,V4.0,2003,2007,2010 (*.xls;*.xlsx)
is only offering the 1st Sheet ie "Units List".
How do I specify the 3rd Sheet?

[Al Buchholz]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

Tom

Please post a small example excel file showing the problem.

Also what build of version 11 are you on?

[SMARTII]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

Build 4096
OK.. ZIPPED EXCEL
THANK YOU

[Stan Mathews]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

I see all three sheets in the DAO genie. Are you specifying excel2007 when building the connection string?


Image 1.pngImage 2.png

[SMARTII]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

I never have used the DAO Genie.
I just did the action script "Import" Excel AlphaDAO etc.
Your way is completely new to me.
How did you get that "building connection string" thing?

[Stan Mathews]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

Easiest to illustrate.

step 1.pngstep 2.pngstep 3.pngstep 4.pngstep 5.png

Will attach two more to next post, hit some limit.

[Stan Mathews]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

step 6.pngstep 7.png

[SMARTII]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

So sorry, but I cannot find the choice for which Tab of the spreadsheet to Export!
What am I missing??

[Stan Mathews]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

When you have completed those steps you should see the individual tabs listed as shown in post 4, upper left corner of the dialog.

[SMARTII]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

When I press Test Connection:

[Stan Mathews]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

Do you have 2007 installed? I think the file you attached was that version. You can always use the link provided to download the driver.

[Stan Mathews]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

If I save the file as Excel 2003 and repeat the steps choosing 2003 compatibility, this is the resulting tab list.


as 2003.png

[Tom Henkel]

Re: Import EXCEL Spreadsheet - Having 3 Sheets. Need 3rd Sheet. Not 1st Sheet

With a lot of help from Stan, we created this function to import esxcel data into an array. It shouldn't be too much to change it to import into a table. When the function is called, the sheet within the workbook is one of the selections.

Maybe this will help...

Tom

'Date Created: 17-Jul-2013 02:57:48 PM
'Last Updated: 23-Jan-2015 01:54:48 PM
'Created By  : Thomas Henkel
'Updated By  : Thomas Henkel
' Variables passed:
' Filename = Name of excel workbook
' First row is numeric = first row to select  - usually 1 unless you have header rows
' First col is numeric, usually 1
' Data_Array is the pointer to the array from the calling script
' Sheet is numeric value of which sheet in the workbook to select
Function get_excel_data as V (Filename as C, First_Row as N, First_Col as N, Data_Array as P, Sheet as N)
    dim MyObject as P
    dim row as N
    dim col as N
    dim rows as N
    dim cols as N
    dim cell as C
    dim temp as C
    cols = Data_Array.size(2)
    rows = Data_Array.size(1)
    on error goto quitting
    MyObject = ole.getobject("","Excel.Application")
	MyObject.Workbooks.Open(Filename)
   MyObject.Worksheets(Sheet).Select()   
    for row = First_Row to  rows
        for col = First_Col to cols
            cell = get_col(col) + row
            Data_Array[row,col] = MyObject.activesheet.Cells.Range(cell)
        next col
    next row
quitting:
    MyObject.Workbooks.close()
    delete MyObject
end function