![]() |
![]() |
We are currently migrating this forum over to our HelpSystems domain. Please post all new threads in our new HelpSystems Community Portal. |
![]() |
|
![]() |
AutoMate Discussion | ![]() |
help :
faq :
home
|
||
Latest News:
|
latest topics : statistics |
Topic Title: Convert xls file to csv Topic Summary: Created On: 01/13/2010 09:58 AM Status: Read Only |
Linear : Threading : Single : Branch |
![]() |
![]()
|
![]() Answer: Matthias, hereunder a basic script that does it perfectly You may have to trim the #reference line to fit your own Excel installation | ||||||||||
![]() |
||||||||||
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.
|
||||||||||
|
||||||||||
![]() |
||||||||||
Matthias,
hereunder a basic script that does it perfectly You may have to trim the #reference line to fit your own Excel installation
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) ------------------------- 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
|
||||||||||
|
||||||||||
![]() |
||||||||||
Tim,
the attached bas file converts each sheet to a csv Just rename it to eliminate the .txt
------------------------- 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
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.
------------------------- 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.
|
||||||||||
|
AutoMate Discussion
» AUTOMATE » AutoMate 7
»
Convert xls file to csv
|
![]() |
FuseTalk Enterprise Edition v4.0 - © 1999-2021 FuseTalk Inc. All rights reserved.