Johniny

AutoRefresh from list

Mar 7th, 2020
467
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Dim configFileList As Variant 'Premenna pre cely modul
  2.  
  3.  
  4. Sub RefreshMultipleFiles()
  5.  
  6. Call GetFileList
  7.  
  8. Dim i As Integer
  9. i = 0
  10. Dim fileOZ As Workbook
  11. Dim folderOZ As String
  12. Dim targetSheet As Worksheet
  13. Dim targetRange As Range
  14.  
  15. folderOZ = (Application.ThisWorkbook.Path & "/Users/")
  16.  
  17. Do While (i < (UBound(configFileList) + 1) And Dir(folderOZ & configFileList(0)) <> "") 'https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ubound-function
  18.    Set fileOZ = Workbooks.Open(folderOZ & configFileList(i)) 'https://www.mrexcel.com/board/threads/vba-code-loop-through-named-list-or-all-xls-files-in-a-directory.845397/
  19.    Set targetSheet = fileOZ.Sheets("Sheet1")
  20.     Debug.Print ("OZ Filename[" & Format(Now, "hh:nn:ss") & "]: " & fileOZ.Name) 'Otvor si Immediate window (Ctrl+G) a uvidíš Debug.Print správy
  21.    Set targetRange = targetSheet.Range("M4") 'https://stackoverflow.com/questions/11568321/writing-a-string-to-a-cell-in-excel
  22.        targetRange.Value = 14
  23.     fileOZ.RefreshAll
  24.        
  25.     fileOZ.Close (False)
  26.     Debug.Print Format(Now, "hh:nn:ss") & " -> Success"
  27.     i = i + 1
  28. Loop
  29.  
  30. If Dir(folderOZ & configFileList(0)) = "" Then
  31.     MsgBox ("Chyba v zozname súborov!" & vbNewLine & "i = " & i), (vbOKOnly + vbCritical)
  32. ElseIf UBound(configFileList) = 0 Then
  33.     MsgBox ("Prázdny zoznam súborov"), (vbOKOnly + vbExclamation)
  34. Else
  35.     MsgBox ("Hotovo :)" & vbNewLine & vbNewLine & UBound(configFileList) + 1) & " súborov bolo aktualizovanych"
  36. End If
  37.  
  38. End Sub
  39.  
  40. Sub GetFileList()
  41.  
  42. Dim configFilePath As String
  43. Dim configWhole As String
  44. Dim configArray As Variant 'nebude to fungovat so String ťhttps://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
  45.  
  46. configFilePath = Application.ActiveWorkbook.Path & "\FileNameList.txt" 'Application.ActiveWorkbook.Path vráti cestu do zložky aktívneho workbooku
  47.    'alternativa Application.ThisWorkbook.Path vráti cestu ku zložke v ktorej je súbor s týmto makrom
  48. Open configFilePath For Input As #1 'netusim ako funguje Open+Input+Close :(, viac info na https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/line-inputstatement
  49. configWhole = Input$(LOF(1), 1) 'tiez neviem ako to funguje :(
  50. Close #1
  51. configFileList = Split(configWhole, vbNewLine) 'https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function
  52.  
  53. End Sub
RAW Paste Data