run time error

[rowingengineer]

I am over loading this scripted in the underlined areas, anyone got any ideas on how to do this export in a better fashion.

Sub ExportArchive2()
'
' ExportArchive Macro
' Save ARC_EXPORT as MSDOS text file.
' This is the Microstran Archive File.
'

' ---------------------------------------------
' Your directory is
myDirectory = "C:/"
' ---------------------------------------------


' Go to the sheet with the Microstran File on
Sheets("ARC_FILE (2)").Select

' Store the information by first geting a file name
ChDir myDirectory
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Archive Files (*.arc), *.arc")

' Store if a valid name and file is available
If fileSaveName <> False Then
MsgBox "Saving as " & fileSaveName

fileNumber = FreeFile
Open fileSaveName For Output As #fileNumber

' Cycle through the range storing each line as text
For thisRow = 1 To 48000

' Cycle through the range storing each cell on a line
For thisCol = 2 To 11

Print #1, ActiveSheet.Cells(thisRow, thisCol).Text;
Print #1, " ";
Next thisCol
' Finish the line
Print #1,
Next thisRow
Close #fileNumber

' Store the input data acknowledging where placed
Sheets("pre purlin").Select
ActiveSheet.Cells(6, 2).Value = fileSaveName

End If

End Sub

http://www.nceng.com.au/

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."

 

[MintJulep]

Why not just export or save as a space delimited file?

 

[IRstuff]

Or as tab or comma delimited, which is the more typical output

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[electricpete]

What does "overloading" mean?

=====================================
(2B)+(2B)' ?

 

[IDS]

Are you writing to an Excel worksheet?

If so, I suggest writing each line to a string array, dimensioned as:

Dim TextArray(1 to NumRows, 1 to 1)

Then if you have a range named "OutputRange" on the spreadsheet you can write the array to it with:

With Range("OutputRange")
 .ClearContents
 .Resize(NumRows, 1).Name = "OutputRange"
 End With
 Range("OutputRange").Value2 = TextArray

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[rowingengineer]

I am exporting to a msdoc.

I will see if I print the textarray to a txt file.

http://www.nceng.com.au/

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."

 

[rowingengineer]

The reason i say overloading is that the runtime error only turns up when I get over 20000 rows.

http://www.nceng.com.au/

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."

 

[IDS]

I'm not familiar with msdocs, but if they have a cell object you might be able to write the array to the msdoc directly.

Or maybe they have a maximum size of around 20,000 rows, I don't know.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[rowingengineer]

The run time error I get is method text of object range failed '-2147417848 (80010108)'

The vba is in excel and could be exported to a txt file or any test type file. I am really struggling to figure out why excel breaks down at 20000 rows.

http://www.nceng.com.au/

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."