#1
|
|||
|
|||
Convert excel file to text file using vba
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 HTML 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 |
#2
|
|||
|
|||
A simpler way:
1. Read your original table (or values from columns you want to transfer) into (starting from row from where to read data without headers) array variable; 2. Create an empty text file with a name you want on destination you want; 3. In For cycle (from 1st row of array to last one), read 1st array row and create a string form all array values in this array row, plus a code for next row at end of string; 4. Write the gotten string into text file; 5. Repeat the cycle of p3 and p4 until end of For cycle; 6. Save the text file. |
Tags |
excel 2021, vba |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Importing\convert a bin file to Excel | shabbaranks | Excel Programming | 1 | 07-10-2014 12:18 AM |
How do you convert a csv file to appear properly in Excel 2013? | DBlomgren | Excel | 3 | 05-22-2014 01:28 PM |
Convert excel file 2010(64 bit) to 2003 | Tyberian1988 | Excel Programming | 1 | 03-10-2014 11:39 PM |
A serious problem in integrating text file with an Excel file? | mradmin | Excel | 12 | 10-25-2013 07:43 AM |
How to convert a text file to an Excel file with the data format automatically? | mradmin | Excel | 6 | 10-16-2013 10:34 AM |