Excel Import and Tabs Question

[water]

Looks like you need to use another value for $iType. I suggest $xlOpenXMLWorkbook.

51 = $xlOpenXMLWorkbook (without macro’s in 2007, .xlsx)
52 = $xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007, .xlsm)
50 = $xlExcel12 (Excel Binary Workbook in 2007 with or without macro’s, .xlsb)
56 = $xlExcel8 (97-2003 format in Excel 2007, .xls)

[Chimaera]

That saves correctly now 

For the help file i think your going to need a reference file for all these settings.

thx again

Edited January 21, 2014 by Chimaera

[water]

All those constants are listed in ExcelConstants.au3.

I have packed as much information as I could get from Microsoft into this file.

But I'm gonna add some description to the help file for function _Excel_BookSaveAs.

[Chimaera]

All those constants are listed in ExcelConstants.au3.

people like me would probably not realise to look in a constants file, same as i never look at Autoit core files we prob just assume the dev knows all and its another necessary include, we probably wouldn't comprehend it anyway.

the list online for the $iType seems sketchy and ambiguous at best so i think some detail would be best.I only knew that there was options to change it as we had discussed csv save once before.

Maybe put in the helpfile For list of possible save types look in Excel Constants.au3 - line 126 or something like that?

Thx again

[water]

I think it should be a mixture.

If you specify $iType then you should take care of the correct combination of extension and $iType.

If you $iType is not specified or set to Default then I could ttry to guess the correct combination based on the Excel version installed and the specified extension.

What do you think?

[Chimaera]

If you specify $iType then you should take care of the correct combination of extension and $iType.

Agreed but i would still say there needs to be a list in the helpfile for it.

Much like this example from the helpfile for MsgBox

i.e. what the $iType value is and the expected result

If you $iType is not specified or set to Default then I could try to guess the correct combination based on the Excel version installed and the specified extension.

Guessing will be a prob because if i was handing out a file to someone i might be using 2013 but i want an .xls because they have 2003 for e.g, but through guessing it would be .xlsx which would be wrong.

Better to give xls as the basic form and allow through $iType options to change the output as needed

Maybe state that if they are choosing a different $iType value then they must specify the required file ending like this example

_Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xlsx", $xlOpenXMLWorkbook, False)

Then that gives them the 2 most popular and a list for the others

Any help?

Edited January 22, 2014 by Chimaera

[water]

The problem is that the available constants and filetypes depend on the installed Excel version.

So a complete list like the one you posted is not possible.

The list available in ExcelConstants.au3 is for Excel 2010.

Means: Everyone who needs a full list for his version of Excel needs to have a look at MSDN.

I think it is sensible to have xls as default for Excel before Excel 2007, and xlsx for newer versions.

Maybe this is something we should discus on the main Excel UDF thread?

[water]

I forgot:

The example script for _Excel_BookSaveAs will load an xls file and save it as xlsx.

More examples can be added if needed.

[Chimaera]

Aah sorry i thought the list was for all versions..  trust MS to make it hard..

[water]

I'll extend the documentation in the help file, add 1 or 2 examples and hope to implement sensible default values.

Next Beta will show

[water]

Hey there

i was running so tests on it before using it properly

and i got this error when a 0.csv file was present in the set

Error 3 from _Excel_RangeCopyPaste

 

Im assuming this is because 1 is the natural starting element for the script

 

Ive had a look at the code but nothing jumps out as a place to alter it, i spoke to my friend who will be using this and he said its unlikely that there would be a 0 based file but he cannot guarantee it

 

Is there a way to make it create the 0 based file just in case?

The whole script I posted is 1 based. It should be easy to make it 0 based by adding 1 to the number of worksheets created and the index of the worksheet where the data is copied to.