Alternative to Sendkeys when manipulating a file in Notepad?

**Packerbacker** · 06-25-2013, 02:40 PM

I must create a process that FTPs a CSV file. The table receiving the file has been structured so that it rejects all files with blank rows (this is out of my control). Saving Excel files as .csv retains a blank row I cannot access or delete in any MS Office application. I can only view and delete this blank row in Notepad.

I have altered the proposed process so that:
Saves the file as .csv
Opens the file in Notepad
strikes "CTRL + END" via the sendkeys function to move the cursor to the end of the file
strikes "BACKSPACE" via sendkeys to delete the final row
strikes "CTRL + S" via sendkeys to save the file
strikes "ALT + F4" via sendkeys to close Notepad

The process then resumes as normal to FTP the file.

I have two questions.

Can you think of any reason why this method is unreliable or volatile?

Is there a better way to delete that final hidden row?

Thanks

**patel45** · 06-25-2013, 02:48 PM

you can export as csv without using save as with a code like this

Sub ExportToTextGeneral()
Dim fs As Object
Set Zona = Worksheets("Registro").UsedRange
Set fs = CreateObject("Scripting.FileSystemObject")
Fname = "C:\users\andre\desktop\prova.txt"
FnameExists = fs.FileExists(Fname)
If FnameExists = False Then
  FileNum = FreeFile()
  Open Fname For Output As #FileNum
  If Err <> 0 Then
    MsgBox "I can not open " & Fname
    Exit Sub
  End If
  sep = ","
  delim = """"
  For RowCount = 1 To Zona.Rows.Count
    For ColumnCount = 1 To Zona.Columns.Count
      If ColumnCount = Zona.Columns.Count - 1 Then
      Print #FileNum, delim & Zona.Cells(RowCount, ColumnCount).Text & delim;
      ElseIf ColumnCount = Zona.Columns.Count Then
        Print #FileNum,
      Else
        Print #FileNum, delim & Zona.Cells(RowCount, ColumnCount).Text & delim & sep;
      
      End If
    Next ColumnCount
  Next RowCount
  Close #FileNum
End If
End Sub

**Packerbacker** · 06-27-2013, 09:43 AM

Thanks for you help on this. I'm having some difficulty tailoring your code to my needs. The number of records and ranges will change each month. To further complication the process the range copied is rarely contiguous.

Here's what I currently have that creates the .csv file:

Sub ExportCSV(strPath As String, strExtractFile As String)
'Calculate workbook to ensure timestamp is updated
Application.CalculateFullRebuild

'Create new workbook, assign variables
Dim wbkExport As Workbook 'destination file for data to export
Set wbkExport = Workbooks.Add
Dim shtExport As Worksheet 'destination worksheet for data to export
Set shtExport = wbkExport.Sheets(1)

'Copy data from output tab to new worksheet
Dim lngValnDtCnt As Long 'valuation date count
lngValnDtCnt = shtAdmin.Range("ValnDtCnt").Value
'IP estimates
shtOutput.Range("A2:G" & lngValnDtCnt - 11).Copy
shtExport.Range("B2:H" & lngValnDtCnt - 11).PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
'OP estimates
shtOutput.Range("A38:G" & lngValnDtCnt + 25).Copy
shtExport.Range("B" & (lngValnDtCnt - 10) & ":H" & ((2 * lngValnDtCnt) - 23)).PasteSpecial _
    xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
'PHYS estimates
shtOutput.Range("A74:G" & lngValnDtCnt + 61).Copy
shtExport.Range("B" & ((2 * lngValnDtCnt) - 22) & ":H" & ((3 * lngValnDtCnt) - 35)).PasteSpecial _
    xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
'RX estimates
shtOutput.Range("A110:G" & lngValnDtCnt + 97).Copy
shtExport.Range("B" & ((3 * lngValnDtCnt) - 34) & ":H" & ((4 * lngValnDtCnt) - 47)).PasteSpecial _
    xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

'Create header record in new worksheet
shtExport.Range("A1").Value = strExtractFile
'shtExport.Range("B1").Value = Format(shtAdmin.Range("ValnDt"), "yyyymmdd")
shtExport.Range("B1").Value = Format(Now(), "yyyymmdd")
shtExport.Range("C1").Value = (4 * lngValnDtCnt) - 48
shtExport.Range("D1").Value = Format(Application.Sum(shtOutput.Range("E2:E145")), "Fixed")
shtExport.Range("D1").NumberFormat = "0.00"
shtExport.Range("E1").Value = Format(Application.Sum(shtOutput.Range("F2:F145")), "Fixed")
shtExport.Range("E1").NumberFormat = "0.00"
'Save CSV file
wbkExport.SaveAs Filename:=strPath & strExtractFile, FileFormat:=xlCSV
wbkExport.Close saveChanges:=False
End Sub