PowerBASIC Peer Support Forums
 

Go Back   PowerBASIC Peer Support Forums > User to user Discussions > Special Interest Groups > Programming COM - Component Object Model

Programming COM - Component Object Model User to user discussions about Component Object Model (COM) programming.

Reply
 
Thread Tools Display Modes
  #1  
Old Feb 8th, 2012, 06:17 PM
Shawn Anderson Shawn Anderson is offline
Member
 
Join Date: Dec 1999
Location: Geneseo, IL USA
Posts: 2,118
save to XLS vs. XLSX

I'm using COM to write a new Excel spreadsheet.

I need to write XLS files rather than XLSX files.
If I just change the file extension when saving, the file will open but it gives me an error.

Is there a way to write a "true" XLS file?

thanks

Code:

#Compile Exe           
#Compiler PBCC 6
#Dim All
#Include Once "../modules/Excel2.inc"   

Function PBMain()
  Dim oExcelApp       As Int__Application
  DIM oExcelWorkbook  AS Int__Workbook
  Dim oExcelWorkSheet As Int__WorkSheet

  Dim vBool    As Variant
  Dim vText1   As Variant
  Dim vText2   As Variant
  Dim vFile    As Variant
  Dim vFileFmt As Variant
  Dim oVnt     As Variant
  Dim nVnt     As Variant
  Dim vX       As Variant
  Dim vY       As Variant
  Dim x        As Long
  Dim y        As Long
  Dim ws       As WSTRING      
  Dim vRange   As Variant      
  Dim vText    As Variant

  ' Open an instance of EXCEL
  oExcelApp = ANYCOM $PROGID_Excel_Application     

  ' Open an instance of EXCEL
  oExcelApp = ANYCOM $PROGID_Excel_Application    
  
  ' Could EXCEL be opened? If not, terminate this app
  If IsFalse IsObject(oExcelApp) Or Err Then
    Print "Excel could not be opened. Please check that Excel and VBA are installed."
    Exit Function
  End If

  ' Create a new workbook in EXCEL
  Object Call oExcelApp.WorkBooks.Add To oExcelWorkBook
  If IsFalse IsObject(oExcelWorkbook) Or Err Then
    Print "Excel could not open a new workbook. Please check that VBA is installed."
    GoTo Terminate
  End If

  Object Call oExcelWorkBook.WorkSheets.Add To oExcelWorkSheet
  If IsFalse IsObject(oExcelWorkSheet) Or Err Then
    Print "Excel could not open a new worksheet. Please check that VBA is installed."
    GoTo Terminate
  End If       

  ' Format and send data for cells A2:G2    
  Let vRange = "A1:A5"$$     
    
  ' Format these cells in money (currency) format, ie: "$#.##"           
  Let vText = "Currency"$$
  Object Let oExcelWorkSheet.Range(vRange).Style = vText     
  
  ' put in some currently values 
    For y = 1 To 5
      Let vX = 1
      Let vY = y
      Object Let oExcelWorkSheet.Cells.Item(vY, vX) = y
    Next x     
  
  ' sum the values  
    Let vX = 1
    Let vY = 6
    Let vText1 = "=sum(a1:a5)"
    Object Let oExcelWorkSheet.Cells.Item(vY, vX) = vText1   


  ' Save the Worksheet to disk - may trigger an "Overwrite?"
  ' prompt if the file already exists.  We could either
  ' delete the file ahead of time or supply a unique name
  vFile = "c:\test\Test2.xls"$$
  Object Call oExcelWorkbook.SaveAs(vFile)    
    

Terminate:
  
  ' close excel
  Object Call oExcelApp.Quit    

  ' Release the interfaces.  We could omit this since the
  ' app is about to close, but "best practice" states we
  ' should clean our house before moving out.                  
  oExcelApp       = Nothing
  oExcelWorkbook  = Nothing
  oExcelWorkSheet = Nothing             
  
  Print "done"
  Sleep 3000

End Function
Reply With Quote
  #2  
Old Feb 8th, 2012, 06:39 PM
Shawn Anderson Shawn Anderson is offline
Member
 
Join Date: Dec 1999
Location: Geneseo, IL USA
Posts: 2,118
I have two versions of Excel on my computer (2003 and 2010).

If I just open Excel 2003 first, it will write correctly.

I'm just wondering how to tell Excel 2010 to write as XLS.

thanks
Reply With Quote
  #3  
Old Feb 8th, 2012, 09:22 PM
Jim Dunn Jim Dunn is offline
Member
 
Join Date: May 2001
Location: USA, North of Mexico, South of Canada
Posts: 629
I think you'll need to modify the .SAVEAS() part of your program.

Here's some sample VBSCRIPT:

Quote:
ActiveWorkbook.SaveAs FileName:="C:\resourcelibrary\Import.xls", FileFormat _
:=xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

ActiveWorkbook.SaveAs "C:\ron.xlsm", fileformat:=52
' 52 = xlOpenXMLWorkbookMacroEnabled = xlsm (workbook with macro's in 2007)
These are the main file formats in Excel 2007:

Quote:
51 = xlOpenXMLWorkbook (without macro's in 2007, .xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, .xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro's, .xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007, .xls)
(you'll need 56)

And some Visual Basic code:

Quote:
'Declaration
Public Overridable Sub SaveAs ( _
<OptionalAttribute> Filename As Object, _
<OptionalAttribute> FileFormat As Object, _
<OptionalAttribute> Password As Object, _
<OptionalAttribute> WriteResPassword As Object, _
<OptionalAttribute> ReadOnlyRecommended As Object, _
<OptionalAttribute> CreateBackup As Object, _
<OptionalAttribute> Optional AccessMode As XlSaveAsAccessMode = XlSaveAsAccessMode.xlNoChange, _
<OptionalAttribute> ConflictResolution As Object, _
<OptionalAttribute> AddToMru As Object, _
<OptionalAttribute> TextCodepage As Object, _
<OptionalAttribute> TextVisualLayout As Object, _
<OptionalAttribute> Local As Object _
)

Private Sub WorkbookSaveAs()
If Me.FileFormat = Excel.XlFileFormat.xlWorkbookNormal Then
Me.SaveAs(Me.Path & "\XMLCopy.xls", _
Excel.XlFileFormat.xlXMLSpreadsheet, _
AccessMode:=Excel.XlSaveAsAccessMode.xlNoChange)
End If
End Sub
POWERBASIC EXCEL.INC code:

MEMBER CALL SaveAs <284> (IN Filename AS WSTRING<0>, OPT IN FileFormat AS VARIANT<1>, OPT IN Password AS VARIANT<2>, OPT _
IN WriteResPassword AS VARIANT<3>, OPT IN ReadOnlyRecommended AS VARIANT<4>, OPT IN CreateBackup AS VARIANT<5>, OPT _
IN AddToMru AS VARIANT<6>, OPT IN TextCodepage AS VARIANT<7>, OPT IN TextVisualLayout AS VARIANT<8>)

It might be possible to just pass the 56 as another variable:

Quote:
Object Call oExcelWorkbook.SaveAs(vFile, 56)
__________________
3.14159265358979323846264338327950
"Ok, yes... I like pie... um, I meant, pi."

Last edited by Jim Dunn; Feb 8th, 2012 at 09:37 PM.
Reply With Quote
  #4  
Old Feb 9th, 2012, 10:31 AM
Shawn Anderson Shawn Anderson is offline
Member
 
Join Date: Dec 1999
Location: Geneseo, IL USA
Posts: 2,118
Hi Jim:
I tried passing like that but it didn't work.
I also tried :

Code:
let fmt=56
Object Call oExcelWorkbook.SaveAs(vFile, fmt)
But it didn't like that either.

I'll keep working on it.

It is interesting that if I simply open Excel 2010, it "wants" to write the default as XLSX, but if I open Excel 2003 first, it writes the XLS correctly.
This is apparently how COM works, and something I'm grasping to understand.

thanks!
Reply With Quote
  #5  
Old Feb 11th, 2012, 09:36 AM
John Petty John Petty is offline
Member
 
Join Date: Feb 2000
Posts: 1,606
I think the fmt should be xlExcel9795 .Thats what I get from recording a Macro.
As for your other question. You are using a version independent method of opening Excel. I believe that method first checks to see if there is a running Excel (any version) and connects to it by COM so not opening another copy. If no version is open then I suspect it opens the newest.
Of course the default save format changed from xls to xlsx in versions after 2003.
Reply With Quote
  #6  
Old Feb 11th, 2012, 12:29 PM
Jim Dunn Jim Dunn is offline
Member
 
Join Date: May 2001
Location: USA, North of Mexico, South of Canada
Posts: 629
Quote:
Originally Posted by Shawn Anderson View Post
It is interesting that if I simply open Excel 2010, it "wants" to write the default as XLSX, but if I open Excel 2003 first, it writes the XLS correctly.
This is apparently how COM works, and something I'm grasping to understand.
That's because you're using ANYCOM in your code. If you use NEWCOM, then it won't use the existing copy of 2003 you have open... but then you'd need to properly close it, otherwise you'll end up with multiple copies running in the background.
__________________
3.14159265358979323846264338327950
"Ok, yes... I like pie... um, I meant, pi."
Reply With Quote
  #7  
Old Feb 11th, 2012, 12:32 PM
Jim Dunn Jim Dunn is offline
Member
 
Join Date: May 2001
Location: USA, North of Mexico, South of Canada
Posts: 629
(moved corrected code to end)
__________________
3.14159265358979323846264338327950
"Ok, yes... I like pie... um, I meant, pi."

Last edited by Jim Dunn; Feb 11th, 2012 at 04:29 PM. Reason: (attached the EXCEL.INC I'm using, as a ZIP file)
Reply With Quote
  #8  
Old Feb 11th, 2012, 02:30 PM
Paul D. Elliott Paul D. Elliott is offline
Member
 
Join Date: May 2002
Location: some hill in WV
Posts: 1,126
Jim,

I'm a little rusty on this so take pity.
Also I'm using Excel 2003 so I'm not sure about saving as XML.
Also needed to change my default folder as Excel was ignoring the
directory for saving.

added
DIM vFalse as variant
let vFalse = 0

and changed to
OBJECT CALL oExcelWorkbook.Close(SAVECHANGES=vFalse)

I uncommented your .SaveAs and if the file exists I get a popup
asking to overwrite or not. Gotta look thru more of my test code
to find the way to overwrite without the popup.

Will be back when/if I find it.
Reply With Quote
  #9  
Old Feb 11th, 2012, 02:52 PM
Paul D. Elliott Paul D. Elliott is offline
Member
 
Join Date: May 2002
Location: some hill in WV
Posts: 1,126
when I try to save a new spreadsheet with just a few numbers as
XML data I get a message
"Can not save XML data because workbook does not contain any XML mappings."

Any clue as to what I'm supposed to put in there for this?
Reply With Quote
  #10  
Old Feb 11th, 2012, 03:49 PM
John Petty John Petty is offline
Member
 
Join Date: Feb 2000
Posts: 1,606
Paul
He wants XLS not XML, you wont get that to work with 2003 unless you have installed the 2007 compatability pack.

Shawn
the error 99 is a problem with the file name not the format. 43 is correct and works.
The differences between inc files with PB versions is a real PIA, switced a working program to the same inc file as you are using (I am on PB9)
the following code almost works (no errors)
Code:
    vtext1 = UCODE$("test")
    vx = 43
    OBJECT CALL oExcelWorkbook.saveas(vtext1, vx)
only problem is it saves as t.xls
if I put in a full path like C:\test\test it saves as c.xls
Its almost 5am, need some sleep will look again tomorrow
Reply With Quote
  #11  
Old Feb 11th, 2012, 04:16 PM
Paul D. Elliott Paul D. Elliott is offline
Member
 
Join Date: May 2002
Location: some hill in WV
Posts: 1,126
John,

Ok. I'm getting threads mixed up.

And I don't recall having to pass anything but a normal string for a file name.
Works fine as a regular string with PB CC v6.
Reply With Quote
  #12  
Old Feb 11th, 2012, 04:22 PM
John Petty John Petty is offline
Member
 
Join Date: Feb 2000
Posts: 1,606
I must have made a typo in testing (couldn't sleep ) this works in 2003
Code:
    vtext1 = "c:\pbprogs\temp\test"
    vx = 43
    OBJECT CALL oExcelWorkbook.saveas(vtext1, vx)
    OBJECT CALL oexcelworkbook.close
    OBJECT CALL oExcelApp.quit
Only thing it doesn't do is add it to the last file opened list, I will leave that to later
Reply With Quote
  #13  
Old Feb 11th, 2012, 04:30 PM
Jim Dunn Jim Dunn is offline
Member
 
Join Date: May 2001
Location: USA, North of Mexico, South of Canada
Posts: 629
Ok, finally realized that %xlExcel9795 was wrong; we need %xlExcel8.

This works (and no POPUPS).

Code:
#Debug Error On
#Debug Display On
#Compile Exe
#Dim All
#Optimize Speed
#Register None
#Tools Off
#UNIQUE VAR On
'#Option AnsiAPI
'#Option WIN95
#Break On
'#Console Off
#Compiler PBCC 6

#Include Once "Excel.inc"

Function PBMain()
    Dim oExcelApp       As Excel_Application
    Dim oExcelWorkbook  As Excel_Workbook
    Dim oExcelWorkSheet As Excel_WorkSheet

    Dim vExcelWorkSheet As Variant

    Dim vBool           As Variant
    Dim vText1          As Variant
    Dim vText2          As Variant
    Dim vFilename       As Variant
    Dim sFilename       As WSTRING
    Dim vFileFormat     As Variant
    Dim nVnt            As Variant
    Dim vX              As Variant
    Dim vY              As Variant
    Dim x               As Long
    Dim y               As Long
    Dim ws              As WSTRING
    Dim vRange          As Variant
    Dim vText           As Variant
    Dim vTrue           As Variant
    Dim vFalse          As Variant

    On Error GoTo MyError

    vTrue = 1
    vFalse = 0

    ' Open an instance of EXCEL
    ? "-> oExcelApp = ANYCOM $PROGID_Excel_Application"
    oExcelApp = AnyCom $PROGID_Excel_Application
    If ObjResult Or Err Then
        ? "Excel could not be opened. Please check that Excel and VBA are installed."
        ? "press a key..." : WaitKey$
        Exit Function
    Else
        ? "(success)"
    End If

    ' Create a new workbook in EXCEL
    ? "-> OBJECT CALL oExcelApp.WorkBooks.Add TO oExcelWorkBook"
    Object Call oExcelApp.WorkBooks.Add To oExcelWorkBook
    If ObjResult Or Err Then
        ? "Excel could not open a new workbook. Please check that VBA is installed."
        ? "press a key..." : WaitKey$
        GoTo Terminate
    Else
        ? "(success)"
    End If

    ? "-> OBJECT CALL oExcelWorkBook.WorkSheets.Add TO oExcelWorkSheet"
    Object Call oExcelWorkBook.WorkSheets.Add To vExcelWorkSheet
    If ObjResult Or Err Then
        ? "Excel could not open a new worksheet. Please check that VBA is installed."
        ? "press a key..." : WaitKey$
        GoTo Terminate
    Else
        ? "(success)"
    End If
    Set oExcelWorkSheet = vExcelWorkSheet

    ' Format and send data for cells A2:G2
    vRange = "A1:A5"$$

    ' Format these cells in money (currency) format, ie: "$#.##"
    vText = "Currency"$$
    Object Let oExcelWorkSheet.Range(vRange).Style = vText

    ' put in some currently values
    For y = 1 To 5
        vX = 1
        vY = y
        Object Let oExcelWorkSheet.Cells.Item(vY, vX) = y
    Next x

    ' sum the values
    vX = 1
    vY = 6
    vText1 = "=sum(a1:a5)"$$
    Object Let oExcelWorkSheet.Cells.Item(vY, vX) = vText1

    ' Save the Worksheet to disk
    sFilename = EXE.Path$ + EXE.Name$ + ".xls"$$
    ? "(" + sFilename + ")"
    vFilename = sFilename
    vFileFormat = %xlExcel8
    ? "OBJECT CALL oExcelWorkbook.SaveAs(...)"
    Object Let oExcelApp.DisplayAlerts = vFalse
    Object Call oExcelWorkbook.SaveAs(vFilename,vFileFormat)
    Object Let oExcelApp.DisplayAlerts = vTrue

    ? "OBJECT CALL oExcelWorkbook.Close(...)"
    Object Call oExcelWorkbook.Close(vFalse)

Terminate:
    ' close excel
    ? "OBJECT CALL oExcelApp.Quit"
    Object Call oExcelApp.Quit

    ' Release the interfaces.  We could omit this since the
    ' app is about to close, but "best practice" states we
    ' should clean our house before moving out.
    Set oExcelApp       = Nothing
    Set oExcelWorkbook  = Nothing
    Set oExcelWorkSheet = Nothing

    ? "Done, press a key..."
    WaitKey$
    Exit Function ' END OF PROGRAM

MyError:
    ? "########################### ERROR ###########################"
    ? Str$(Err) + ", " + ERR$ + ", press a key..."
    ? "########################### ERROR ###########################"
    WaitKey$
    GoTo Terminate

End Function
__________________
3.14159265358979323846264338327950
"Ok, yes... I like pie... um, I meant, pi."

Last edited by Jim Dunn; Feb 11th, 2012 at 04:44 PM. Reason: (fixed .SaveAs)
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 06:01 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 1999-2011 PowerBASIC, Inc. All Rights Reserved.
Error in my_thread_global_end(): 1 threads didn't exit