Hi,

i have a problem, i get every day from one of our customer an excel sheet.

This sheet i want to convert to an csv file.

How can i do this with automate.

Thanks for your help.

Category Survey

Matthias,
hereunder a basic script that does it perfectly
You may have to trim the #reference line to fit your own Excel installation

Category Survey AutoMate 7 version: Windows version:

Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
'#Reference {00020813-0000-0000-C000-000000000046}#1.6#0#C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE#Microsoft Excel 12.0 Object Library '#Reference {00020813-0000-0000-C000-000000000046}#1.4#0#C:\Program Files\Microsoft Office\Office10\EXCEL.EXE#Microsoft Excel 10.0 Object Library Sub Main StrXLSFile="c:\temp\MyXLS.xls" StrCSVFile="c:\temp\MyCSV.csv" Dim objXlApp As New Excel.Application Dim objXlBook As Excel.Workbook Set objXlBook = Excel.Workbooks.Open( StrXLSFile) objXlApp.DisplayAlerts = False objXlBook.SaveAs StrCSVFile, xlCSV objXlBook.Close SaveChanges:=True objXlApp.Quit Set objXlBook = Nothing Set objXlApp = Nothing End Sub

-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4

Jean, 

I found your code for AutoMate 6 and it works for first worksheet in a workbook.

I have a workbook with 6 worksheets that I want to convert each sheet to a separate .csv file.  I would like to name each file using the Sheet name from the original .xls file.

Thanks for the site to post this question.

Tim

Category Survey

Tim,
the attached bas file converts each sheet to a csv
Just rename it to eliminate the .txt

Category Survey AutoMate 7 version: Windows version:

-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4

Jean,

That worked great, Thanks

My Worksheet has some date columns, when I convert a sheet to a csv manually the Month and Day fields use 2 digits ("01/09/2010")

I have the "Short date" as mm/dd/yyyy in the Regional Options.

This VB does not seem to be using those settings I get ("1/9/2010"), any idea on how to force the settings ?

Tim

Category Survey

Edited: 11/01/2010 at 08:50 AM by TimHicks

It's the same in my system.
You can force the date format by using a statements like
Set objRange = objXlWorksheet.Range("A1","C20")
objRange.NumberFormat = ("mm/dd/yyyy")

after
objXlWorksheet.Activate

where A1:C20 is the range which you want to convert, but if there are other numbers in the cells, they will also be converted
e.g. 6 willl yield 01/06/1900
This must be done selectively for the real date cells.

You must know upfront where the dates are located, which is not easy.
That must also be feasible programmatically, but I don't have time now to do it.

Category Survey AutoMate 7 version: Windows version:

-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4

I know this is an old post, but I found an error in the code. It leaves an orphaned Excel process every time it runs:

Sub Main
1 StrXLSFile="c:\temp\MyXLS.xls"
2 StrCSVFile="c:\temp\MyCSV.csv"
3 Dim objXlApp As New Excel.Application
4 Dim objXlBook As Excel.Workbook
5 Set objXlBook = Excel.Workbooks.Open( StrXLSFile)
6 objXlApp.DisplayAlerts = False
7 objXlBook.SaveAs StrCSVFile, xlCSV
8 objXlBook.Close SaveChanges:=True
9 objXlApp.Quit
10 Set objXlBook = Nothing
11 Set objXlApp = Nothing
End Sub
...

Line 3 (objXlApp) dims the Excel object reference, but the line 5 does not use that object. Instead it directly calls Excel to set the workbook (Set objXlBook = Excel...). At this point the script loses its reference to the original Excel object reference. Later, when line 11 attempts to set the Excel object to nothing, it fails.

Fix:
I changed line 5 to

Set objXlBook = objXlApp.Workbooks.Open(StrXLSFile)

All object references are intact...no orphan Excel process.

Category Survey AutoMate 7 version: Windows version: Windows 7