Tool to convert Excel files to CSV

[omar]

Is it possible to use AHK to convert Excel files to CSV?

I was hoping there was - so I could right click an Excel file and save as CSV.

Thanks.

[SifJar]

Code: Select all

path = %1%
SplitPath, path, , dir, , noext
outpath :=  dir . "\" . noext . ".csv"
xl := ComObjCreate("Excel.Application").Workbooks.Open(path)
xl.SaveAs(outpath, 6) 	; type 6 is CSV c.f. https://msdn.microsoft.com/en-us/library/office/ff198017.aspx
xl.close(0) 				; close without saving (already saved, no changes made)

This script will convert the first argument to CSV i.e. if you add it to the context menu it should work. From command line run something like script.ahk C:\example\test.xlsx, and a file "test.csv" will be created in the same directory.

[omar]

@SifJar, OMG, that is SO awesome!

How can I add to the context menu?
The only way I can think of is to add to the 'Sent To' directory and use a batch file.
Is there a way I can directly add?

Thanks.

[SifJar]

Probably the easiest way is to use a tool like this: http://www.nirsoft.net/utils/shell_menu_view.html

This will allow you to find the desired filetype (e.g. .xlsx) and add context menu options for it.

The command you would want to add would be "C:\scripts\conv.ahk" "%1" where C:\scripts\conv.ahk is the path and name of the script, wherever you saved it.

You can also do it directly in the registry, but this is easier.

I think there is a slight error with my script though, this should be the fixed the version:

Code: Select all

path = %1%
SplitPath, path, , dir, , noext
outpath :=  dir . "\" . noext . ".csv"
xl := ComObjCreate("Excel.Application")
xl.DisplayAlerts := 0		; don't display alert when quitting Excel
book := xl.Workbooks.Open(path)
book.SaveAs(outpath, 6) 		; type 6 is CSV c.f. https://msdn.microsoft.com/en-us/library/office/ff198017.aspx
xl.Quit() 					; quit Excel, closing both version of workbook

(in the previous version, the original Excel file is left open)

[kaka]

How can I add to the context menu?

[HKEY_CLASSES_ROOT\Directory\Background\shell\Convert XLSX to CSV\command]
@="your_app_path\name.exe %1%"

you can use this registry for context menu of folders

[sv270190]

Suppose there are six sheets in the workbook. How can we save all as separate csv file say sheets1. Csv like this

[tomoe_uehara]

To separate each sheets:

Code: Select all

path = %1%
SplitPath, path, , dir, , noext
xl := ComObjCreate("Excel.Application").Workbooks.Open(path)
count := xl.Worksheets.Count
Loop, %count%
{
	xl.Worksheets(A_Index).Activate
	name := xl.Worksheets(A_Index).Name
	outpath :=  dir . "\" . noext . "(" . name . ").csv"
	xl.SaveAs(outpath, 6)
}
xl.close(0)		
ExitApp

[sv270190]

Very useful
. Thanks a lot
Xl. Close() and not close(0). Please amend your code

Alerts to be disabled
Once again thanks

[ozzii]

Hi,
I have this code with excel 2010.

Code: Select all

path = D:\aa.xlsm
SplitPath, path, , dir, , noext
xl := ComObjCreate("Excel.Application")
xl.DisplayAlerts := 0		; don't display alert when quitting Excel
book := xl.Workbooks.Open(path)
count := xl.Worksheets.Count
count :=5
Loop, %count%
{
	if A_Index=1
		continue
	book.Worksheets(A_Index).Activate
	name := book.Worksheets(A_Index).Name
	outpath :=  dir . "\" . noext . "(" . name . ").csv"
	book.SaveAs(outpath, 6)
}
;xl.close()		
xl.Quit()
ExitApp

I have this error with the code:

Code: Select all

-----------
excel_export_sheets_csv.ahk
---------------------------
Error:  0x80010001 - L’appel a été rejeté par l’appelé.

Specifically: Worksheets

	Line#
	015: path = D:\aa.xlsm
	016: SplitPath,path,,dir,,noext
	017: xl := ComObjCreate("Excel.Application")
	018: xl.DisplayAlerts := 0  
	019: book := xl.Workbooks.Open(path)
--->	020: count := xl.Worksheets.Count
	022: Loop,%count%
	023: {
	024: if A_Index = 1
	025: Continue
	026: book.Worksheets(A_Index).Activate  
	027: name := book.Worksheets(A_Index).Name

Continue running the script?
---------------------------
Oui   Non   
---------------------------

Also an error with the close, and the excel process keep running.
Any help please !?

[sv270190]

please modify this code for. openoffice spreadsheet