igmeou

Thanks man. Just another question, can autoIT pass a variable value to VB macro directly? If not maybe I may try to get autoIT to write the required filenames to a file then using vb to read it in then. What do you think?

Ok. Thank you so much randallc I had finally got it to work. For the au3 script for a fix file. Now, I need to know how to import all the dated csv files into a Workbook. How to modify the macro so that I can specify the filename to import and the workbook name and the worksheet to be save in certain workbook? Must it be done inside the excel macro or autoIT script? Or can we write a macro manually into a excel file and excute it?

I think I don't understand your explaination... I'm using the Tools->Macro->Visual Basic Editor on the excel file named "persoNAL.XLS". Then on in the editor, I go under the Sheet1 and paste your code there. After I save that it will produce a macro name "persoNAL.xls!Sheet1.ImportCSV" by default. What do you mean by macro in a hidden worksheet called "persoNAL.XLS"? Is this the excel filename or a hidden worksheet named "persoNAL.XLS"? Oh the ImportCSV is a just the subroutine that I paste into the editor. For 2., it is the same copy of the "persoNAL.XLS" that I copy manually myself to the desktop for testing. I haven't tested/learn how to use the excelcom.au3 yet. And don't know what result to expect so you really need me to test? I'll try it later.

This is my au3 script #include<ExcelCom.au3> ; none of these shorter commands are visible until you say "_XLshow" dim $FilePath,$var1,$LastRow $FilePath="C:\Documents and Settings\Administrator\Desktop\ProxyTemplate1.xls" If Not FileExists($FilePath) Or Not StringInStr($FilePath, "xls") Then $FilePath = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $FilePath, "Worksheet" & " (" & "*.xls" & ")", 1) EndIf $Time1=@HOUR&" hour:"&@MIN&" @MIN:"&@sec&" @sec:" _XLmacroRun($FilePath,"Sheet1","persoNAL.XLS!ImportCSV") _XLshow($FilePath,1) $Time2=@HOUR&" hour2:"&@MIN&" @MIN2:"&@sec&" @sec2:" MsgBox(0,"_XLmacroRun=","$Time1="&$Time1&@CRLF&"$Time2="&$Time2) I attached my excel file below. Ops file too big can't attach. Below is the modified of your Macro. CODESub ImportCSV() ' ' ImportCSV Macro ' Macro recorded 12/09/2005 by Randall Clapp ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Documents and Settings\Administrator\Desktop\gzip\20050905", Destination:=Range( _ "A1")) .Name = "fiftywideBy6" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Application.CommandBars("External Data").Visible = False ChDir "C:\Documents and Settings\Administrator\Desktop\gzip\" Workbook.Save 'Filename:="C:\Documents and Settings\Administrator\Desktop\gzip\book1.xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False End Sub In the Tools -> Macro it is name as "persoNAL.XLS!Sheet1.ImportCSV" Sorry that I can't provide the data file as it is too big and confidential... But it is a space delimited file with filename as "20050905" no extension, and everday I would have 1 file for that. I'm using autoit-v3.1.1.73-beta, office 2003 on a winXP pro system. Is the information sufficient?

Did what u had mention but the error is still the same. But I don't have the personal.xls file as default. I just copy a xls file over and rename it. I having the marcro named as "persoNAL.xls!Sheet1.ImportCSV" does that matter/affect the script?

I need someone to explain this part of the code. ChDir "C:\Program Files\AutoIt3" ActiveWorkbook.Save 'Filename:="C:\Program Files\AutoIt3\book1.xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=FalseWhen I remove the the " ' " b efore the Filename=:... it give me error. How would I be able to save the file into a different location? Do I need to do this on every worksheet to import everyone? How to make my filename to be imported dynamically as I need to to import everynow n then with different filename (normally in the form of date)? For the au3 file this is the error msg. C:\Documents and Settings\...\ExcelCom.au3 (207) : ==> The requested action with this object has failed.: .Application.Run($ExcelValue) .Application.Run($ExcelValue)^ ERRORI suspect the problem is I don't understand this line of code. _XLmacroRun($FilePath,1,[B]"persoNAL.XLS!ImportCSV"[/B])I don't know what should I fill for this. And I don't think I have the personal.xls file around when I tried to search. Note: I'm using office2003 now to test and the final script will be running on office2K. Waoh! That's quite a lengthy one... Can someone pleaes enlighten me please. I really don't know whats the total length. The file can be as low as a few lines to I don't know how many lines... Normally, I would set auto filter to read the imported files.

You're right. I tried with my home system which is Excel 2003 it can't display any data greater than 65535 rows. I think I had mistaken as there isn't any error message pop-up for that. :"> Now I'm trying to figure out how to change that macro and script to suit my path and filename to test. Thanks randallc.

Search for "ball" or "pixel" in this forum. You find lots of useful comments and code needed for learning to script in AutoIT.

Thanks all of your for the prompt reply. I'm using Office 2k for this script and I had came across a msg saying it exceed 65535 rows to be imported but that is only when I try to "open" the csv file. But if I do a proper import of an external file using the import external data tools, I have no issue with the rows limit. All my data will be imported fully.

I got some problem with this script. As my this csv file is around 300MB. So I think it will take years to load into the memory... There is no compile error or things like this, but I have been running it for half an hour on only 1 file and it still not yet done. So I don't think that can be a good solution for me to read the file into the memory. Thanks anyway. I'm still in the middle of testing and finding better solutions. Can anyone help on this?

No problem. I would like to get my things going first while I may convert them to AutoIT later too. PM me the vb script if possible.

Thanks. I tried it out and it give me an error like... $MyObject.Workbooks.Open("c:\temp.csv", 0, false, 3) $MyObject.Workbooks.Open("c:\temp.csv", 0, false, 3)^ ERROR I'll be importing quite a number of files(csv) into a single excel file. Therefore, I need to know how to import can be done. At least you gave me a direction to my problem. Thanks. But I don't know how to solve this error. I had tried adding ","s but it don't do the trick.

Hi, Going through the forum but still couldn't find information how autoIT can import external file into excel. Normally, I would do it in excel using Data->Import External Data->Import Data. Then I will browse for an cvs file and choose the appropriate delimiter and import that file into the excel sheet. Is there any COM function or autoIT statement to automate these? Thanks.

Thanks Mhz.

Anyone got examples of how to use the gzip in AutoIT??