Excel Object Library version conflict (2003 SP2)

Topic

New Reply

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?

Reply | Quote

Replies

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:

Dim xlApp as Object
Set xlApp = CreateObject("Excel.Application")

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

Reply | Quote

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.

Reply | Quote

All you need to do is change xlNormal to -4143 (obviously! )
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 ?xlNormal and press enter.

Reply | Quote

Incidentally, to improve speed (late binding is a little slower than early), I would move the CreateObject line outside your loop:

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

Reply | Quote

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

Reply | Quote

Try

If xlWbk.Worksheets(“Sheet1”).Range(“A1”) = “” Then

Reply | Quote

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

Rory

Reply | Quote

Tried them both and get a “Script out of range” error.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

rory, I guess you answered that question.

Reply | Quote

I have tried each and I get “Object variable or With block Variable not set.

Public Function ConvertFiles()
Dim RS As DAO.Recordset, DB As DAO.Database
Dim strFileName As String
Dim xlObj As Object
Dim xlWbk As Object
Dim xlWsh As Object

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(“tblFileNames”)
Set xlWsh = xlWbk.Sheets(1)
‘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 xlWsh Is Nothing 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

Reply | Quote

Obviously, you cannot refer to a worksheet before you have opened the workbook. The line

Set xlWsh = xlWbk.Sheets(1)

must go below the line

Set xlWbk = xlObj.Workbooks.Open(strFileName)

Reply | Quote

Obviously. No errors now, but after inserting a new worksheet called “Test” and steping through the procedure it reads the IF statement as False so no message.

Reply | Quote

When I step through the code and hover over xlWsh in the IF statement it equals nothing and never changes. Shouldn’t it change to =Test?

Reply | Quote

To be frank, I don’t understand what you want to accomplish.

Here is a version in which the logic has been cleaned up, and with error handling added. It still doesn’t do anything useful.

Public Function ConvertFiles()
Dim RS As DAO.Recordset, DB As DAO.Database
Dim strFileName As String
Dim xlObj As Object
Dim xlWbk As Object

On Error GoTo ErrHandler

Set DB = CurrentDb
Set RS = DB.OpenRecordset(“tblFileNames”)
RS.MoveFirst
Set xlObj = CreateObject(“Excel.Application”)
Do Until RS.EOF
strFileName = RS(“Folder”) & “” & RS(“FileName”)
xlObj.DisplayAlerts = False
Set xlWbk = xlObj.Workbooks.Open(strFileName)
If xlWbk.Worksheets(1).Range(“A1”) = “” Then
MsgBox strFileName & ” ” & “has no Data”
‘ What is the purpose of this?
xlWbk.SaveAs Filename:=strFileName, FileFormat:=-4143
End If
‘ Why save?
xlWbk.Close SaveChanges:=True
RS.MoveNext
Loop

ExitHandler:
On Error Resume Next
RS.Close
Set RS = Nothing
Set DB = Nothing
Set xlWbk = Nothing
xlObj.Quit
Set xlObj = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Function

Reply | Quote

It works perfectly! For the sake of explanation, the spreadsheets were originally delivered to the users as text files (don’t ask me, I have no idea why), and some of the users saved them that way after they added their data. Others however, saved them as xls. To make them consistent I added this function to my procedure prior to importing the data.

Thanks again for your help and patience.

Reply | Quote

Just to expand on Hans’ point: if you want to refer to the first sheet, whatever it may be called, use:
Sheets(1) rather than Sheets("Sheet1")

Reply | Quote

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.

Reply | Quote

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

Reply | Quote