Tool to convert Excel files to CSV

Get help with using AutoHotkey and its commands and hotkeys
omar
Posts: 398
Joined: 22 Oct 2015, 17:56

Tool to convert Excel files to CSV

03 Apr 2016, 15:50

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
Posts: 398
Joined: 11 Jan 2016, 17:52

Re: Tool to convert Excel files to CSV

03 Apr 2016, 18:59

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
Posts: 398
Joined: 22 Oct 2015, 17:56

Re: Tool to convert Excel files to CSV

04 Apr 2016, 04:33

@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
Posts: 398
Joined: 11 Jan 2016, 17:52

Re: Tool to convert Excel files to CSV

04 Apr 2016, 07:51

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
Posts: 27
Joined: 16 Nov 2013, 06:08

Re: Tool to convert Excel files to CSV

06 Apr 2016, 07:04

omar wrote: 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
Posts: 43
Joined: 06 Feb 2014, 11:48
Facebook: [email protected]

Re: Tool to convert Excel files to CSV

28 Jan 2017, 17:40

Suppose there are six sheets in the workbook. How can we save all as separate csv file say sheets1. Csv like this
S.V. SRINIVASAN
SRIVILLIPUTTUR
TAMIL NADU
User avatar
tomoe_uehara
Posts: 210
Joined: 05 Oct 2013, 12:37
Contact:

Re: Tool to convert Excel files to CSV

29 Jan 2017, 07:21

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
Posts: 43
Joined: 06 Feb 2014, 11:48
Facebook: [email protected]

Re: Tool to convert Excel files to CSV

30 Jan 2017, 10:59

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

Alerts to be disabled
Once again thanks
S.V. SRINIVASAN
SRIVILLIPUTTUR
TAMIL NADU
ozzii
Posts: 327
Joined: 30 Oct 2013, 06:04

Re: Tool to convert Excel files to CSV

05 Mar 2017, 06:27

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 !?

Return to “Ask For Help”

Who is online

Users browsing this forum: asad41163, Bad husband, Bing [Bot], boiler and 196 guests