Copy sheet to text file with specific template

BKChedlia

New Member
Joined
Jun 15, 2016
Messages
41
I want to copy sheet to text file with specific template, I defined a range but the problem is my code begins in A3 instead of A2 and don't stop to LastRow so it bring empty rows
and skippes even numbers it means rows A2, A4,A6... not copied
Code:
Option Explicit

 Sub txtFile()
 
  Dim strPath As String
  Dim fso As Object
  Dim ts As Object
  
   Dim wsDest As Worksheet
   Set wsDest = Sheets("Filter FS")
   wsDest.Select
  Set fso = CreateObject("Scripting.FileSystemObject")
  
  Dim cellAimsID As Variant
  Dim cellAmount As Variant
  Dim cellCurrencyISO As Variant
  Dim cellReason As Variant
  Dim cellExpiryDate As Variant
  Dim FirstRow As String
  Dim LastRow As String
  
  Dim a As Range, b As Range, cell As String, rng As Range
  Set a = Selection
  Set ts = fso.CreateTextFile("C:\Users\cben\Documents\BKC\FinancialSecurity\test11.txt", True, True)
  ' for each cell in the worksheet create a line in text fil
 FirstRow = wsDest.UsedRange.Rows(1).Row
 LastRow = wsDest.Range("A" & Rows.Count).End(xlUp).Row
 Set rng = wsDest.Range("A2:A" & LastRow)
  For Each a In rng
cellAimsID = a.Cells(a.Row, 1)
cellAmount = a.Cells(a.Row, 2)
cellCurrencyISO = a.Cells(a.Row, 3)
cellReason = a.Cells(a.Row, 4)
cellExpiryDate = a.Cells(a.Row, 5)
  
'AimsID, Amount, Currency, Reason, ExpiryDate are the name of columns in worksheet
  ts.WriteLine (Chr(9) & "cases" & ": [")
  ts.WriteLine (Chr(9) & "{")
  ts.WriteLine (Chr(9) & "AimsID:" & cellAimsID & ",")
  ts.WriteLine (Chr(9) & "Amount:" & cellAmount & ",")
  ts.WriteLine (Chr(9) & "CurrencyISO:" & cellCurrencyISO & ",")
  ts.WriteLine (Chr(9) & "Reason:" & cellReason & ",")
  ts.WriteLine (Chr(9) & "ExpiryDate:" & cellExpiryDate & ",")
  ts.WriteLine (Chr(9) & "}" & ",")
 
  Next
 ts.Close
   
End Sub
this is the end result :
cases: [ {
AimsID:69210794,
Amount:EUR,
CurrencyISO:30000,
Reason:0,
ExpiryDate:30.09.2015,
},
cases: [
{
AimsID:86917526,
Amount:MXN,
CurrencyISO:,
Reason:1,
ExpiryDate:22.06.2016,
},
cases: [
{
AimsID:,
Amount:,
CurrencyISO:,
Reason:,
ExpiryDate:,
},
cases: [
{
AimsID:,
Amount:,
CurrencyISO:,
Reason:,
ExpiryDate:,
},
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top