Announcement

Collapse
No announcement yet.

SaveAs method of Workbook class failed

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

  • SaveAs method of Workbook class failed

    I used to be able save an existing csv file to xls using this line of code:

    Code:
    Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\ekrengel\Desktop\CSVDE AD Export\temp.csv")
    
    objExcel.ActiveWorkbook.SaveAs("C:\Documents and Settings\ekrengel\Desktop\CSVDE AD Export\temp.xls"), 43
    Instead I get the attached error.

    The only thing that has changed is that I have upgraded to Office 2007. Are there new SaveAs values in Excel 2007?

    http://www.microsoft.com/technet/scr...6/hey0307.mspx
    Attached Files

  • #2
    Re: SaveAs method of Workbook class failed

    Originally posted by ekrengel View Post
    Are there new SaveAs values in Excel 2007?
    Of course Excel 2007 support some new file formats, but also dropped a few.

    The file format you configured for Saveas is xlExcel9597 Excel 2007 cannot save to that file format.
    Try 56
    http://www.rondebruin.nl/saveas.htm

    \Rems

    This posting is provided "AS IS" with no warranties, and confers no rights.

    __________________

    ** Remember to give credit where credit's due **
    and leave Reputation Points for meaningful posts

    Comment


    • #3
      Re: SaveAs method of Workbook class failed

      I knew I wasn't crazy! lol

      Thanks Rems.

      Comment


      • #4
        Re: SaveAs method of Workbook class failed

        If anyone else had this problem, here's a little snippet you can add for both office versions to save excel 95/97 (xls):
        Code:
        sXLS = "C:\temp.xls"
        
            If GetOfficeVer() = "2007" Then
            	objExcel.ActiveWorkbook.SaveAs(sXLS), 56
            End If
        
            If GetOfficeVer() = "2003" Then
        	objExcel.ActiveWorkbook.SaveAs(sXLS), 43
            End If
        
        Function GetOfficeVer()
          Dim sRegPre, sRegPost, sOfficeVer
          sRegPre = "HKLM\SOFTWARE\Microsoft\Office\"
          sRegPost = "\Common\InstallRoot\"
          Select Case True
            Case RegKeyExists(sRegPre & "12.0" & sRegPost)
              sOfficeVer = "2007"
            Case RegKeyExists(sRegPre & "11.0" & sRegPost)
              sOfficeVer = "2003"
            Case Else
              sOfficeVer = "0"
          End Select
          GetOfficeVer = sOfficeVer
        End Function
        
        Function RegKeyExists(ByVal sRegKey)
          Dim sDescription, oShell
          Set oShell = CreateObject("WScript.Shell")
          RegKeyExists = True
          sRegKey = Trim (sRegKey)
          If Not Right(sRegKey, 1) = "\" Then
            sRegKey = sRegKey & "\"
          End If
          On Error Resume Next
          oShell.RegRead "HKEYNotAKey\"
          sDescription = Replace(Err.Description, "HKEYNotAKey\", "")
          Err.Clear
          oShell.RegRead sRegKey
          RegKeyExists = sDescription <> Replace(Err.Description, sRegKey, "")
          On Error Goto 0
        End Function

        Comment


        • #5
          Re: SaveAs method of Workbook class failed

          Nice ekrengel,


          I have an other
          Code:
          Const Excel2007 = 12
          
          Set objExcel = CreateObject("Excel.Application")
          appVerInt = split(objExcel.Version, ".")(0)
          
          If appVerInt-Excel2007 >=0 Then
            objExcel.ActiveWorkbook.SaveAs(sXLS), 56
          Else
            objExcel.ActiveWorkbook.SaveAs(sXLS), 43
          End If

          This posting is provided "AS IS" with no warranties, and confers no rights.

          __________________

          ** Remember to give credit where credit's due **
          and leave Reputation Points for meaningful posts

          Comment


          • #6
            Re: SaveAs method of Workbook class failed

            That's awesome!



            I didn't think there was any easier way of finding out the version of office apps...this will come in handy, thanks!

            Comment

            Working...
            X