+ Reply to Thread
Results 1 to 6 of 6

convert all 97-2003 excel documents to macro enabled documents on server

Hybrid View

  1. #1
    Registered User
    Join Date
    07-19-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2007
    Posts
    11

    convert all 97-2003 excel documents to macro enabled documents on server

    I am trying to go through every file on the server and convert the old excel document to a new macro enabled one. This needs to be done in some way shape or form. It will convert hundreds of files then randomly stop with an open excel document left on my screen and the code stops without giving an error. This is more of a headache as I have to restart the conversion. Can someone tell me why this is happening.

    
    Sub ListFiles()
    
         
         'Declare the variables
         Dim objFSO As Scripting.FileSystemObject
         Dim objTopFolder As Scripting.Folder
         Dim strTopFolderName As String
         countYes = 0
         countNo = 0
         Dim temp As String
         
           Dim time1 As Double, time2 As Double
           Dim hour, min, sec As Double
           
    
        'Assign the top folder to a variable
         strTopFolderName = "H:\"
    
        Set startWork = Workbooks("CHANGE EXCEL.xlsm")
     
    '  Call StartEmail
    
         
          time1 = Timer
         
         'Create an instance of the FileSystemObject
         Set objFSO = CreateObject("Scripting.FileSystemObject")
         
         'Get the top folder
         Set objTopFolder = objFSO.GetFolder(strTopFolderName)
         Range("A4").Select
         
         'Call the RecursiveFolder routine
         filePaths = strTopFolderName
         Application.DisplayAlerts = False
         Call RecursiveFolder(objTopFolder, True)
         
    
         
    '     strTopFolderName = "L:\"
    '
    '
    '  'Create an instance of the FileSystemObject
    '     Set objFSO = CreateObject("Scripting.FileSystemObject")
    '
    '     'Get the top folder
    '     Set objTopFolder = objFSO.GetFolder(strTopFolderName)
    '     Range("A4").Select
    '     'Call the RecursiveFolder routine
    '    filePaths = filePaths + vbNewLine + strTopFolderName
    '     Application.DisplayAlerts = False
    '     Call RecursiveFolder(objTopFolder, True)
         
         
         Range("B1").Value = filePaths
         Range("B2").Value = countYes
         Range("B3").Value = countNo
         Range("B6") = "DONE"
          time2 = Timer
          
       hour = Application.WorksheetFunction.RoundDown(((time2 - time1) / 3600), 0)
    min = Application.WorksheetFunction.RoundDown(((time2 - time1 - (hour * 3600)) / 60), 0)
    sec = Application.WorksheetFunction.Round((((time2 - time1 - (60 * min) - (hour * 3600)))), 0)
    Range("B4") = hour & "  HOURS  " & min & "  MINUTES  " & sec & "  SECONDS  "
    Range("B5") = (time2 - time1) & " SECONDS"
    
       Application.DisplayAlerts = True
       
       Call Complete
       
    End Sub
    
    Sub RecursiveFolder(objFolder As Scripting.Folder, _
         IncludeSubFolders As Boolean)
    
         'Declare the variables
         Dim objFile As Scripting.File
         Dim tempString As String
         Dim objSubFolder As Scripting.Folder
         Dim NextRow As Long
    
         'Find the next available row
         NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
         
         'Loop through each file in the folder
         For Each objFile In objFolder.Files
         
                Workbooks("CHANGE EXCEL.XLSM").Activate
                Application.ScreenUpdating = True
                tempString = objFile
                Range("B6") = tempString
    
             Application.ScreenUpdating = False
       
             If UCase(Right(objFile, 3)) = "XLS" Then
                Application.EnableEvents = False
        
                Workbooks.Open Filename:=objFile
                ActiveWorkbook.SaveAs Filename:= _
                objFile & "m", FileFormat:= _
                xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
                ActiveWorkbook.Close
                Kill objFile
                Cells(countYes + 9, 1) = Now()
                 Cells(countYes + 9, 2) = tempString
                countYes = countYes + 1
                Else
                countNo = countNo + 1
                End If
                Application.EnableEvents = True
                Application.ScreenUpdating = True
     
         Next objFile
         
    
    'Loop through files in the subfolders
         If IncludeSubFolders Then
             For Each objSubFolder In objFolder.SubFolders
             filePaths = filePaths + vbNewLine + objSubFolder
                 Call RecursiveFolder(objSubFolder, True)
             Next objSubFolder
         End If
         
         Exit Sub
    
         
    End Sub

  2. #2
    Registered User
    Join Date
    07-19-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: convert all 97-2003 excel documents to macro enabled documents on server

    Run time error '1004':
    Method 'SaveAs' of object'_Worksheet' failed

                ActiveWorkbook.SaveAs Filename:= _
                objFile & "m", FileFormat:= _
                xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

  3. #3
    Registered User
    Join Date
    07-19-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: convert all 97-2003 excel documents to macro enabled documents on server

    And Run-time error '75':
    Path/File access error
                Kill objFile

  4. #4
    Registered User
    Join Date
    07-19-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: convert all 97-2003 excel documents to macro enabled documents on server

    Hello,

    I'm not sure that I have a solution to your problem, but it sounds as if the connection between Excel and your servers has broken somewhere in the process (which is why Excel cannot save directly to the server, or delete an object from the server). I have come across this issue many times over the years, and whilst frustrating I'm not sure there is much you can do about it. The fact that the error occurs 'randomly' means it will be difficult to point the finger at the code as in some passes a file may work fine, and in others it may stop. I suspect then that it is nothing to do with your code or the actual files themselves, but more likely to be caused by an unreliable server connection (even a split second flicker can cause Excel to break).

    There maybe some clever API calls that can re-establish the connection that may get around this, but I have never heard of anything.

    I think one solution that may 'help' would be to maintain a text file log of the files that you have converted successfully as the code runs, so that the next time it breaks you can resume the routine where it left off, rather than having to re-save every workbook each time. Alternatively, you could just check to see whether a file of 'xlsm' type already exists, and skip over the file if this is the case?

    Not very helpful I'm afraid, but hopefully a couple of pointers for you here.

    Kind regards,
    Simon

  5. #5
    Registered User
    Join Date
    07-19-2013
    Location
    Fort Wayne, IN
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: convert all 97-2003 excel documents to macro enabled documents on server

    I just wanted someone to look at it and see what happens. I will add in the code to write to a text file , but I am curious how I would get it to know how many recursions it has gone through. Also I have it check to see if the file is an .xls
    If UCase(Right(objFile, 3)) = "XLS" Then
    So the next time it starts it zips through pretty quickly. I just wanted to run this code through the weekend and I can't without babysitting it. The server has about 30Gb of info so I really just wanted it to run through without someone watching it.

    Thanks again

  6. #6
    Registered User
    Join Date
    01-24-2013
    Location
    Den Haag, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: convert all 97-2003 excel documents to macro enabled documents on server

    Maybe just create a list of filenames handled, and include a check that skips allready handled files.

    -edit- (sorry, mis-read your previous post, missed the last bit.)
    this wont help you if you cant be there when it runs.
    Last edited by redocs; 07-23-2013 at 09:07 AM. Reason: did not read :)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Search function in Excel with direct link to documents on server
    By ZJoMetInga in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-11-2013, 05:56 AM
  2. [SOLVED] Can I convert linked into embedded documents in Excel 2003?
    By Karly Harly in forum Excel General
    Replies: 0
    Last Post: 05-11-2006, 12:40 AM
  3. excel documents won't open from my documents folder
    By Paul1961 in forum Excel General
    Replies: 2
    Last Post: 01-15-2006, 01:35 PM
  4. [SOLVED] Record Excel 2003 Macro to link two documents
    By Dajana in forum Excel General
    Replies: 1
    Last Post: 08-22-2005, 10:05 PM
  5. Replies: 4
    Last Post: 07-24-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1