Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Excel Object Library version conflict (2003 SP2)

    I share a database with a user running Access 2000. One of the procedures references Excel so I added the Excel Object Library, but my only choice is 11 since I am using 2003 and he need 9. We reset the library to 9 from his machine but every time I open the database it sets it back to 11. Can I check the version with a procedure and set the library?
    Thanks
    chuck

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Object Library version conflict (2003 SP2)

    You will probably find it easier, particularly if it is only one procedure, to use late binding and remove the reference altogether. Then declare all your Excel variables as Objects and use syntax like:
    <pre>Dim xlApp as Object
    Set xlApp = CreateObject("Excel.Application")
    </pre>

    Note: if you use any Excel constants in the code, you will either need to declare them or use the literal values instead.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Object Library version conflict (2003 SP2)

    rory, you know I love this stuff, but I know so little. I looked up late binding in the knowledge base since I was not familiar with the term and applied your example. I am converting text files to excel format. Here's my procedure:
    Public Function ConvertFiles()
    Dim RS As DAO.Recordset, DB As DAO.Database
    Dim strFileName As String
    Dim xlObj As Object
    Dim xlWbk As Object
    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset("tblFileNames")
    'On Error Resume Next
    RS.MoveFirst
    Do Until RS.EOF
    strFileName = RS("Folder") & "" & RS("FileNames")
    Set xlObj = CreateObject("Excel.Application")
    xlObj.DisplayAlerts = False
    Set xlWbk = xlObj.Workbooks.Open(strFileName)
    xlWbk.SaveAs FileName:= _
    strFileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    RS.MoveNext
    xlWbk.Close SaveChanges:=True
    Set xlWbk = Nothing
    xlObj.Quit
    Set xlObj = Nothing
    Loop
    xlObj.DisplayAlerts = True
    RS.Close
    End Function

    I figure I could drop everything past "strFileName, FileFormat:=xlNormal", but I don't know how to declare xlNormal.
    Thanks
    chuck

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Object Library version conflict (2003 SP2)

    All you need to do is change <code>xlNormal</code> to <code>-4143</code> (obviously! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    You can look up the value of any Excel constant in the Object Browser in the Excel VBEditor, or use the immediate window and just type <code>?xlNormal</code> and press enter.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Object Library version conflict (2003 SP2)

    Incidentally, to improve speed (late binding is a little slower than early), I would move the CreateObject line outside your loop:
    <pre>Public Function ConvertFiles()
    Dim RS As DAO.Recordset, DB As DAO.Database
    Dim strFileName As String
    Dim xlObj As Object
    Dim xlWbk As Object
    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset("tblFileNames")
    'On Error Resume Next
    RS.MoveFirst
    Set xlObj = CreateObject("Excel.Application")
    xlObj.DisplayAlerts = False
    Do Until RS.EOF
    strFileName = RS("Folder") & "" & RS("FileNames")
    Set xlWbk = xlObj.Workbooks.Open(strFileName)
    xlWbk.SaveAs FileName:= _
    strFileName, FileFormat:=-4143, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    RS.MoveNext
    xlWbk.Close SaveChanges:=True
    Set xlWbk = Nothing
    Loop
    xlObj.DisplayAlerts = True
    xlObj.Quit
    Set xlObj = Nothing
    RS.Close
    End Function
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Object Library version conflict (2003 SP2)

    In the first place, when you use late binding (i.e. remove the reference to the Microsoft Excel n.0 Object Library), you must replace all Excel constants with their values, as Rory indicated. Change xlNormal to -4143. You can find this value by typing

    ? xlNormal

    in the Immediate window before removing the reference (or look it up in Excel).

    In the second place, you cannot use

    xlObj.DisplayAlerts = True

    after quitting xlObj and setting it to Nothing. Since you quit Excel anyway, you don't need the line.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Object Library version conflict (2003 SP2)

    Gentleman, your expertise is greatly appreciated. God bless you.
    Thanks
    chuck

  8. #8
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Object Library version conflict (2003 SP2)

    Recently one of the spreadsheets I had to process had the data in sheet2 rather than sheet1 so my process did not pull any data. I thought I could modify this conversion function to include checking cell A1 for null with an IF statement. How do I refer to the open workbook? My code gives an error "Object doesn't support this property or method" at the IF statement line.

    Public Function ConvertFiles()
    Dim RS As DAO.Recordset, DB As DAO.Database
    Dim strFileName As String
    Dim xlObj As Object
    Dim xlWbk As Object
    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset("tblFileNames")
    'On Error Resume Next
    RS.MoveFirst
    Do Until RS.EOF
    strFileName = RS("Folder") & "" & RS("FileName")
    Set xlObj = CreateObject("Excel.Application")
    xlObj.DisplayAlerts = False
    Set xlWbk = xlObj.Workbooks.Open(strFileName)
    If IsNull(xlWbk!Sheet1.A1) Then
    MsgBox strFileName & " " & "has no Data"
    xlWbk.SaveAs FileName:= _
    strFileName, FileFormat:=-4143
    End If
    RS.MoveNext
    xlWbk.Close SaveChanges:=True
    Set xlWbk = Nothing
    xlObj.Quit
    Set xlObj = Nothing
    Loop
    RS.Close
    End Function
    Thanks
    chuck

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Object Library version conflict (2003 SP2)

    Try

    If xlWbk.Worksheets("Sheet1").Range("A1") = "" Then

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Object Library version conflict (2003 SP2)

    Assuming there is no formula in A1, you can use:
    <code>If xlWbk.sheets("Sheet1").Range("A1").Value = "" Then</code>
    HTH

    Rory
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Object Library version conflict (2003 SP2)

    Tried them both and get a "Script out of range" error.
    Thanks
    chuck

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Object Library version conflict (2003 SP2)

    That would mean that there is no Sheet1 at all. Of course, you cannot refer to Sheet1 if it doesn't exist. you can try something like this:

    Dim xlWsh As Object
    ' Suppress error messages
    On Error GoTo Next
    ' Try to refer to Sheet1
    Set xlWsh = xlWbk.Worksheets("Sheet1")
    ' Restore error handling
    On Error GoTo 0
    ' Check if worksheet exists
    If xlWsh Is Nothing Then
    MsgBox "blah blah..."
    Else
    ...
    End If

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Object Library version conflict (2003 SP2)

    Just to expand on Hans' point: if you want to refer to the first sheet, whatever it may be called, use:
    <code>Sheets(1)</code> rather than <code>Sheets("Sheet1")</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Object Library version conflict (2003 SP2)

    Do you mean the sheet is not named sheet1? That would be true. I now have hundreds of these spreadsheets to process and each user creatively names there first sheet in their workbook. I'm working on the code you gave me.
    Thanks
    chuck

  15. #15
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Object Library version conflict (2003 SP2)

    rory, I guess you answered that question.
    Thanks
    chuck

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •