Reports are generated from our mainframe in text format and sent to various recipients. The layout of the reports is in a spreadsheet so to facilitate opening of the file in Excel they are given the xls extension. Each recipient modifys their report and saves it in a folder where I grab it for further processing. When they save their file, some save it as an Excel workbook, while others just save it so it remains in text format. I have to import the data from these files into Access so I created a Function to convert the files to Excel format, but of course it doesn’t work. When I step through the code each variable changes with each loop as expected, and I get no error messages. What am I missing?
Dim RS As DAO.Recordset, DB As DAO.Database
Dim strFileName As String
Dim xlObj As Excel.Application
Dim xlWbk As Excel.Workbook
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”)
Set xlWbk = xlObj.Workbooks.Open(strFileName)
ActiveWorkbook.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
RS.Close
