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

    File conversion function (2003 SP2)

    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
    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: File conversion function (2003 SP2)

    Hi,
    You get no error messages because you have an <code>on error resume next</code> statement in there. I suspect your problem is down to your use of an unqualified <code>ActiveWorkbook</code> variable. Try changing <code>ActiveWorkbook.SaveAs</code> to <code>xlWbk.SaveAs</code>
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: File conversion function (2003 SP2)

    Referring to ActiveWorkbook without specifying that it belongs to xlObj is dangerous - it may cause a second instance of Excel to be started that is not controlled by your application. Try replacing ActiveWorkbook with xlWbk.

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

    Re: Thanks folks

    Works every time now. Is there a way to turn off the warnings? Since I'm not renaming the files I get the "file already exists" warning for each file as it is saved.
    Thanks
    chuck

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

    Re: Thanks folks

    You can insert a line

    xlObj.DisplayAlerts = False

    before the loop, and

    xlObj.DisplayAlerts = True

    after it.

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

    Re: Thanks folks

    That did it! Thanks again. Just curious though...why set the alerts to true after each loop? Why not just at the end?
    Thanks
    chuck

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

    Re: Thanks folks

    You could set DisplayAlerts to True at the very end of the code, or even omit it - Excel resets DisplayAlerts to True automatically when code execution ends. But I prefer to set it to True explicitly the moment it is no longer necessary to suppress alerts and warnings, to prevent something slipping by without noticing it.

Posting Permissions

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