Re: [wxPython-users] wxPython & COM server
nelson -
Hi
I don't know enough about COM and OLE to say for sure (just enough to be
dangerous) but wx doesn't have enough of the plumbing itself required to
do something like this, and although combining with PyWin modules might
make it possible, I expect that it would be real tricky.
In fact i want to combine wxpython and the win32 modules. Googling and starching info make thin
any advice?
thanks,
nelson
irak...@optonline.net
To learn how to use Python and Excel via COM, I used "Python Programming On Win32 : Help for Windows Programmers" by Mark Hammond, Andy Robinson.
I found the easiest way to determine the parameters for Excel commands is to go into Excel itself,
turn on the Macro recording from the menu (Tools -> Macro -> Record New Macro). When recording is completed (press the square stop button on the Macro dialog box), look at the code (Tools -> Macro, highlight the desired macro name, then click [Edit] to view the Visual Basic code). Note the syntax of VB is different from other languages as functions do not seem to require parentheses. Here is the code I captured:
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\ira.kaplan\My Documents\Book1.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
in Python this probably is:
wb.SaveAs(Filename="C:\Documents and Settings\ira.kaplan\My Documents\Book1.xls",
FileFormat= xlNormal,
Password="",
WriteResPassword="",
ReadOnlyRecommended=False,
CreateBackup=False)
Python's False and True boolean built-ins will work for the False and True above.
To get the values of the constant xlNormal and others, using PythonWin IDE's (ActiveState Python distribution) Tools -> COM MakePy utility and select the library for Microsoft Excel n.m Object Library. Look for n.m to be the latest version (but I'm not sure). The results will be in a file such as:
"Generating to C:\Python24\lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x4.py"
>From the file:
xlNormal =-4143 # from enum XlWindowState
In Excel, the built-in Visual Basic editor has a help feature (not always installed by the user. Here you can find all the parameters for SaveAs. It does not seem to return a success code so you can then move on and execute the query.
>From VB Help --------------------------------------------------
Saves changes to the workbook in a different file.
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
expression Required. An expression that returns one of the above objects.
Filename Optional Variant. A string that indicates the name of the file to be saved. You can include a full path; if you don't, Microsoft Excel saves the file in the current folder.
FileFormat Optional Variant. The file format to use when you save the file. For a list of valid choices, see the FileFormat property. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.
Password Optional Variant. A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.
WriteResPassword Optional Variant. A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened, the file is opened as read-only.
ReadOnlyRecommended Optional Variant. True to display a message when the file is opened, recommending that the file be opened as read-only.
CreateBackup Optional Variant. True to create a backup file.
AccessMode Optional XlSaveAsAccessMode.
XlSaveAsAccessMode can be one of these XlSaveAsAccessMode constants.
xlExclusive (exclusive mode)
xlNoChange default (don't change the access mode)
xlShared (share list)
If this argument is omitted, the access mode isn't changed. This argument is ignored if you save a shared list without changing the file name. To change the access mode, use the ExclusiveAccess method.
ConflictResolution Optional XlSaveConflictResolution.
XlSaveConflictResolution can be one of these XlSaveConflictResolution constants.
xlUserResolution (display the conflict-resolution dialog box)
xlLocalSessionChanges (automatically accept the local user's changes)
xlOtherSessionChanges (accept other changes instead of the local user's changes)
If this argument is omitted, the conflict-resolution dialog box is displayed.
AddToMru Optional Variant. True to add this workbook to the list of recently used files. The default value is False.
TextCodePage Optional Variant. Not used in U.S. English Microsoft Excel.
TextVisualLayout Optional Variant. Not used in U.S. English Microsoft Excel.
Local Optional Variant. True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).
--------------------------------------------------------------------------------------
I hope this answers your question and gives you a way to determine the parameters for other Excel statements.
Ira
----- Original Message -----
From: Phill Atwood <m...@phillatwood.name>
Date: Sunday, April 2, 2006 1:53 pm
Subject: [wxPython-users] python and ADO
>
> Perhaps, not the optimum list to ask this on, but...
>
> I'm using Python, win32com and ADO. I have an Access DB with some
> tableslinked to some Excel files. I need to Open and Save an Excel
> file before
> certain queries, since there are some calculations in the Excel file
> that need to figure in. So this is my code, or at least an abbreviated
> version:
>
> cmd = win32com.client.Dispatch("ADODB.Command")
> cmd.ActiveConnection = conn
> cmd.CommandType = 1
> xl = win32com.client.Dispatch("Excel.Application")
> for qtuple in wildpac_query.sql_stmt:
> cmd.CommandText = qtuple[0]
> if qtuple[1]: # Save Excel file flag
> wb = xl.Workbooks.Open(excel_file)
> wb.Save()
> wb.Close()
> cmd.Execute()
> xl.Quit()
>
> Couple of questions. I haven't been able to discover the parms for
> wb.Save() (or wb.SaveAs()). Will my wb.Save() work? And second, are
> there timing issues between the excel save and query execute? If
> so, how
> do I ensure the save is done before the query?
>
> Thanks,
> Phill
>
>
>
>
> --------------------------------------------------------------------
> -
> To unsubscribe, e-mail: wxPython-user...@lists.wxwidgets.org
> For additional commands, e-mail: wxPython-users-
> he...@lists.wxwidgets.org
>
Phill Atwood
Ira,
Thanks, for this. I learned some stuff that I didn't know. However, my
problem turned out to be elsewhere. I'll let you know because it could
bite you or someone else using ADO:
If you have query statements in MS Access with a Like clause that uses
the "*" wildcard, you need to convert the wildcard character to "%"
which is what ADO uses. Extremely not obvious. I was running the queries
by hand in MS Access and they worked. In my code I run the same queries
(cut and paste from the SQL view of Access into my code) and they didn't
work. Until I changed the * to %. Hope this helps someone.
Phill
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: wxPython-user...@lists.wxwidgets.org
> For additional commands, e-mail: wxPython-...@lists.wxwidgets.org
>