Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

run time error

Status
Not open for further replies.

rowingengineer

Structural
Jun 18, 2009
2,463
AU
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


"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."
 
Replies continue below

Recommended for you

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

TTFN
faq731-376
7ofakss

Need help writing a question or understanding a reply? forum1529
 
What does "overloading" mean?

=====================================
(2B)+(2B)' ?
 
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:

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

Doug Jenkins
Interactive Design Services
 
I am exporting to a msdoc.

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

"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."
 
The reason i say overloading is that the runtime error only turns up when I get over 20000 rows.

"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."
 
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
 
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.

"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."
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top