Hello dear Excel community.
First I want to thank the Excel forums for always being the first ones to send me an email for my Birthday. Usually I'm eagerly waiting for messages as soon as the clock hits 0:00, I tend to not receive any except for Excel community, so it's a great relief. Big thanks to whoever is taking the time to send me those messages.
Anyways, I have an excel macro which converts my sheets to TXT format when I press a special key combination. These TXT files are data used by my Application.
Sub SaveCopies()
'
' SaveCopies Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim MyDate
MyDate = Date
Dim MyTime
MyTime = Time
Dim StrTime As String
StrTime = Format(MyTime, "hh.mm.ss")
Dim StrDate As String
StrDate = Format(MyDate, "YYYY-MM-DD")
ExcelPath = "C:\App\Data\global\excel\"
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=ExcelPath & ActiveSheet.Name & ".txt", _
FileFormat:=xlText
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
However, when I have a Data->FILTER applied to my sheet, upon saving, Excel adds an extra blank line at the end of the TXT file. This causes my app to break in complete anger.
I am curious how can I detect if the last line is empty, and remove if it is, when generating the TXT file.
Very much appreciated. Thank you EXCEL COMMUNITY.
Bookmarks