[RESOLVED] Excel macro - help with loop / text file export

[palantir]

I've tried to adapt an existing macro that converts excel to a txt file (fixedwidth). I want (as you can se below) to add a new variable, NumEntry, which adds an outer loop to the existing loop.

The debug facility highlight the line "For MyRow = FirstRow To LastRow " though. I also moved the Open PageName For Output As #1 line too (probably to the wrong place, but in its original place it kept on saying a file was already open)

I'm a noob but want to get this done for someone as it will save them sooo much time and I owe them

Sub MakeFixedWidth()
Dim MyStr As String, PageName As String, FirstRow As Integer, LastRow As Integer, MyRow As Integer, NumEntry As Integer

PageName = "C:\Modding\EDU_Unit" & Format(Time, "HHMM") & ".txt" ' location and name of saved file

Open PageName For Output As #1
For NumEntry = 1 To Range("B4").Value
FirstRow = ((NumEntry * 25) - 25) + 10 ' gets first row of each edu entry per loop
LastRow = FirstRow + 23 ' adds 23 rows to include all of entry
 For MyRow = FirstRow To LastRow ' loop through each row of the table
 MyStr = ""
 MyStr = Cells(MyRow, 1).Value & String(17 - Len(Cells(MyRow, 1).Value), " ") ' type + spacer
 MyStr = MyStr & Cells(MyRow, 2).Value ' adds type entry
 ' MyStr = MyStr & " " & Cells(MyRow, 3).Value & String(20 - Len(Cells(MyRow, 3).Value), " ")
 ' MyStr = MyStr & Cells(MyRow, 4).Value & String(15 - Len(Cells(MyRow, 4).Value), " ")
 ' MyStr = MyStr & Cells(MyRow, 5).Value & String(13 - Len(Cells(MyRow, 5).Value), " ")
 ' MyStr = MyStr & Cells(MyRow, 6).Value & String(25 - Len(Cells(MyRow, 6).Value), " ")
 ' MyStr = MyStr & Format(Cells(MyRow, 7).Value, "0000000.00")
 Print #1, MyStr
 Next MyRow
Next NumEntry
Close #1
Sheets("DATA").Range("G2").ClearContents ' note that this row expects the worksheet to be named DATA
Sheets("DATA").Hyperlinks.Add Range("G2"), PageName
End Sub

I've attached the excel should anyone want to know more, and an example of what the txt file *should* look like when generated.

VBA 6.3

[westconn1]

here is some basic code to do some of what you want, leaves you to find your start and end rows etc, also it puts the same number of fields into every line, even if they are empty

myarr = Range("b10:m83")
tmparr = Range("a10:a83")
For i = 1 To UBound(myarr, 1)
    tmpstr = Space(17)
    LSet tmpstr = tmparr(i, 1)
    myarr(i, 1) = tmpstr & myarr(i, 1)
Next
Open "c:\temp\mytest.txt" For Output As 1
Print #1, join2(myarr, ",", vbNewLine)
Close 1

Function join2(myarr, chr1 As String, chr2 As String) As String
      Dim mystr As String
      mystr = ""
      For i = LBound(myarr, 1) To UBound(myarr, 1)
          For j = LBound(myarr, 2) To UBound(myarr, 2) - 1
              mystr = mystr & CStr(myarr(i, j)) & chr1
          Next
          mystr = mystr & CStr(myarr(i, j)) & chr2
      Next
      join2 = mystr
      End Function

sample output

type             irish monastic levy,,,,,,,,,,,
dictionary       irish_monastic_levy,,,,,,,,,,,
category         infantry,,,,,,,,,,,
class            spearmen,,,,,,,,,,,
voice_type       General_1,,,,,,,,,,,
soldier          irish_monastic_levy,55,0,0.42,,,,,,,,
mount_effect     horse +1,,,,,,,,,,,
attributes       sea_faring,hide_forest,is_peasant,,,,,,,,,
formation        1.3,1.4,2.6,2.8,4,square,,,,,,
stat_health      1,5,,,,,,,,,,
stat_pri         6,6,javelin,50,3,thrown,simple,piercing,spear,15,1,
stat_pri_attr    prec,,,,,,,,,,,
stat_sec         9,6,no,0,0,melee,simple,piercing,spear,25,0.41,
stat_sec_attr    light_spear,,,,,,,,,,,
stat_pri_armour  7,7,3,leather,,,,,,,,
stat_sec_armour  0,0,flesh,,,,,,,,,
stat_heat        2,,,,,,,,,,,
stat_ground      0,-1,0,0,,,,,,,,
stat_mental      10,low,untrained,,,,,,,,,
stat_charge_dist 30,,,,,,,,,,,
stat_fire_delay  0,,,,,,,,,,,
stat_food        60,300,,,,,,,,,,
stat_cost        1,422,158,155,193,1175,,,,,,
ownership        celts,slave,ostrogoths,,,,,,,,,
                 ,,,,,,,,,,,
type             monk,,,,,,,,,,,
dictionary       irish_monastic_levy,,,,,,,,,,,
category         infantry,,,,,,,,,,,
class            spearmen,,,,,,,,,,,
voice_type       General_1,,,,,,,,,,,
soldier          irish_monastic_levy,55,0,0.42,,,,,,,,
mount_effect     horse +1,,,,,,,,,,,
attributes       sea_faring,hide_forest,is_peasant,,,,,,,,,
formation        1.3,1.4,2.6,2.8,4,square,,,,,,
stat_health      1,5,,,,,,,,,,
stat_pri         6,6,javelin,50,3,thrown,simple,piercing,spear,15,1,
stat_pri_attr    prec,,,,,,,,,,,
stat_sec         9,6,no,0,0,melee,simple,piercing,spear,25,0.41,
stat_sec_attr    light_spear,,,,,,,,,,,
stat_pri_armour  7,7,3,leather,,,,,,,,
stat_sec_armour  0,0,flesh,,,,,,,,,
stat_heat        2,,,,,,,,,,,
stat_ground      0,-1,0,0,,,,,,,,
stat_mental      10,low,untrained,,,,,,,,,
stat_charge_dist 30,,,,,,,,,,,
stat_fire_delay  0,,,,,,,,,,,
stat_food        60,300,,,,,,,,,,
stat_cost        1,422,158,155,193,1175,,,,,,
ownership        celts,slave,ostrogoths,,,,,,,,,
                 ,,,,,,,,,,,
type             kern,,,,,,,,,,,
dictionary       irish_monastic_levy,,,,,,,,,,,
category         infantry,,,,,,,,,,,
class            spearmen,,,,,,,,,,,
voice_type       General_1,,,,,,,,,,,
soldier          irish_monastic_levy,55,0,0.42,,,,,,,,
mount_effect     horse +1,,,,,,,,,,,
attributes       sea_faring,hide_forest,is_peasant,,,,,,,,,
formation        1.3,1.4,2.6,2.8,4,square,,,,,,
stat_health      1,5,,,,,,,,,,
stat_pri         6,6,javelin,50,3,thrown,simple,piercing,spear,15,1,
stat_pri_attr    prec,,,,,,,,,,,
stat_sec         9,6,no,0,0,melee,simple,piercing,spear,25,0.41,
stat_sec_attr    light_spear,,,,,,,,,,,
stat_pri_armour  7,7,3,leather,,,,,,,,
stat_sec_armour  0,0,flesh,,,,,,,,,
stat_heat        2,,,,,,,,,,,
stat_ground      0,-1,0,0,,,,,,,,
stat_mental      10,low,untrained,,,,,,,,,
stat_charge_dist 30,,,,,,,,,,,
stat_fire_delay  0,,,,,,,,,,,
stat_food        60,300,,,,,,,,,,
stat_cost        1,422,158,155,193,1175,,,,,,
ownership        celts,slave,ostrogoths,,,,,,,,,

[palantir]

westconn1,

Wow - thank you very much. I shall look into trying this out as soon as I can

[palantir]

RESOLVED. Thank you!

[Hack]

RESOLVED. Thank you!

As a newer member I ask that if you consider your question to be resolved please help us out by pulling down the Thread Tools menu and clicking the Mark Thread Resolved menu item. That will let everyone know that you have your answer.