0

I have an Excel sheet that gets data from another closed Excel sheet. I managed to get the code working as follows when placed in 'ThisWorkbook' in my 'VBAProject':

Option Explicit

  Private Sub Workbook_Open()
    Call ReadDataFromCloseFile
  End Sub

  Sub ReadDataFromCloseFile()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

  Dim src As Workbook

  ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
  Set src = Workbooks.Open("Source file path", True, True)

  ' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.
  Dim iTotalRows As Integer
  iTotalRows = src.Worksheets("data entry").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Rows.Count

  ' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION WORKBOOK.
  Dim iCnt As Integer         ' COUNTER.
  For iCnt = 1 To iTotalRows
    Worksheets("display").Range("A1:W1" & iCnt).Formula = src.Worksheets("data entry").Range("A1:W1" & iCnt).Formula
  Next iCnt

  ' CLOSE THE SOURCE FILE.
  src.Close False             ' FALSE - DON'T SAVE THE SOURCE FILE.
  Set src = Nothing

  ErrHandler:
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

Previously I have a button to trigger the code. Not only that it can auto update when I first open the file. Now I would like to add a timer function to my code so that it can refresh the data frequently and automatically.

So I added a timer to my Workbook_Open function as follows:

Private Sub Workbook_Open()
  Application.OnTime Now + TimeValue("00:01:00"), "ReadDataFromCloseFile"
End Sub

However when I let the timer run I receive the message saying I cannot run the macro because it may not be available in workbook or all macros is disabled.

Clearly my timer has issues, but I can't figure out why. I even placed the time into the main sub but still no dice. Any suggestions?

Community
  • 1
  • 1
hjh93
  • 570
  • 11
  • 27
  • Take a look here: https://stackoverflow.com/questions/2319683/vba-macro-on-timer-style-to-run-code-every-set-number-of-seconds-i-e-120-secon – Variatus Nov 24 '17 at 02:32
  • 1
    Possible duplicate of [VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds](https://stackoverflow.com/questions/2319683/vba-macro-on-timer-style-to-run-code-every-set-number-of-seconds-i-e-120-secon) – L42 Nov 24 '17 at 05:58
  • You just have to move 'Sub ReadDataFromCloseFile()' into a vba module: add a new module in the vba project explorer and move your subroutine there. 'The Private Sub Workbook_Open()' stays where it is. Let me know if this solves your problem. – Hubisan Nov 24 '17 at 11:39
  • Tried it. Now the timer doesn't trigger, the data-entry file will auto open as read only when I open my display and I still get the error message if I try to trigger it manually. – hjh93 Nov 27 '17 at 00:28
  • Just tried your code with one little change 'Worksheets("display")' to 'thisWorkbook.Worksheets("display")' to be sure it's referring to the right workbook. Moved the ReadDataFromCloseFile into a module (VBAProject > Module > Modul1), Private Sub Workbook_Open() stays inside VBAProject > thisWorkbook (see comment). Works as it is supposed to. – Hubisan Nov 27 '17 at 14:14

0 Answers0