Trying to simplify .txt file creation macro

[localfiend]

After a bit of searching around on the web I was able to get some code working that creates a .txt file from cell values.

However the text file I need to create involves a whole lot of cells, and using the quick and dirty code below tends to make excel puke itself.

Can anyone show me how to simplify the "Print #iFileNum" section? It's abbvreviated for the moment and only shows code for rows 2 and 3. I need to to repeat that section of code all the way down to row 600. I can normally do something like this, but the freefile function being in there has me scratching my head.

Thanks,

' Declare stuff
Dim sFile As String
Dim sLine As String
Dim iFileNum As Integer

' Text File Location
sFile = "C:\temp\test.txt"

iFileNum = FreeFile

Open sFile For Output As iFileNum

' Make the Text File

With Worksheets("httpd")

    Print #iFileNum, .Range("A2").Value
    Print #iFileNum, .Range("B2").Value
    Print #iFileNum, .Range("C2").Value
    Print #iFileNum, .Range("D2").Value
    Print #iFileNum, .Range("E2").Value
    Print #iFileNum, .Range("F2").Value
    Print #iFileNum, .Range("G2").Value
    Print #iFileNum, .Range("H2").Value
    Print #iFileNum, .Range("I2").Value
    Print #iFileNum, .Range("J2").Value
    Print #iFileNum, .Range("K2").Value
    Print #iFileNum, .Range("A3").Value
    Print #iFileNum, .Range("B3").Value
    Print #iFileNum, .Range("C3").Value
    Print #iFileNum, .Range("D3").Value
    Print #iFileNum, .Range("E3").Value
    Print #iFileNum, .Range("F3").Value
    Print #iFileNum, .Range("G3").Value
    Print #iFileNum, .Range("H3").Value
    Print #iFileNum, .Range("I3").Value
    Print #iFileNum, .Range("J3").Value
    Print #iFileNum, .Range("K3").Value

End With

Close #iFileNum

 

[texasalynn]

one you need to change
Dim iFileNum As Integer
to
Dim iFileNum As Long

but I'm not following what you are trying to do for the rest

 

[VoG]

Look here http://www.cpearson.com/excel/imptext.aspx

 

[localfiend]

one you need to change
Dim iFileNum As Integer
to
Dim iFileNum As Long

but I'm not following what you are trying to do for the rest

Good idea, didn't think about that being a limit.

Look here http://www.cpearson.com/excel/imptext.aspx

Haha, I'm already trying to decipher that page. I will keep reading and post back here if it doesn't click.

 

[Domenic]

Maybe...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] CreateTextFile()

    [color=green]' Declare stuff[/color]
    [color=darkblue]Dim[/color] sFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] iFileNum [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastColumn [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] j [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=green]' Text File Location[/color]
    sFile = "C:\temp\test.txt"
    
    iFileNum = FreeFile
    
    [color=darkblue]Open[/color] sFile [color=darkblue]For[/color] [color=darkblue]Output[/color] [color=darkblue]As[/color] iFileNum
    
    [color=green]' Make the Text File[/color]
    [color=darkblue]With[/color] Worksheets("httpd")
    
        [color=darkblue]With[/color] .UsedRange
        
            LastRow = .Rows.Count + .Rows(1).Row - 1
            
            LastColumn = .Columns.Count + .Columns(1).Column - 1
            
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        
        [color=darkblue]If[/color] LastRow > 1 [color=darkblue]Then[/color]
        
            [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] LastRow
                [color=darkblue]For[/color] j = 1 [color=darkblue]To[/color] LastColumn
                    [color=darkblue]If[/color] j <> LastColumn [color=darkblue]Then[/color]
                        [color=darkblue]Print[/color] #iFileNum, .Cells(i, j).Value; Tab;
                    [color=darkblue]Else[/color]
                        [color=darkblue]Print[/color] #iFileNum, .Cells(i, j).Value
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]Next[/color] j
            [color=darkblue]Next[/color] i
                     
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]Close[/color] #iFileNum

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[localfiend]

Got it, turns out that the integer declaration was the problem and not issues with freefile. It can still probably be simplified further - but this bit of code works.

' Declare stuff
Dim sFile As String
Dim sLine As String
Dim iFileNum As Long

' Text File Location
sFile = "C:\temp\test.txt"

iFileNum = FreeFile

Open sFile For Output As iFileNum

' Make the Text File

Dim sCellComboA As String
Dim sCellComboB As String
Dim sCellComboC As String
Dim sCellComboD As String
Dim sCellComboE As String
Dim sCellComboF As String
Dim sCellComboG As String
Dim sCellComboH As String
Dim sCellComboI As String
Dim sCellComboJ As String
Dim sCellComboK As String

Dim iCellNumber As Long

iCellNumber = 2

Do Until iCellNumber = 600

sCellComboA = "A" & iCellNumber
sCellComboB = "B" & iCellNumber
sCellComboC = "C" & iCellNumber
sCellComboD = "D" & iCellNumber
sCellComboE = "E" & iCellNumber
sCellComboF = "F" & iCellNumber
sCellComboG = "G" & iCellNumber
sCellComboH = "H" & iCellNumber
sCellComboI = "I" & iCellNumber
sCellComboJ = "J" & iCellNumber
sCellComboK = "K" & iCellNumber

With Worksheets("httpd")

    Print #iFileNum, .Range(sCellComboA).Value
    Print #iFileNum, .Range(sCellComboB).Value
    Print #iFileNum, .Range(sCellComboC).Value
    Print #iFileNum, .Range(sCellComboD).Value
    Print #iFileNum, .Range(sCellComboE).Value
    Print #iFileNum, .Range(sCellComboF).Value
    Print #iFileNum, .Range(sCellComboG).Value
    Print #iFileNum, .Range(sCellComboH).Value
    Print #iFileNum, .Range(sCellComboI).Value
    Print #iFileNum, .Range(sCellComboJ).Value
    Print #iFileNum, .Range(sCellComboK).Value

End With

iCellNumber = iCellNumber + 1

Loop

Close #iFileNum

Thanks for the help guys!!

 

[texasalynn]

your welcome and thanks for the feedback