Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving multiple Excel files (Excel 2000)

    I have a list of templates that I what to create workbooks from, save them to a common folder, and close them without displaying them. The code I am currently using is slow and I wonder if there might be a built in method in Excel that will perform this. Below is the sub routines and functions involved:

    Sub ProcessMultiDocs(xmlNodeList As MSXML2.IXMLDOMNodeList, saveFolder As String)
    Dim xmlNode As MSXML2.IXMLDOMNode

    Dim objExcelApp As Excel.Application

    For Each xmlNode In xmlNodeList
    ProcessOneDoc xmlNode.selectSingleNode("FileName").Text, saveFolder, True
    Next

    On Error Resume Next


    Set objExcelApp = GetObject(, "Excel.Application")
    If Not objExcelApp Is Nothing Then
    objExcelApp.Visible = True
    End If
    End Sub

    Sub ProcessOneDoc(fileName As String, saveFolder As String, bClose As Boolean)

    OpenExcelDoc UCase(fileName), saveFolder, bClose

    End Sub

    Function OpenExcelDoc(gFileToOpen As String, FolderToSave As String, bClose As Boolean)
    Dim objExcelApp As Excel.Application
    Dim strDocname As String

    strDocname = ParseFileName(gFileToOpen, ".XLT", FolderToSave, ".XLS")

    On Error Resume Next
    ' Try and establish link to existing open instance
    Set objExcelApp = GetObject(, "Excel.Application")

    ' If it fails then open an instance
    If objExcelApp Is Nothing Then
    Set objExcelApp = CreateObject("Excel.Application")
    End If


    objExcelApp.EnableEvents = True

    objExcelApp.Workbooks.Add (gFileToOpen)

    objExcelApp.EnableEvents = False
    objExcelApp.ActiveWorkbook.SaveAs strDocname
    objExcelApp.EnableEvents = True

    If bClose Then
    objExcelApp.ActiveWorkbook.Close
    Else
    LoadAddin objExcelApp, "EWPE2.xla"
    objExcelApp.Visible = True
    objExcelApp.ActiveWorkbook.Activate


    End If

    ' Clean up memory
    Set objExcelApp = Nothing

    End Function

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

    Re: Saving multiple Excel files (Excel 2000)

    You're using GetObject / CreateObject in each invocation of OpenExcelDoc. This is quite slow. I would define objExcelApp as a module-level (or global) variable. Use GetObject / CreateObject in ProcessMultiDocs to set this variable, loop the loop, and only then set objExcelApp to Nothing.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving multiple Excel files (Excel 2000)

    Ahhhh as soon as I read your first sentence I saw it too, I should have noticed what I was doing wrong, dumb, dumb, dumb. I guess I have been to close to it all and needed someone else's eyes. Thanks alot Hans!!!!!

    Jim

Posting Permissions

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