I am sorting data in excel, then at the end I convert to a single column and then export it to a text file. Everything works except I cant get the text file to open automatically.
Can someone please help with the code below:
I tried using the #1 variable but it can't find that and if I put the full path then it works, but I want to keep it generic.
Can someone please help with the code below:
Sub Export2()
Dim r As Range, c As Range
Dim sTemp As String
'Declare constants for Desktop Location
Dim oWSS As Object
Const szlocation As String = "Desktop"
Dim szDesktopPath As String
' Get the desktop location
Set oWSS = CreateObject("WScript.Shell")
szDesktopPath = oWSS.SpecialFolders(szlocation)
' Open text file for output
Range("A1", Range("A1").End(xlDown)).Select
Open szDesktopPath & "\Fidelity.qif" For Output As #1
For Each r In Selection.Rows
sTemp = ""
For Each c In r.Cells
sTemp = sTemp & c.Text & Chr(9)
Next c
'Get rid of trailing tabs
While Right(sTemp, 1) = Chr(9)
sTemp = Left(sTemp, Len(sTemp) - 1)
Wend
Print #1, sTemp
Next r
Close #1
Shell "notepad.exe szDesktopPath & "\Fidelity.qif", vbNormalNoFocus
End Sub
I tried using the #1 variable but it can't find that and if I put the full path then it works, but I want to keep it generic.