Блог

Я создаю отчет в Excel, который должен обновляться ежемесячно. Поэтому я получаю каждый месяц новый Excelfile с данными, которые должны быть обобщены в отчете. Отчет состоит из вычислений конкретных Excelfiles с данными за каждый месяц, например, количество клиентов мужского или женского пола.

Возможно ли создать макрос с помощью VBA, который создает путь к новому Excelfile, чтобы мне не приходилось изменять путь к файлу вручную? В этом случае, например, вместо того, чтобы я должен изменить формулу на '...non_activated-2019-03' , введя ее, Excel должен сделать это автоматически, потому что существует более 60 таких вычислений, в которых мне пришлось бы изменить файл.

 =COUNTIFS('C:UsersdenniDesktopReportingNon Activated[non_activated-2019-02.xlsx]non_activated-2019-02'!$M:$M;$B$9;'C:UsersdenniDesktopReportingNon Activated[non_activated-2019-02.xlsx]non_activated-2019-02'!$B:$B;$C10)
  

Да, это возможно, и я делаю это сам, когда мне нужно создавать отчеты и файлы журналов. Просто добавьте следующее в свое имя файла String :

 filename = "...non_activated-" amp; Year(Date) amp; "-" amp; Month(Date)
  

Номер месяца с 0

Если вы хотите, чтобы номер месяца начинался с 0 , вы можете просто изменить код следующим образом:

 Sub yourSub()
   '...
   filename = "...non_activated-" amp; Year(Date) amp; "-" amp; getMonthNumber(Date)
   '...
End Sub

Function getMonthNumber(data As Date) As String
    If Month(data) < 10 Then
        getMonthNumber = "0" amp; Month(data)
    Else
        getMonthNumber = Month(data)
    End If
End Function
  

Откройте ваш файл

Существует множество способов открыть (и записать) ваш файл. Вы можете попробовать это:

 Open yourPath amp; yourFilename For Output As #1
Print #1, "Print somenthing on your file"
'Do your stuff
Close #1
  

Теперь ваш файл сохранен в вашем path.

Примечания

Используя этот метод, каждый месяц он автоматически создает новый файл, который вы можете легко найти.

Просто не забудьте проверить, существует ли файл, иначе он может вызвать исключение.

Вы могли бы использовать подобную функцию в ячейке на листе, затем ссылаться на ячейку в формулах, а затем оставить их в покое.

Например =GetLatestImportFile("C:workspacedummy data")

 Function GetLatestImportFile(strPath As String, _
        Optional strLookFor As String = "non-activated")

Dim f As Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fl As Scripting.File
Dim dt As Date

Set f = New Scripting.FileSystemObject
Set fld = f.GetFolder(strPath)

For Each fl In fld.Files

    If InStr(1, fl.Name, strLookFor) > 0 Then
        If fl.DateCreated > dt Then
            dt = fl.DateCreated
            GetLatestImportFile = fl.Name
        End If
    End If

Next fl

set f=nothing
set fld=nothing
set fl=nothing

End Function
  

Итак, на данный момент этот код работает довольно хорошо и заменяет старую ссылку на новую!

 Sub MySub()
   Dim old as String
   Dim new as String
   Dim i as Integer

   old = "activated-2019-01"
   new = "activated-2019-02"
   For i=4 to 160
      Cells(i,"E").FormulaLocal = Replace(Cells(i,"F").FormulaLocal, old, new)
   Next i
End Sub