Link to home
Start Free TrialLog in
Avatar of SunnyX
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
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

Open in new window


Please take at the attachment
ABC-5182015--120500-PM.txt
testing.txt
For-forum.xlsx
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Try this amendment to the previous code

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

Open in new window

Avatar of SunnyX
SunnyX

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SunnyX

ASKER

thx u so much !
Glad it worked