Excel function error

layzzer · Post by **layzzer** » Tue May 14, 2013 6:33 pm

Hi everyone, I am simply trying to get data from an Excel file and can't seem to get anything.

XLSheetToArray>C:\Macro Scheduler\Test\data\Test.xlsx,TestData,sheetArray
Wait>2
ArrayCount>sheetArray,numrows
MessageModal>%numrows%

This script does not go past the first line.

I've checked the file and path, they are correct. Every time I run this script I get a "File is not an Excel" error

Any idea what's going on?

Thanks

Jerry Thomas · Post by **Jerry Thomas** » Tue May 14, 2013 8:26 pm

Use the Code Builder to get the path.
Right click XLSheetToArray and select Code Builder. Then Browse to file.
(We all make typos.)

Did you put double quotes around the path? <- DON'T
(Old habits die slowly)

Right click the Excel page tab, select Rename and then copy the exact name.
(and some of us still make typos...)

If these don't fix it, hopefully someone else can offer assistance.

layzzer · Post by **layzzer** » Wed May 15, 2013 2:21 pm

I triple checked the path, file name and worksheet name. Same error, I have no idea what is going on.

Is there any extension or add-on needed?

Jerry Thomas · Post by **Jerry Thomas** » Wed May 15, 2013 2:29 pm

No extensions or add ons.
I haven't added anything and I am not seeing the problem you are having.

Can you connect to any spreadsheet?

layzzer · Post by **layzzer** » Wed May 15, 2013 3:08 pm

I just created a "New Microsoft Excel Worksheet.xlsx" and the sheet I'm trying to pull data from is Sheet1

And the same error occurred.

Can someone provide sample code to see what could be wrong with my particular system perhaps?

layzzer · Post by **layzzer** » Wed May 15, 2013 3:09 pm

Macro Scheduler can open up Excel files just fine, but connecting to them... no

JRL · Post by **JRL** » Wed May 15, 2013 7:27 pm

I see the same "File is not an Excel file" error as layzzer. Converted the file to a .xls and XLSheetToArray> now works. Tried on Win XP and Win 7 with the same results.

My xlsx file was created using Excel 2007. Perhaps a newer version of Excel creates a .xlsx file that is compatible with XLSheetToArray>?

One other point. Discovered that the sheet name parameter is case sensitive.

Just found a copy of Excel 2010 and saved the file to an xlsx format and get the same "File is not an Excel file" error.

Simply renaming the file from .xlsx to .xls does not fix the issue. It appears the file must be saved in the .xls format for XLSheetToArray> to work.

layzzer · Post by **layzzer** » Thu May 16, 2013 5:38 pm

Confirm that by saving the Excel file from .xlsx to .xls as solved the issue

JRL · Post by **JRL** » Thu May 16, 2013 5:53 pm

Help for [color=green]XLSheetToArray[/color]> wrote:Imports a sheet from an Excel file (.xls or .xlsx) into an array. Note that this function differs from the other XL functions in that it does NOT require Excel to be installed and instead reads the Excel file directly. Therefore the same degree of compatibility cannot be guaranteed.

So I don't know if help is incorrect and the function cannot read from a .xlsx file or if the function is meeting a "degree of compatibility" and somewhere there is a .xlsx file that it can read from... just not the ones we've tried.

In any case glad you have a resolution. Hope it works for you.