Hey fellas,
I've got a macro that grabs the data from the first table in every .docm file in a network folder, and then stitches all the data together into one table. It works fine, the problem I'm having is in determining in what order the files in this folder are opened. At the moment it seems to be random, though I know this cannot be the case. The files are named to reflect their position in the merged document (i.e. "A) Health and Safety", "B) Environment", etc...). I would think it would open them in alphabetical sequence (A, B, C, and so on), but it does not. The current order is (D, C, A, B, F, K, B, E, J ,G, L, C, I, H, C, B) keeping in mind that there are three B files and three C files whos inside order does not matter. Code follows:
Code:
Sub GetJournalData()
'///////////////////////////////////////////////////////////////////////////////////
'/////This function stitches together the data from the first table in all .docm////
'///////////////////////files in the listed directory.//////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////
Dim Target As Word.Document
Dim Source As Word.Document
Dim RowTarget As Row
Dim RowCount As Integer
Dim iCounter As Integer
Dim Folder As String
Dim File As String
'Set current document as Source file
Set Source = ActiveDocument
'Clear table of previous data, if it exists
Do While Source.Tables(1).Rows.Count > 2
Source.Tables(1).Rows.Last.Delete
Loop
'Set target folder
Folder = "\\SharePoint@Port\teamsites\subsite\subsite\site\library\"
'Find any and all .docm files in target folder
File = Dir(Folder & "\*.docm")
'Loop while there are still files to process
Do While File <> ""
'Open target and set Target variable
Documents.Open FileName:=Folder & File
Set Target = ActiveDocument
RowCount = Target.Tables(1).Rows.Count
For iCounter = 3 To RowCount
'Add row and set elements
Set RowTarget = Source.Tables(1).Rows.Add
RowTarget.Cells(1).Range.Text = Target.Tables(1).Rows(iCounter).Cells(1).Range.Text
RowTarget.Cells(2).Range.Text = Target.Tables(1).Rows(iCounter).Cells(2).Range.Text
RowTarget.Cells(3).Range.Text = Target.Name
RowTarget.Cells(4).Range.Text = Target.BuiltInDocumentProperties("Last Save Time")
Next iCounter
'Close target file
Target.Close savechanges:=False
File = Dir
Loop
'//////////////////END//////////////////////////////////////////////////////////
End Sub
The apparent order does not appear to conincide with any property of the file (created date/time, last modified date/time, size, etc..). Any ideas, Word VBA community?