Announcement

Collapse
No announcement yet.

save to XLS vs. XLSX

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      I think you'll need to modify the .SAVEAS() part of your program.

      Here's some sample VBSCRIPT:

      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:

      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:

      '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:

      Object Call oExcelWorkbook.SaveAs(vFile, 56)
      Last edited by Jim Dunn; 8 Feb 2012, 08:37 PM.
      3.14159265358979323846264338327950
      "Ok, yes... I like pie... um, I meant, pi."

      Comment


      • #4
        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!

        Comment


        • #5
          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.

          Comment


          • #6
            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."

            Comment


            • #7
              (moved corrected code to end)
              Last edited by Jim Dunn; 11 Feb 2012, 03:29 PM. Reason: (attached the EXCEL.INC I'm using, as a ZIP file)
              3.14159265358979323846264338327950
              "Ok, yes... I like pie... um, I meant, pi."

              Comment


              • #8
                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.

                Comment


                • #9
                  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?

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      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.

                      Comment


                      • #12
                        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

                        Comment


                        • #13
                          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
                          Last edited by Jim Dunn; 11 Feb 2012, 03:44 PM. Reason: (fixed .SaveAs)
                          3.14159265358979323846264338327950
                          "Ok, yes... I like pie... um, I meant, pi."

                          Comment

                          Working...
                          X