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
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:,
},
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
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: