Open save and close Excel files 3

[Delano29]

Hi,

Is there a way to open, save and then close Excel files using a macro or VB? I have several linked files located on the network in a single folder and the process of updating the links takes quite a long time. I would like to automate this as much as possible.

While having the main Excel file open I would like each of the linked Excel file located in the folder to open, update links, save and then close the file and then move on to the next Excel file located in the same folder and repeat the process until all files are completed. In addition, after each file is successfully processed to keep a log maybe in a txt file to verify each files successfully updating, saving and closing.

I need help getting started with the coding.

Thanks

 

[VulcanJedi]

Use the macro recorder and you'll see....

Tranpkp

 

[SkipVought]

You must have the SAME workbook open and running the code during the entire process.

From that workbook, you can open, save and close OTHER workbooks. You can SAVE and CLOSE that workbook at the end of the process.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Delano29]

Guys, those ideas never even occurred to me. Thanks. I'll try them.

 

[ck1999]

try this code I have used a modified version in the past

    Dim file(800)
    Dim vsheetname
    Dim vdirectory
    
    vsheetname = ActiveSheet.Name
    a = -1
    dingy = Dir("\\server\network\enviroserve\blendsheet\blend2\*.xls")
      Do While dingy <> ""
         a = a + 1
         file(a) = dingy
         dingy = Dir
    Loop
     c = a
     Dim i
     For a = 0 To (c - 1)
        For b = (a + 1) To c
           If file(a) > file(b) Then i = file(a): file(a) = file(b): file(b) = i
        Next b
    Next a
    vdirectory = "c:\newfolder"
    For a = 0 To c
        Workbooks.Open Filename:=vdirectoy & file(a)
              
              ' your code here
          
        Windows(file(a)).Activate
        ActiveWorkbook.Close savechanges = True
    Next a
    Windows(vsheetname).Activate

I put this in an Xla file "personal.xla" as an addin so it is not tied to an actual workbook

 

[Delano29]

Thanks ck1999.

 

[Gavona]

A few further ideas you may be able to adapt to your purpose:

each of the linked Excel file

You can also loop through all the linked workbooks and open them. This routine opens all linked workbooks

Sub OpenAllLinks()
    Dim arLinks As Variant
    Dim intIndex As Integer
    Dim MyWbk As Workbook
    Set MyWbk = ActiveWorkbook
    arLinks = MyWbk.LinkSources(xlExcelLinks)
   
    If Not IsEmpty(arLinks) Then
        For intIndex = LBound(arLinks) To UBound(arLinks)
            MyWbk.OpenLinks arLinks(intIndex)
 '           xxxxxx
        Next intIndex
    Else
        MsgBox "The active workbook contains no external links."
    End If
    MyWbk.Activate
End Sub

to keep a log maybe in a txt file to verify each files successfully updating, saving and closing

This routine runs a series of sub-routines. Inserted into each routine at suitable points (eg within loops) are calls to my StatusMonitor routine. This routine:
1. Displays progress in the status bar, including time elapsed
2. Records the same information in a sheet in the workbook. This sheet has a single-cell named range "Progress" which is used to define where the log information should be stored.

So in the preceding code in place of xxx I might add:
Statusmonitor(MyWbk.OpenLinks arLinks(intIndex).name + " Opened")

Sub Mysub()
Start = Timer
ProgressRow = 1
StatusMonitor ("Hello! Starting to do my stuff!")
Call SubRoutine1
Call SubRoutine2
Call ResetStatusBar
Finish = Timer
TotalTime = Finish - Start
MsgBox "Routine took " & TotalTime / 60 & "  minutes"
End Sub

Sub StatusMonitor(StatusText)
'Displays text in status bar and records progress in progress sheet
Application.StatusBar = StatusText & "        " & Round((Timer - Start) / 60, 2) & "  minutes so far"
ProgressRow = ProgressRow + 1
Range("Progress").Offset(ProgressRow, 0) = Application.StatusBar
Range("Progress").Offset(ProgressRow, 1) = Round((Timer - Start) / 60, 2)
End Sub

Sub ResetStatusBar()
Application.StatusBar = ""
Application.StatusBar = False
Application.DisplayStatusBar = True
End Sub

Gavin