AutoHotkey Community
It is currently May 27th, 2012, 1:10 pm

View unanswered posts | View active topics


Board index » AutoHotkey » Scripts

All times are UTC [ DST ]



Post new topic Reply to topic  Page 7 of 20
  [ 296 posts ]  Go to page Previous  1 ... 4, 5, 6, 7, 8, 9, 10 ... 20  Next
  Print view | E-mail friend Previous topic | Next topic 

Should this be continued?
Poll ended at June 18th, 2008, 11:34 pm
Yes 93%  93%  [ 14 ]
No 7%  7%  [ 1 ]
Total votes : 15
Author Message
tank
 Post subject:
PostPosted: August 17th, 2009, 4:06 am 
Offline
User avatar

Joined: December 21st, 2007, 3:14 pm
Posts: 3826
Location: Louisville KY USA
HeWhoWas wrote:
I think Cells.Interior.ColorIndex will return the colour currently filling the cell.
Correct :!:

_________________
No matter what your oppinion Please join this discussion
Formal request to Polyethene
Image


Report this post
Top
 Profile  
Reply with quote  
ahklerner
 Post subject: Convert XML to XLS
PostPosted: August 28th, 2009, 2:44 am 
Offline

Joined: June 26th, 2006, 6:14 pm
Posts: 1379
Location: USA
holy crap! i just now actually read all the posts in here. looks like I have some updating to do. thanks sean for helping out.

i have a new little script here to add first though.
it converts all the files in a directory to XLS.
Code:
; requires excel 2003 pro (or above) i think
Com_Init()
xlNormal = -4143 ; Used for SaveAs

;Workbooks.OpenXML LoadOption Constants
xlXmlLoadPromptUser = 0
xlXmlLoadOpenXml = 1
xlXmlLoadImportToList = 2 
xlXmlLoadMapXml = 3

FileSelectFolder, XML_Path

oExcel := COM_CreateObject("Excel.Application")
COM_Invoke(oExcel,"Visible=",True) ; comment thisline to make it hidden
COM_Invoke(oExcel,"Activate")
oWorkbooks := COM_Invoke(oExcel,"Workbooks")

Loop %XML_Path%\*.xml
   {
   ;COM_Invoke(oWorkbooks,"OpenXML",A_LoopFileFullPath,"vT_NoNe",xlXmlLoadImportToList) ; Should work but doesnt
   COM_Invoke(oWorkbooks,"OpenXML",A_LoopFileFullPath) ; open the file
   StringReplace, OutFileName, A_LoopFileFullPath, .xml, .xls
   oActiveWorkbook := COM_Invoke(oExcel,"ActiveWorkbook")
   com_invoke(oActiveWorkbook,"SaveAs", OutFileName, FileFormat := xlNormal) ; Save the file as .xls
   com_invoke(oActiveWorkbook,"Close") ; Close the file
   com_release(oActiveWorkbook) ; cleanup
   Count := A_Index
   }

COM_Release(oWorkbooks)
COM_Invoke(oExcel,"Quit")
COM_Release(oExcel)
MsgBox Complete. %Count% files converted.
Return   
Bonus points if you can tell me why the commented out com_invoke ine doesnt work.

_________________
Image
ʞɔпɟ əɥʇ ʇɐɥʍ


Report this post
Top
 Profile  
Reply with quote  
tank
 Post subject:
PostPosted: August 28th, 2009, 3:54 am 
Offline
User avatar

Joined: December 21st, 2007, 3:14 pm
Posts: 3826
Location: Louisville KY USA
i dont see where you set Stylesheets ? :?:

_________________
No matter what your oppinion Please join this discussion
Formal request to Polyethene
Image


Report this post
Top
 Profile  
Reply with quote  
ahklerner
 Post subject:
PostPosted: August 28th, 2009, 3:57 am 
Offline

Joined: June 26th, 2006, 6:14 pm
Posts: 1379
Location: USA
i changed my code sorry

_________________
Image
ʞɔпɟ əɥʇ ʇɐɥʍ


Report this post
Top
 Profile  
Reply with quote  
tank
 Post subject:
PostPosted: August 28th, 2009, 4:24 am 
Offline
User avatar

Joined: December 21st, 2007, 3:14 pm
Posts: 3826
Location: Louisville KY USA
might i say firstly it is an honor if my input helps i quite literally have your injectJS function and my use of it initially and learning experience from it for my current JOB

this works for me
Code:
; requires excel 2003 pro (or above) i think
Com_Init()
xlNormal = -4143 ; Used for SaveAs

;Workbooks.OpenXML LoadOption Constants
xlXmlLoadPromptUser = 0
xlXmlLoadOpenXml = 1
xlXmlLoadImportToList = 2
xlXmlLoadMapXml = 3


   If    !oExcel:=COM_GetActiveObject("Excel.Application")
      If    !oExcel:=COM_CreateObject("Excel.Application")
      ExitApp
COM_Invoke(oExcel,"Visible=",True) ; comment thisline to make it hidden
FileSelectFolder, XML_Path

Loop %XML_Path%\*.xml
   {
   COM_Invoke(oExcel,"workbooks.OpenXML",A_LoopFileFullPath,xlXmlLoadImportToList) ; Should work but doesnt
;~    COM_Invoke(oExcel,"workbooks.OpenXML",A_LoopFileFullPath) ; open the file
   StringReplace, OutFileName, A_LoopFileFullPath, .xml, .xls
   oActiveWorkbook := COM_Invoke(oExcel,"ActiveWorkbook")
   com_invoke(oActiveWorkbook,"SaveAs", OutFileName, FileFormat := xlNormal) ; Save the file as .xls
   com_invoke(oActiveWorkbook,"Close") ; Close the file
   com_release(oActiveWorkbook) ; cleanup
   Count := A_Index
   }

COM_Invoke(oExcel,"Quit")
COM_Release(oExcel)
MsgBox Complete. %Count% files converted.
Return   

_________________
No matter what your oppinion Please join this discussion
Formal request to Polyethene
Image


Report this post
Top
 Profile  
Reply with quote  
ahklerner
 Post subject:
PostPosted: August 28th, 2009, 4:39 am 
Offline

Joined: June 26th, 2006, 6:14 pm
Posts: 1379
Location: USA
sorry, but it is 3rd parameter of the OpenXML method that needs to be the list. the second (optional) parameter is stylesheet.

_________________
Image
ʞɔпɟ əɥʇ ʇɐɥʍ


Report this post
Top
 Profile  
Reply with quote  
ahklerner
 Post subject:
PostPosted: August 28th, 2009, 4:41 am 
Offline

Joined: June 26th, 2006, 6:14 pm
Posts: 1379
Location: USA
http://msdn.microsoft.com/en-us/library ... enxml.aspx

_________________
Image
ʞɔпɟ əɥʇ ʇɐɥʍ


Report this post
Top
 Profile  
Reply with quote  
tank
 Post subject:
PostPosted: August 28th, 2009, 4:43 am 
Offline
User avatar

Joined: December 21st, 2007, 3:14 pm
Posts: 3826
Location: Louisville KY USA
the example in the msdn without style sheet skips the second parameter

_________________
No matter what your oppinion Please join this discussion
Formal request to Polyethene
Image


Report this post
Top
 Profile  
Reply with quote  
tank
 Post subject:
PostPosted: August 28th, 2009, 4:46 am 
Offline
User avatar

Joined: December 21st, 2007, 3:14 pm
Posts: 3826
Location: Louisville KY USA
http://msdn.microsoft.com/en-us/library/aa195815.aspx
Code:
Sub UseOpenXML()
    Application.Workbooks.OpenXML _
        Filename:="customers.xml", _
        LoadOption:=xlXmlLoadImportToList
End Sub

_________________
No matter what your oppinion Please join this discussion
Formal request to Polyethene
Image


Report this post
Top
 Profile  
Reply with quote  
Sean
 Post subject:
PostPosted: August 28th, 2009, 5:36 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
Specify as a missing parameter. The MSDN code is in VBA which allows named parameters.
http://www.autohotkey.com/forum/viewtopic.php?t=22923
Code:
COM_Invoke(oWorkbooks,"OpenXML",A_LoopFileFullPath,"-0",xlXmlLoadImportToList)


Report this post
Top
 Profile  
Reply with quote  
tank
 Post subject:
PostPosted: August 28th, 2009, 5:48 am 
Offline
User avatar

Joined: December 21st, 2007, 3:14 pm
Posts: 3826
Location: Louisville KY USA
Sean wrote:
Specify as a missing parameter. The MSDN code is in VBA which allows named parameters.
http://www.autohotkey.com/forum/viewtopic.php?t=22923
Code:
COM_Invoke(oWorkbooks,"OpenXML",A_LoopFileFullPath,"-0",xlXmlLoadImportToList)
result :cry:
Code:
---------------------------
COM Error Notification
---------------------------
Function Name:   "OpenXML"
ERROR:   The parameter is incorrect.

   (0x80070057)
PROG:   
DESC:   
HELP:   ,0

ERROR2:   Member not found.

   (0x80020003)

Will Continue?
---------------------------
Yes   No   
---------------------------
EDIT seems you have updated COM again (i hadnt noticed) :cry:
works perfectly
i had the version from 6-2-2009 and you updated 6-29-2009

_________________
No matter what your oppinion Please join this discussion
Formal request to Polyethene
Image


Report this post
Top
 Profile  
Reply with quote  
Sean
 Post subject:
PostPosted: August 28th, 2009, 6:09 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
tank wrote:
i had the version from 6-2-2009 and you updated 6-29-2009
I think I announced it:
http://www.autohotkey.com/forum/topic22923-385.html

IIRC, the only one I changed after that was altering to use dynamic function call instead of RegisterCallback in COM_DispInterface which should not affect the functionality (so wasn't announced, I suppose).


Report this post
Top
 Profile  
Reply with quote  
tank
 Post subject:
PostPosted: August 28th, 2009, 12:39 pm 
Offline
User avatar

Joined: December 21st, 2007, 3:14 pm
Posts: 3826
Location: Louisville KY USA
well something changed with it because the 6-2 version wouldnt allow the -0 in this particular example ... at any rate :D Thanks

_________________
No matter what your oppinion Please join this discussion
Formal request to Polyethene
Image


Report this post
Top
 Profile  
Reply with quote  
Blind
 Post subject: Strong stuff...
PostPosted: August 28th, 2009, 9:07 pm 
...here. Where is the excel.com.ahk?


Report this post
Top
  
Reply with quote  
Sean
 Post subject:
PostPosted: August 29th, 2009, 12:09 pm 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
tank wrote:
well something changed with it because the 6-2 version wouldnt allow the -0 in this particular example
You may compare the two files using your favorite compare proggy. I'm curious what the 6/2 version was as the announced date was 5/27.


Report this post
Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 7 of 20
  [ 296 posts ]  Go to page Previous  1 ... 4, 5, 6, 7, 8, 9, 10 ... 20  Next

Board index » AutoHotkey » Scripts

All times are UTC [ DST ]

Who is online

Users browsing this forum: Bing [Bot], Bon, sks and 17 guests

You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
cron
Powered by phpBB® Forum Software © phpBB Group