×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

run time error

run time error

run time error

(OP)
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."

RE: run time error

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

RE: run time error

What does "overloading" mean?

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

RE: run time error

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
http://newtonexcelbach.wordpress.com/

RE: run time error

(OP)
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."

RE: run time error

(OP)
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."

RE: run time error

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/

RE: run time error

(OP)
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."

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close