AW: Automatischer export am ende des Monats
14.02.2019 19:53:11
Sepp
Hallo Dennis,
füge den Code in ein allgemeines Modul ein, führe 'startExport' aus und dann warte bis 28.02.2019 23:59:59 ;-))
Modul Modul2
Option Explicit
Private nextRun As Date
Sub startExport()
nextRun = DateSerial(Year(Date), Month(Date) + 1, 0) + TimeSerial(23, 59, 59)
Call Application.OnTime(EarliestTime:=nextRun, Procedure:="exportHTM", Schedule:=True)
End Sub
Sub stopExport()
On Error Resume Next
Call Application.OnTime(nextRun, "exportHTM", Schedule:=False)
End Sub
Sub exportHTM()
Dim TargetRange As Range, strFileName As String
strFileName = "D:\Downloads\Forum\" & Format(Date, "yyyymmdd") & "_export.htm" 'Dateiname inkl. Pfad! - Anpassen!
With Sheets("Tabelle1").Range("A1:D10") 'Tabelle und Zellbereich - Anpassen!
.Parent.Parent.PublishObjects.Add(SourceType:=xlSourceRange, FILENAME:=strFileName, _
Sheet:=.Parent.Name, Source:=.Address, HtmlType:=xlHtmlStatic).Publish True
End With
Application.Wait TimeSerial(0, 0, 2)
Call startExport
End Sub
VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media
Code erstellt und getestet in Office 16 - mit VBAHTML 12.6.0
Modul Modul2
Option Explicit
Private nextRun As Date
Sub startExport()
nextRun = DateSerial(Year(Date), Month(Date) + 1, 0) + TimeSerial(23, 59, 59)
Call Application.OnTime(EarliestTime:=nextRun, Procedure:="exportHTM", Schedule:=True)
End Sub
Sub stopExport()
On Error Resume Next
Call Application.OnTime(nextRun, "exportHTM", Schedule:=False)
End Sub
Sub exportHTM()
Dim TargetRange As Range, strFileName As String
strFileName = "D:\Downloads\Forum\" & Format(Date, "yyyymmdd") & "_export.htm" 'Dateiname inkl. Pfad! - Anpassen!
With Sheets("Tabelle1").Range("A1:D10") 'Tabelle und Zellbereich - Anpassen!
.Parent.Parent.PublishObjects.Add(SourceType:=xlSourceRange, FILENAME:=strFileName, _
Sheet:=.Parent.Name, Source:=.Address, HtmlType:=xlHtmlStatic).Publish True
End With
Application.Wait TimeSerial(0, 0, 2)
Call startExport
End Sub
VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media
Code erstellt und getestet in Office 16 - mit VBAHTML 12.6.0