Making a macro run automatically and update data in a sheet (not create a new one)

[michaelhill94]

Hi! I'm new to all this so sorry if this is a daft question. I have a macro that can be used to show the date various word files are updated. However the only way I can get it to work is by rerunning the macro which then places all the updated information a new sheet. Is there anyway that I can make it so that the macro runs as soon as I open the sheet and so that it always updates to the same place (and therefore doesn't create a new sheet)?

[Solus Rankin]

Yes there is probably a way. It might be easier if you attached what you have so far to be edited.

[michaelhill94]

Public Sub GetFileInfo()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim wksLog As Excel.Worksheet
Dim lngRow As Long: lngRow = 1

Const strPath As String = "C:\Users\User\Dropbox\TSCat GEN\Pipeline\Projects and Project Ideas\Project Overviews"
Const strFileExtension As String = "*.doc*"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder(strPath)
For Each objFile In objFolder.Files
With objFile
If .Name Like strFileExtension Then
If wksLog Is Nothing Then
Set wksLog = ThisWorkbook.Sheets.Add
wksLog.Range("A1:C1").Value = VBA.Array("File name", "Date created", "Date last modified")
End If
lngRow = lngRow + 1
wksLog.Cells(lngRow, "A").Value = .Name
wksLog.Cells(lngRow, "B").Value = .DateCreated
wksLog.Cells(lngRow, "C").Value = .DateLastModified
End If
End With
Next objFile
End Sub

[michaelhill94]

That's what I've got so far.

[Solus Rankin]

Try changing this:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Also to get it to run automatically you'll have to copy everything (Except the very first and very last lines) into the workbook_open event

[michaelhill94]

I've done the first thing you recommend and it worked a treat. Thank you. However as I know so little the second recommendation means nothing to me. What is the 'workbook_open event.' I'm completely new to all this and will need quite a bit of detail.

[Solus Rankin]

Capture.jpg


See attached picture. I've highlighted the areas you need to change. Double click Workbook (highlighted in box to left) and then change the boxes highlighted across the top to their respective values.

Please Login or Register  to view this content.

Will populate automatically. This is why its important to delete the first and last lines of the code you have currently. Paste the rest in between Sub and end sub.

[michaelhill94]

I've tried making mine look the same as yours but it still doesn't seem to update automatically. Here is the code as I have it
Private Sub Workbook_Open()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim wksLog As Excel.Worksheet
Dim lngRow As Long: lngRow = 1

Const strPath As String = "C:\Users\User\Dropbox\TSCat GEN\Pipeline\Projects and Project Ideas\Project Overviews"
Const strFileExtension As String = "*.doc*"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder(strPath)
For Each objFile In objFolder.Files
With objFile
If .Name Like strFileExtension Then
If wksLog Is Nothing Then
Set wksLog = ActiveSheet
wksLog.Range("A1:C1").Value = VBA.Array("File name", "Date created", "Date last modified")
End If
lngRow = lngRow + 1
wksLog.Cells(lngRow, "A").Value = .Name
wksLog.Cells(lngRow, "B").Value = .DateCreated
wksLog.Cells(lngRow, "C").Value = .DateLastModified
End If
End With
Next objFile
End Sub

[Solus Rankin]

Michael,

When post code, please press the # button above and paste the code in between [code] and [\code].

Are you getting an error? Have the files changed so that there is actually anything to update?

[michaelhill94]

It works! Thank you very much!

[Solus Rankin]

No problem.

If you would, please take the time mark the thread as [SOLVED] and add reputation by clicking the * below.

Thanks,