 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
icefreez
Joined: 15 May 2007
Posts: 34
|
 Posted: Tue Oct 09, 2007 2:53 pm Post subject: |
 |
|
I figured it out. I was running this script from my Desktop which I have no doubt the path caused the problem. "C:\Documents and Settings\David\Desktop\xlsconvert\" I have moved it to a directory in my HD's root folder and it ran just fine "C:\xlsconvert\.
I also found out the file names can not have a space him them either.
Is there anyways I can set it up to just convert the first worksheet?, and remove the file after it has finished converting it?
This is the naming procedure I am shooting for: Goes in as test.xls, comes out as test.dat (CSV formated)
Thx for your time. |
|
| Back to top |
|
 |
icefreez
Joined: 15 May 2007
Posts: 34
|
| Posted: Tue Oct 09, 2007 3:37 pm Post subject: |
|
|
With the help of a friend (more fluent in VB than I) I was able to modify the script to perform the exact conversion I needed.
This modified version of the script will take the original file (test.xls) and convert it into a CSV formated file (test.dat) with the custom extention change of .dat. It will process all XLS files this way then delete the left over XLS files.
| Code: | #SingleInstance force
chosen_folder = %A_ScriptDir%
ConvertXlsCsv(chosen_folder)
Exitapp
ConvertXlsCsv(folder="") {
{ ;FOLDER
If folder =
{
FileSelectFolder, folder, , , Folder with xls files
If ErrorLevel = 1
Return
IfNotExist %folder%
Return
}
}
{ ;LIST FILES TO BE CONVERTED
Loop, %folder%\*.xls
files=%files%`n%A_LoopFileName%
;MsgBox, 0, Files to be converted, %files%, 3
}
{ ;VBS CODE
;ORIGINAL VBS CODE FROM Jimmy The Fish
;http://forums.xaprief.com/showthread.php?s=&threadid=3159
vbs_code =
(
option explicit
dim a, arg, oArgs, ArgNum
a = 0
Set oArgs = WSCript.arguments
ArgNum = oArgs.Count
if ArgNum <> 1 then
WSCript.echo "Syntax: cscript <Script> filename_root"
WScript.quit(1)
end if
dim filename_root, oldfilename, pos,newname
oldfilename = oArgs(0)
rem Remove .xls file extension if it was provided
dim ext
ext = right( oldfilename, 4 )
if lcase( ext ) = ".xls" then
oldfilename = left( oldfilename, len( oldfilename ) - 4 )
end if
pos = instr(oldfilename, " ")
if pos > 0 then
newname = right(oldfilename, len(oldfilename) - instrrev(oldfilename, "\"))
newname = replace(newname," ","_")
filename_root = left(oldfilename, instrrev(oldfilename, "\")) & newname
dim oShell
set oShell = Wscript.CreateObject("WScript.Shell")
Wscript.echo "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
oShell.run "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
else
filename_root = oldfilename
end if
dim app
set app = createobject("Excel.Application")
dim wb
set wb = app.workbooks.open( filename_root & ".xls" )
const xlXMLSpreadsheet = 46
const xlCSV = 6
app.DisplayAlerts = false
dim sht
for each sht in wb.worksheets
sht.activate
dim output_filename
'output_filename = filename_root & "_" & replace( sht.name, " ", "_" ) & ".csv"
'--------------------------------------------------------------------------------------
' ----------MODIFIED to save to with original file name but use.dat extention----------
'--------------------------------------------------------------------------------------
output_filename = filename_root & ".dat"
wb.saveAs output_filename, xlCSV
'--------------------------------------------------------------------------------------
' ---------------------Break stops script after first workbook-------------------------
'--------------------------------------------------------------------------------------
'break
app.DisplayAlerts = true
wb.close false
WScript.quit
next
'wb.saveAs filename_root & ".xml", xlXMLSpreadsheet
app.DisplayAlerts = true
wb.close false
'app.close
WScript.quit
)
FileDelete, %folder%\xls2csv.vbs
FileAppend, %vbs_code%, %folder%\xls2csv.vbs
}
{ ;BATCH FILE
FileDelete, %folder%\xls2csv.bat
FileAppend, for `%`%i in (*.xls) do cscript xls2csv.vbs `%`%~dpni > nul , %folder%\xls2csv.bat
}
{ ;RUN VBS FILE THROUGH BAT
RunWait, %folder%\xls2csv.bat, %folder%
}
{ ;DELETE TEMP FILES
FileDelete, %folder%\xls2csv.vbs
FileDelete, %folder%\xls2csv.bat
}
{ ;WipeXLS files
FileDelete, %folder%\*.xls
}
{ ;CONVERT INTO PORTUGUESE FORMAT (OPTIONAL)
If A_Language = 0416
{
ConvertCsvSemiColon(folder)
MsgBox, 0, Arquivos convertidos em CSV português, Verifique a pasta original, 15
Return
}
}
;MsgBox, 0, Files converted, Please check original folder, 15
}
ConvertCsvSemiColon(folder) {
Loop, %folder%\*.csv
{
;REWRITE ONLY FILES MODIFIED IN THE LAST 5 MINUTES
FileGetTime, file_time, %A_LoopFileName%, M
minutes_ago = %A_Now%
minutes_ago += -5, Minutes
If file_time < %minutes_ago%
Continue
;READ CONTENT
FileRead, content, %A_LoopFileName%
;REPLACE COMMAS ETC
StringReplace, content, content, `,, `;, all
StringReplace, content, content, `., `,, all
;REWRITE CSV
FileDelete, %A_LoopFileName%
FileAppend, %content%, %A_LoopFileName%
}
} |
|
|
| Back to top |
|
|
lingoist
Joined: 05 Oct 2004
Posts: 118
Location: Brasília, Brazil
|
| Posted: Thu Oct 11, 2007 3:13 am Post subject: |
|
|
| icefreez wrote: | I am unable to get this script to convert an XLS to a CSV file. It shows "Files to be converted, runs the bat file, then says "Files Converted" "Please checked the original folder" The file remains unchanged in the scripts directory, and no new files have been created.
Any suggestions what is going wrong? |
Try to use:
#SingleInstance force
chosen_folder =
ConvertXlsCsv(chosen_folder)
Exitapp |
|
| Back to top |
|
|
neXt
Joined: 19 Mar 2007
Posts: 429
|
| Posted: Mon Jan 21, 2008 6:23 pm Post subject: |
|
|
| Any ideas on how do i make it work with spaces in file path/name? |
|
| Back to top |
|
|
lingoist
Joined: 05 Oct 2004
Posts: 118
Location: Brasília, Brazil
|
| Posted: Mon Jan 21, 2008 10:39 pm Post subject: |
|
|
| Sorry... I don't thing it's possible with spaces... |
|
| Back to top |
|
|
ahklerner
Joined: 26 Jun 2006
Posts: 1090
Location: USA
|
| Posted: Mon Jan 21, 2008 11:38 pm Post subject: |
|
|
can you use the short path instead of the full path?
| Code: | MsgBox % GetShortName("C:\Documents and Settings\FooUser\Really Long Path With Spaces\temp.ahk")
GetShortName(File) {
Loop, %File%
Return A_LoopFileShortPath
} |
_________________
 |
|
| Back to top |
|
|
Guest
|
| Posted: Tue Jan 22, 2008 12:26 am Post subject: |
|
|
| I don't think that the VB script would word... |
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
