• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

convert excel file to text file [ SOLVED ]

prabirmustafi

prabirmustafi

New Member
I have the following vba code, it is not working. though it converts the excel to text but not the way i need.

Essentially i am trying

  1. First Copy the selected sheet from the workbook and create a new sheet with the selected as workbook
  2. copy and value paste all the cells starting from cell A1 to AG3000
  3. remove first 7 rows
  4. column A refers to category and i want to keep on IN HOUSE rows
  5. keep columns C,D,G and O
  6. rename the column C as modelname
  7. save it as text file in desktop
please let where i am going wrong

Code: 
Sub ExportDataToTextFile()
    Dim newWB As Workbook
    Dim wsCopy As Worksheet
    Dim LastRow As Long, LastCol As Long
    Dim i As Long
    Dim keepColumns As Variant
 
    ' Columns to keep
    keepColumns = Array("C", "D", "G", "O")
 
    ' Create a new workbook and copy the worksheet to it
    ThisWorkbook.Sheets("SomeSheetOne").Copy
    Set newWB = ActiveWorkbook
    Set wsCopy = newWB.Sheets(1)
 
    ' Paste values to remove formulas
    With wsCopy
        .UsedRange.Value = .UsedRange.Value
        LastRow = 3010 ' Assuming Last Row is fixed at 3010
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
 
    ' Remove rows where Column A is not "IN HOUSE"
    For i = LastRow To 9 Step -1
        If wsCopy.Cells(i, 1).Value <> "IN HOUSE" Then
            wsCopy.Rows(i).Delete
        End If
    Next i
 
    ' Delete columns not in keepColumns array
    For i = LastCol To 1 Step -1
        If Not IsError(Application.Match(wsCopy.Cells(1, i).Value, keepColumns, 0)) Then
            ' Column header found in keepColumns array, do nothing
        Else
            wsCopy.Columns(i).Delete
        End If
    Next i
 
    ' Remove duplicates based on all columns
    With wsCopy
        .Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlYes
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
 
    ' Change header of column A to "Model Name"
    wsCopy.Cells(1, 1).Value = "Model Name"
 
    ' Save the workbook as a text file on the desktop
    newWB.SaveAs Filename:="C:\Users\prabir\Desktop\TESTONE.txt", FileFormat:=xlText, CreateBackup:=False
 
    ' Close the workbook without saving changes
    newWB.Close SaveChanges:=False
End Sub
 
Last edited by a moderator:

prabirmustafi​

Have You tried to do it manually (those 7 steps)?
Do it works then?
Have You tried to record a macro with those 7 steps?
... What is different than You're needing?
If You would like to ask about VBA ... then this thread should be in VBA Macros ( moved there ).
Could You send a sample Excel-file (source) and expected correct txt-file.
 
yes i have tried to do manually those steps, it works then
yes i have tried using macros by recording it works when it is recorded, but doesn't works when try to run the macro
i dont know how to move to vba macros Moderator Note ( As written moved there )
i will try to add a sample file
 
@vletm here is sample, i have removed the formulas and macros. there are other sheets as well in the original which i have removed for the sample
 

Attachments

  • TEST.xlsm
    88.5 KB · Views: 3
yes provided all the conditions it follows

  1. First Copy the selected sheet from the workbook and create a new sheet with the selected as workbook
  2. copy and value paste all the cells starting from cell A1 to AG3000
  3. remove first 7 rows
  4. column A refers to category and i want to keep on IN HOUSE rows
  5. keep columns C,D,G and O
  6. rename the column C as modelname
  7. save it as text file in desktop
 

prabirmustafi​

I waited for a sample expected correct txt-file ....

Here one sample (.xlsb-file ) to get txt-file (.txt-file)
... You gotta modify needed SaveAs line there.
 

Attachments

  • textfile.txt
    68 bytes · Views: 4
  • totxt.xlsb
    19.5 KB · Views: 3
Hi sorry for the delay, it worked a treat. really appreciate for the amazing help. only one question. how can refer it to specific sheet instead of using active sheet . i had this one
ThisWorkbook.Sheets("SomeSheetOne").Copy
Set newWB = ActiveWorkbook where can i add this?
 

prabirmustafi​

Why do You need to copy that sheet?
Copy needed range as in my sample.
Compare to this sample.
From where do You have idea to run that my code?
If in other file than that ... sheet is then ... something different.
 

Attachments

  • totxt.xlsb
    19.5 KB · Views: 4
i dont know anything about excel. starting to learn excel as needed in work. i was searching on google on how to use vba. i wanted to use the specific sheet because there are 4 other sheets. and the file one who created has left the job. it is given to me.
 
Did You test my file?
Did You check that my code?
That uses only Your named SomeSheetOne-sheet.
... of course, before testing something - have a backup!
 
okay i just saw the code is added in the module. but saw yours is added in the sheet module. sorry i am very new to this vba. trying to learn by searching on google and copilot.
 
I use normally with sheet-module.
It's possible to search many thing from Google, but that other , which name I skip ... I recommend to forget.
 
As a reminder when coding into the worksheet module then such With Sheets("SomeSheetOne") codeline is useless​
as it can be replaced with this codeline With Me but still useless as every object without any worksheet reference​
belongs automatically to the worksheet where the code is located …​
​
According to post #4 attachment a Windows only starter VBA demonstration to paste only to the Blad worksheet module :​
Code: 
Sub Demo1()
        Const S = "IN HOUSE"
   With Application
       .ScreenUpdating = False
       .DisplayAlerts = False
        [AV8:AV9] = Evaluate("{""" & [A8] & """;""" & S & """}")
        Workbooks.Add xlWBATWorksheet
        ActiveSheet.[A1:D1] = .Index([C8:O8], 1, [{1,2,5,13}])
        Range("A8", Cells(Rows.Count, 15).End(xlUp)).AdvancedFilter 2, [AV8:AV9], ActiveSheet.[A1:D1], True
        [AV8:AV9].Clear
        ActiveSheet.[A1] = "Model Name"
        ActiveSheet.UsedRange.NumberFormat = "General"
        ActiveWorkbook.SaveAs CreateObject("WScript.Shell").SpecialFolders("Desktop") & .PathSeparator & S & " .txt", 20
        ActiveWorkbook.Close
       .DisplayAlerts = True
       .ScreenUpdating = True
   End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 

prabirmustafi​

You already gave an image of solved with #16 reply.
It's possible to post one more posting in which You could express that again.
I add [ SOLVED ] in the end of title ( done ) ... this part is solved now.
 
You must log in or register to reply here.
Back
Top