.csv import into excel

[Sorak99]

Hello,

How do I import a specific .csv file into specific sheet on a .xls.

George

P.S. I have alot of difficulty using the ExcelCOM UDF (and IE for that matter), I appreciate any help.

[randallc]

Hi,

Sorry to hear of the difficulty;

This is straight from the quote on my ExcelCOM first post;

You need beta versio of AutoIt

You need ExcelCOM.au3 in script directory (or change the include and put in beta\include directory)

;importcsv.au3

#include"ExcelCom.au3"

$s_CSVToImport=@ScriptDir&"\test1.csv"

$s_FileToIntake=@ScriptDir&"\book1.xls"

_XLcsvPaste($s_FileToIntake,1,3,5,"Save",$s_CSVToImport,1); import start at col 3 row 5; or put it at 1,1?

MsgBox(0,"","Here it is?")

Does it not work?

Show me the error?

Best, Randall

;importcsv.au3
#include"ExcelCom.au3"
$s_CSVToImport=@ScriptDir&"\test1.csv"
$s_FileToIntake=@ScriptDir&"\book1.xls"
_XLcsvPaste($s_FileToIntake,1,3,5,"Save",$s_CSVToImport,1); import start at col 3 row 5; or put it at 1,1?
MsgBox(0,"","Here it is?")

[Sorak99]

Hi,

Sorry to hear of the difficulty;

This is straight from the quote on my ExcelCOM first post;

You need beta versio of AutoIt

You need ExcelCOM.au3 in script directory (or change the include and put in beta\include directory)

Does it not work?

Show me the error?

Best, Randall

;importcsv.au3
#include"ExcelCom.au3"
$s_CSVToImport=@ScriptDir&"\test1.csv"
$s_FileToIntake=@ScriptDir&"\book1.xls"
_XLcsvPaste($s_FileToIntake,1,3,5,"Save",$s_CSVToImport,1); import start at col 3 row 5; or put it at 1,1?
MsgBox(0,"","Here it is?")

Thanks Randall,

I know that there are examples that go with ExcelCom.au3 but is there a help file or a way i can figure out what goes into the fields for the different functions?

For example I took the following from here

_XLpaste($sFilePath,$Sheet,$Column$Row,$ExcelValue,$Save)

What is $save? 1 for yes, 0 for no?

_XLcalc($sFilePath)

I'm not sure what this does.

I will be working alot with the ExcelCom shortly. Iregret that i can't find much help on a search, perhaps i am not using the right keywords.

Thanks Again Randall,

George

Edit: typo

Edited June 18, 2006 by Sorak99

[Zedna]

For example I took the following from here

What is $save? 1 for yes, 0 for no?

I'm not sure what this does.

You have sources of this UDF, so you can look into it ...

[Sorak99]

You have sources of this UDF, so you can look into it ...

ok...

Func _XLCalc(ByRef $s_FilePath, $s_i_Visible = 0)

$var = _ExcelCOM($s_FilePath, 1, "A", 1, "Calc", "NoSave", 1, $s_i_Visible, "NOTExit", "NOTLastRow", "NOTToColumn"); read cell "E7"

Return $var

Your right, I did find some answers to the first question reading the source. I do appreciate you taking the time to guide me to the obvious, rather than to let me fumble around (sincerely). But forgive me for not being that bright, because I still can't figure out what _XLcalc does.

George

[randallc]

Hi,

It just turns on calculation mode for that call.

Usually any calculation updates for the sheet are not performed until a "save" (I did this so you can perform a number in a row on a large sheet and not have things slow down with recurring calculation; )

$xlCalculationManual = -4135
$xlCalculationAutomatic = -4105
$Calculation = .Application.Calculation
.Application.Calculation = $xlCalculationManualoÝ÷ Ù«­¢+Ù%ÀÌØíÍ}¥}MÙôôÅÕ½ÐíMÙÅÕ½Ðì=ÈÀÌØíÍ}¥}MÙôÄQ¡¸(¹ÁÁ±¥Ñ¥½¸¹
±Õ±Ñ¥½¸ôÀÌØí
±Õ±Ñ¥½¸

Best, Randall

Edited June 18, 2006 by randallc

[randallc]

Sorry about poor documentation;

in the UDF, near the top;

; Parameter(s): $s_FilePath - Path and filename of the excel file

; $s_i_Sheet - Worksheet number [or name]; $s_i_Column -parameter allows column by letter(s) [or numbers], or range [then row is ignored]

; $i_Row - Row number

; $s_MEExcelCom - Type of action; Read, Add, Into, Run, Calc, Paste, Show, Ready, Close, Import

; $s_i_Save - Saves after action ["other"/"Save"] [0/1]

; ;[**NOTE - no worksheet update calculation occurs till save, unless also run "calc", whatever previous setting]

; $s_i_ExcelValue - Value to Insert; dummy required otherwise [may be tab-delimited string

; ; [set $s_i_LastRow or $s_i_ToColumn if needed]

; $s_i_Visible - Set whether to show or not [0/1 - or 0/"Visible"]

; $s_i_Exit - Set whether to Exit or not [0/1 - or 0/"Exit"]

; $s_i_LastRow - Set whether to automatically select row after last as insertion row [0/1]- or 0/"LastRow"

; $s_i_ToColumn - Set whether to insert tab-delimited string as column (1) or not (otherwise)- or 0/"ToColumn"

So can use $s_i_Save - Saves after action ["other"/"Save"] [0/1] ; strings OR integers will work for the parameters

[Sorak99]

Sorry about poor documentation;

in the UDF, near the top;

So can use $s_i_Save - Saves after action ["other"/"Save"] [0/1] ; strings OR integers will work for the parameters

How come when i change:

_XLcsvPaste($s_FileToIntake,$a,1,1,"Save",$s_CSVToImport, 1)

To this:

_XLcsvPaste($s_FileToIntake,$a,1,1,"Save",$s_CSVToImport, 0)

I get an error:

.Application.Calculation = $Calculation

.Application^ERROR

Just curious. the first way works fine for me.

Interesting how the _XLCalc works guess i should learn how Excel works first

The autoit help file is just more familiar to me, but i'm sure reading the UDFs will most likely help me learn how to use this language. I'll stick to it and ask questions when im stuck.

Thanks Randall,

George

[randallc]

Sorry not to be able to help just at the moment. I have been busy.

It sounds as though there is a bug. It might be that using the previous version from the zip file might work but it sounds as though this may have been a persistent problem in any case. I can imagine that one might need to have the worksheet open and visible to be able to copy and paste, though I am not sure.

As an interim workaround, you might want to simply write the import macro and run that from the script, as in the previous post. Please let me know how you get on, and I will try to get back to you later.

_XLmacroRun($TempXLS,1,"persoNAL.XLS!ImportCSV")

There's a sample import macro there;....

ImportCSVRandall