Mian

How to save a .csv file as an excel file in Excel 2013

10 posts in this topic

Hi, I am new to AutoIT but I have many years of IT experience. I searched a lot for saving the document as xls and xlsx file but no success. The following is my code. Please help.

#include <Excel.au3>

Global $oExcel="D:\ChromeDownloads\abc.csv"
$prog_excel = _Excel_Open() ;opens a new instance of the Excel software
sleep(5000)
$workbook = _Excel_BookOpen($prog_excel, $oExcel) ; open an existing excel file
$oExcel.ActiveWorkbook.SaveAs ( "D:\ChromeDownloads\abc.xlsx",50,"","",False,False)
;_Excel_BookSaveAs($oExcel, "D:\ChromeDownloads\abc", "xlWorkbookDefault", True)
;_Excel_BookClose($oExcel, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of changes

Share this post


Link to post
Share on other sites



You need to add some error checking. What is the value of @error after _Excel_BookOpen or _Excel_BookSaveAs?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Hi my question is how can I save a file as xls type. For example at the moment I am using the following code which is saving a file as an xls format after it is modified in excel.

; Script to save document.

send("{f12}") ; this code open the save as dialog box in excel
sleep(3000)
Send("{TAB 1}"); this code select the save as type option
sleep(3000)
Send("M") ; this code select the type as xls format
sleep(3000)
Send("{TAB 6}"); this code click the save button
Send("S") ; this code click the save button
sleep(3000)
WinWait('Confirm Save As') ; this code wait until the confirmation pop up appears
WinActivate('Confirm Save As') ; this code activate the save as pop up
Send("{TAB 1}") ; this code select the yes button
Send("{ENTER}") ; this code click the save button to overwrite the previously saved file.

 

Edited by Mian

Share this post


Link to post
Share on other sites

If you could post an (anonymized) part of your CSV (just a few lines) I'm sure I can come up with a simple script to open the CSV and save it as XLSX.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

If you could post an (anonymized) part of your CSV (just a few lines) I'm sure I can come up with a simple script to open the CSV and save it as XLSX.

Thank you @water for your quick reply. The file I am working is a long file of more than 200 columns but any csv will do. In fact it doesnt matter that what file I am opening. All I need is to save a file opened in excel as excel file. 

But for you I am adding the sample csv I downloaded from https://support.spatialkey.com/spatialkey-sample-csv-data/  

FL_insurance_sample.csv

Share this post


Link to post
Share on other sites

Rename your CSV file to TXT and the following code will work:
 

#include <excel.au3>
#include <MsgBoxConstants.au3>

Global $sPathTXT = "C:\temp\FL_insurance_sample.txt"
Global $sPathExcel = "C:\temp\FL_insurance_sample.xlsx"
Global $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpenText($oExcel, $sPathTXT)
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookSaveAs($oWorkbook, $sPathExcel)
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error saving the workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#9 ·  Posted

On 12/21/2015 at 9:30 PM, water said:

Rename your CSV file to TXT and the following code will work:
 

#include <excel.au3>
#include <MsgBoxConstants.au3>

Global $sPathTXT = "C:\temp\FL_insurance_sample.txt"
Global $sPathExcel = "C:\temp\FL_insurance_sample.xlsx"
Global $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpenText($oExcel, $sPathTXT)
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookSaveAs($oWorkbook, $sPathExcel)
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error saving the workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

 

This one worked for me. I got more issues that I am facing can I ask them to you 'water'.

Share this post


Link to post
Share on other sites

#10 ·  Posted

Sure. Simply post them here if they are related to the subject of this thread or open a new one.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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