#112002 - 2004-01-18 02:24 PM
Creating .XLS from .CSV via EXCEL using COM
|
GMC
Fresh Scripter
Registered: 2002-01-31
Posts: 17
Loc: Toronto,Canada
|
Hi Everybody
I just started using the COM feature to call EXCEL
Have had success by looking at other examples but cannot
finish what I need to do.
I can import a large .CSV files, make modifications via
COM but cannot seem to eliminate the prompts that appear
to ask me if I want to save the changes I made even though
I have defined the SaveAS file ad .XLS
I need the script to redefine the input from a CSV type
to a workbook type without prompts.
Also is there a way to list out all the properties
that can be called in EXCEL.
Even better would be some sort of beginner documentation
Thanks Gary
|
|
Top
|
|
|
|
#112004 - 2004-01-18 04:06 PM
Re: Creating .XLS from .CSV via EXCEL using COM
|
GMC
Fresh Scripter
Registered: 2002-01-31
Posts: 17
Loc: Toronto,Canada
|
Thanks Shawn
That eliminates the prompt but did not convert the file to .XLS.
I beleive i need the right syntax to include FileFormat:=xlNormal ( from Macro record in EXCEL )
ex .ActiveWorkbook.Saveas ( FileName, FileFormat )
Or maybe some good examples
Thanks Gary
|
|
Top
|
|
|
|
|
#112005 - 2004-01-18 08:56 PM
Re: Creating .XLS from .CSV via EXCEL using COM
|
Shawn
Administrator
Registered: 1999-08-13
Posts: 8611
|
Here is an example line:
$oXL = $oXL.ActiveWorkbook.SaveAs("$SaveAs",-4143,"","",0,0,,,0)
from this thread:
Excel Importation Example
-Shawn
|
|
Top
|
|
|
|
|
#112006 - 2004-01-19 02:26 PM
Re: Creating .XLS from .CSV via EXCEL using COM
|
GMC
Fresh Scripter
Registered: 2002-01-31
Posts: 17
Loc: Toronto,Canada
|
Hi Shawn
Thanks very much that worked. Now I like to know how ?
All documention and examples seemed to be along the lines
of the following
.SaveAS Filename:=(FilePath), FileFormat:=xlNormal
How does -4143 equate to xlNormal
If you run the Macro recorder in Excel you will see.
Thank again Gary
|
|
Top
|
|
|
|
|
#112007 - 2004-01-19 02:54 PM
Re: Creating .XLS from .CSV via EXCEL using COM
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
The value is a constant (aka enumeration), which is translated from the text value to an internal numeric value by the scripting or programming language.
KiXtart does not have access to these constants, so you have to do the translation manually.
Sometimes they are a bugger to find, and the only way to do it is to use an object viewer.
For reference, the xlFileFormat values for Excel 2002 are:
Quote:
typedef enum {
xlAddIn = 18,
xlCSV = 6,
xlCSVMac = 22,
xlCSVMSDOS = 24,
xlCSVWindows = 23,
xlDBF2 = 7,
xlDBF3 = 8,
xlDBF4 = 11,
xlDIF = 9,
xlExcel2 = 16,
xlExcel2FarEast = 27,
xlExcel3 = 29,
xlExcel4 = 33,
xlExcel5 = 39,
xlExcel7 = 39,
xlExcel9795 = 43,
xlExcel4Workbook = 35,
xlIntlAddIn = 26,
xlIntlMacro = 25,
xlWorkbookNormal = 0xffffefd1,
xlSYLK = 2,
xlTemplate = 17,
xlCurrentPlatformText = 0xffffefc2,
xlTextMac = 19,
xlTextMSDOS = 21,
xlTextPrinter = 36,
xlTextWindows = 20,
xlWJ2WD1 = 14,
xlWK1 = 5,
xlWK1ALL = 31,
xlWK1FMT = 30,
xlWK3 = 15,
xlWK4 = 38,
xlWK3FM3 = 32,
xlWKS = 4,
xlWorks2FarEast = 28,
xlWQ1 = 34,
xlWJ3 = 40,
xlWJ3FJ3 = 41,
xlUnicodeText = 42,
xlHtml = 44,
xlWebArchive = 45,
xlXMLSpreadsheet = 46
} XlFileFormat;
xlWorkbookNormal is a very large unsigned number, which appears to be a negative value when used in a signed data type.
|
|
Top
|
|
|
|
|
#112008 - 2004-01-20 02:49 PM
Re: Creating .XLS from .CSV via EXCEL using COM
|
GMC
Fresh Scripter
Registered: 2002-01-31
Posts: 17
Loc: Toronto,Canada
|
Thanks Richard
I am new at COM but was able to accomplish everthing I needed to do.
Is their a way to list all the properties of an object
from KIX. I was trying to get them using the EXCEL macro
recorder but was hung up translating the syntax to KIX
Thanks Gary
|
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 2220 anonymous users online.
|
|
|
