Replies

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.

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

rory, I guess you answered that question.

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.

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

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

Steve,

As I was thinking of questions the links you gave me answered them all. I love this forum. Thanks so much.

I applied MarkJ’s tip. Really made a big difference in size on several databases.
Thanks

Hans, I am sorry I couldn’t explain myself better. I was able to work out the issues so I know you would have had you understood what my problem was. The idea came to me as I was walking home last night.

I found it in the help file…daaa.

When I experienced this I was able to correct it by setting the sharing level to ‘edited record’.

Why not use an update query with the update to as Null, no quotes, and the criteria as 0.

Works like a charm…as always. Thanks.

Thank you. I really missed you guys while your site was down.

I have noticed when I use the NZ function I cannot format Currency. Is there a way around this?

“12-34-567-8912345.JOHN DOE” .”MARY SMITH.MIKE JONES” .
7FF6FF6FFF6FFFFFFF*0*DDCD4CDC74*0*7DCDE4EDCEC*0*DCDC4DDDCE74*0*4444444444444444444444444
F12034056708912345*A*1 6 85 046 5 F0*5*F419 8 024 9 38 *A*4 92 5 0165 5 2 F0*5*0000000000000000000000000

This is the HEX view of a string to show where the 0A and 05 characters are placed. I’ve added spaces to make the second and third lines line up as they are read vertically. The * is just to identify the characters. When opened in Excel the Z with the inverted caret shows up between 5 and John Doe, and between H and Mike Jones. Sorry I’m doing such a poor job communicating this. If this still makes no sense just let it go. Thanks for your time.