Sign in to follow this  
Followers 0
nmelchi

help with excel functions

9 posts in this topic

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.

Share this post


Link to post
Share on other sites



Show us what you have come up with so far.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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


You can't see a rainbow without first experiencing the rain.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0