SunnyX
asked on
Excel VBA. Code improving : Create txt file with specific name
Let say there is a code that copy - past data from sheet to txt file. Now the name of the txt file always "testing". I need to improve this code in order that txt file that is creating will have name that consist from :
1) the info in cell C8 sheet "parameters " and 2) second part of the name is located in sheet "data " in the cell G8
Please take at the attachment
ABC-5182015--120500-PM.txt
testing.txt
For-forum.xlsx
1) the info in cell C8 sheet "parameters " and 2) second part of the name is located in sheet "data " in the cell G8
Sub test()
Dim TempSht As Worksheet
Dim rRng As Range
Dim sFullPath As String
sFullPath = ThisWorkbook.Path & Application.PathSeparator
With Sheet1
Set rRng = .Range(.Cells(8, 2), .Cells(.Rows.Count, 7).End(xlUp))
End With
Set TempSht = Sheets.Add
rRng.Copy TempSht.Range("A1")
TempSht.Copy
ActiveWorkbook.SaveAs Filename:=sFullPath & "testing.txt", FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = False
TempSht.Delete
ActiveWorkbook.Close True
Application.DisplayAlerts = True
End Sub
Please take at the attachment
ABC-5182015--120500-PM.txt
testing.txt
For-forum.xlsx
ASKER
thx for your reply but
the code doesn't work properly. The code gives error on line 9 ( highlighting => .txt )
Therefore I change from .txt to ".txt"
and the code still doesn't work properly.
It doesn't delete temporary sheet list and doesn't make txt file
the code doesn't work properly. The code gives error on line 9 ( highlighting => .txt )
Therefore I change from .txt to ".txt"
and the code still doesn't work properly.
It doesn't delete temporary sheet list and doesn't make txt file
I missed the speech marks around.txt, but the code does work when they are added,
Does the workbook actually contain those two sheets mentioned?
What text is in the two cells? It may be that the cells contain characters not allowed in file names.
Does the workbook actually contain those two sheets mentioned?
What text is in the two cells? It may be that the cells contain characters not allowed in file names.
Option Explicit
Sub ConvertToTabDelimited()
Dim TempSht As Worksheet
Dim rRng As Range
Dim sFullPath As String, sName As String
sName = Sheets("parameters").Range("C8") & Sheets("data").Range("G8") & ".txt"
sFullPath = ThisWorkbook.Path & Application.PathSeparator & sName
With Application
.ScreenUpdating = False
On Error GoTo err_quit
With Sheet1
Set rRng = .Range(.Cells(8, 2), .Cells(.Rows.Count, 7).End(xlUp))
End With
Set TempSht = Sheets.Add
rRng.Copy TempSht.Range("A1")
TempSht.Copy
ActiveWorkbook.SaveAs Filename:=sFullPath, FileFormat:=xlText, CreateBackup:=False
.DisplayAlerts = False
TempSht.Delete
ActiveWorkbook.Close True
.DisplayAlerts = True
err_quit:
.ScreenUpdating = True
End With
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thx u so much !
Glad it worked
Open in new window