help with excel functions

[nmelchi]

Hello -

I'm trying to create a script that does the following...

Open a CSV file on a network drive

Save it to another network drive as Tab delimited text file

I'd like the script to be totally silent, as there are a number of other items that I don't need help with (yet muttley ), that run after these first two steps occur.

I looked at the http://www.autoitscript.com/forum/index.php?showtopic=34302 post and tried using the functions that person setup, but didn't have any success.

Any help would be appreciated.

Thanks in advance.

[big_daddy]

Show us what you have come up with so far.

[nmelchi]

Show us what you have come up with so far.

Sorry for the delayed response... I was tryin gmany different variations of the ExcelComUDF functions, but after looking around, it appears I never actually saved any scripts that didn't produce an error. I took one of the examples from the ExcelComUDF download and put it into my own script (which is below and produces errors upon compile).

The file I'm opening doesn't have any passwords or security on it, and the TXT file I need to convert it to, obviously doesn't need anything secured either. Just need a 'simple' XLS (or CSV) to TXT conversion.

Longer term, this conversion is the first step in the process I need to do. The next steps include, copying said TXT file to a network drive, then kicking off a program and sending some keyboard/mouse clicks to initiate another program that will use the TXT file as input.

Thanks...

Local $oExcel = _ExcelBookOpen("c:\filename.xls")

Func _ExcelBookSaveAs($oExcel, $sFilePath = "c:" & "\temp.txt", $sType = "txt", $fAlerts = 0, $fOverWrite = 1, $sPassword = "", $sWritePassword = "", $iAccessMode = 1, $iConflictResolution = 2)

If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)

If $sType = "xls" or $sType = "csv" or $sType = "txt" or $sType = "template" or $sType = "html" Then

If $sType = "xls" Then $sType = $xlNormal

If $sType = "csv" Then $sType = $xlCSVMSDOS

If $sType = "txt" Then $sType = $xlTextWindows

If $sType = "template" Then $sType = $xlTemplate

If $sType = "html" Then $sType = $xlHtml

Else

Return SetError(2, 0, 0)

EndIf

If $fAlerts > 1 Then $fAlerts = 1

If $fAlerts < 0 Then $fAlerts = 0

$oExcel.Application.DisplayAlerts = $fAlerts

$oExcel.Application.ScreenUpdating = $fAlerts

If FileExists($sFilePath) Then

If NOT $fOverWrite Then Return SetError(3, 0, 0)

FileDelete($sFilePath)

EndIf

If $sPassword = "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, Default, Default, Default, Default, $iAccessMode, $iConflictResolution)

If $sPassword <> "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, $sPassword, Default, Default, Default, $iAccessMode, $iConflictResolution)

If $sPassword <> "" And $sWritePassword <> "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, $sPassword, $sWritePassword, Default, Default, $iAccessMode, $iConflictResolution)

If $sPassword = "" And $sWritePassword <> "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, Default, $sWritePassword, Default, Default, $iAccessMode, $iConflictResolution)

If NOT $fAlerts Then

$oExcel.Application.DisplayAlerts = 1

$oExcel.Application.ScreenUpdating = 1

EndIf

Return 1

EndFunc ;==>_ExcelBookSaveAs

; And finally we close out

_ExcelBookClose($oExcel)

[PsaltyDS]

Sorry for the delayed response... I was tryin gmany different variations of the ExcelComUDF functions, but after looking around, it appears I never actually saved any scripts that didn't produce an error. I took one of the examples from the ExcelComUDF download and put it into my own script (which is below and produces errors upon compile).

The file I'm opening doesn't have any passwords or security on it, and the TXT file I need to convert it to, obviously doesn't need anything secured either. Just need a 'simple' XLS (or CSV) to TXT conversion.

Longer term, this conversion is the first step in the process I need to do. The next steps include, copying said TXT file to a network drive, then kicking off a program and sending some keyboard/mouse clicks to initiate another program that will use the TXT file as input.

Thanks...

Why are you declaring the function _ExcelBookSaveAs() instead of just putting "#include <ExcelCOM_UDF.au3>" at the top of your script and using the function that is already there?

#include <ExcelCOM_UDF.au3>

Local $oExcel = _ExcelBookOpen("c:\filename.xls")
_ExcelBookSaveAs($oExcel, "c:\temp.txt", "txt", 0, 1, "", "", 1, 2)
_ExcelBookClose($oExcel)

muttley

P.S. What version of ExcelCOM_UDF.au3 do you have? Current is version 1.4, Last Update: 01-04-08

P.P.S. My mistake. I missed that you were trying to modify it to be completely silent.

Edited July 16, 2008 by PsaltyDS

[YellowLab]

Is there a reason to do this step in excel?

Couldn't you open the csv file and replace all commas with tabs?

Concept code...

$sfile = FileOpen('myfile',0)
$sfilestring = FileRead($sFile)
FileClose($sFile)
$snewfile = StringReplace($sfilestring,',',Chr(9))
$sfile = FileOpen('newfile',2)
FileWrite($sFile,$snewfile)
FileClose($sfile)

Bob

[nmelchi]

Why are you declaring the function _ExcelBookSaveAs() instead of just putting "#include <ExcelCOM_UDF.au3>" at the top of your script and using the function that is already there?

#include <ExcelCOM_UDF.au3>

Local $oExcel = _ExcelBookOpen("c:\filename.xls")
_ExcelBookSaveAs($oExcel, "c:\temp.txt", "txt", 0, 1, "", "", 1, 2)
_ExcelBookClose($oExcel)

muttley

P.S. What version of ExcelCOM_UDF.au3 do you have? Current is version 1.4, Last Update: 01-04-08

P.P.S. My mistake. I missed that you were trying to modify it to be completely silent.

I have 1.4 of the ExcelCOM_UDF.

[nmelchi]

Is there a reason to do this step in excel?

Couldn't you open the csv file and replace all commas with tabs?

Concept code...

$sfile = FileOpen('myfile',0)
$sfilestring = FileRead($sFile)
FileClose($sFile)
$snewfile = StringReplace($sfilestring,',',Chr(9))
$sfile = FileOpen('newfile',2)
FileWrite($sFile,$snewfile)
FileClose($sfile)

Bob

I'll give that sample code a shot and report back. Thanks.

[nmelchi]

I'll give that sample code a shot and report back. Thanks.

The script you provided seemed to work just fine.

$sfile = FileOpen('myfile',0)

$sfilestring = FileRead($sFile)

FileClose($sFile)

$snewfile = StringReplace($sfilestring,',',Chr(9))

$sfile = FileOpen('newfile',2)

FileWrite($sFile,$snewfile)

FileClose($sfile)

I examined the resulting TXT file and found it looked good. I won't know for sure until I can run it through the final process, but it looks good to me.

Thanks for the help.

[nmelchi]

The script you provided seemed to work just fine.

I examined the resulting TXT file and found it looked good. I won't know for sure until I can run it through the final process, but it looks good to me.

Thanks for the help.

I finished the script and tested it as much as I could before using it in production.

Used the 'CSV to TXT' conversion script, ran it through our data import automation, and found that the script provided above, does exactly what it says. Replaces all commas with tabs. However, I found out that when you Save As tab delimitted TXT in Excel, there is some other logic happening that keeps some commas in the file and converts others to tabs.

Result was about 150k rows of improperlly formated data imported into the system. Luckily we caught it fairly quickly and are fixing it.

Back to the drawing board on the CSV to TXT script. I'm a little gunshy now.