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.
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.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
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