1 Attachment(s)
[RESOLVED] Excel macro - help with loop / text file export
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 ;)
Code:
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
Re: Excel macro - help with loop / text file export
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
vb Code:
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
vb Code:
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
Code:
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,,,,,,,,,
Re: Excel macro - help with loop / text file export
westconn1,
Wow - thank you very much. I shall look into trying this out as soon as I can :thumb:
Re: Excel macro - help with loop / text file export
Re: Excel macro - help with loop / text file export
Quote:
Originally Posted by
palantir
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.
