Save Problem, delay required in code, How do i work around this?

I have some large code importing/export data from
AutoCAD->Excel->Excel->etc->AutoCAD. When it come time to save these
documents, I have found that the time it takes to save across the network is
too slow sometimes for the code, and VBA wants to progress on even thought
the save isn't complete, and this causes problems in the applications,
especially when I am trying to close them when it is still active. I am
currently setting a delay in my code after each save. This "WORKS" but I do
not like it. Is there a better alternative. Some sort of reactor to the save
event complete or something that I am missing.

Keep in my I am still new to VBA, so go easy on me.

Thanks, Dan

 

Are you using sendcommand for moving the data?

-- Mike
___________________________
Mike Tuersley
___________________________
the trick is to realize that there is no spoon...

 

Are you responding to the start of the save command or its completion?

 

I want to respond to the completion of the save process.

 

No use of sendcommand is issued.
From my AutoCAD VBA code: ActiveWorkbook.Close SaveChanges:=True

 

So you're kicking off the routine in the EndCommand (or whatever the
name is) event?

 

You have to Dim WithEvents your excel workbook object
so that it will have events.

 

This is a separate issue but ... If you are controlling Excel from AutoCAD VBA and pumping a lot of data into a workbook you may find that the transfer of that data is taking an enormous amount of time because of the inter-process communication delays.

If that is the case you may want to export the data to Excel in a different way and that may also solve the problem you are currently having.

Here is what I did that reduced an export from several minutes to just a few seconds.

#1 - In AutoCAD VBA collect all the data and write to a temporary file.
#2 - Launch a blank Excel spreadsheet that has an Autoexec macro

The autoexec macro has the responsibility to detect the temporary file and load the data into a second workbook and then save the second workbook and close itself (ie. the first workbook containing the autoexec macro). All this is done independantly of AutoCAD so there are no timing issues.

 

If the Excel workbook/sheet is only used from store data (no complicated
formula/calculation applied to particular cells), you can use DAO/ADO to
read/write data to Excel WorkBook/Sheet. It is very fast, no Excel
Auotmation is requred, which is the main cause of slowness.

VBA and pumping a lot of data into a workbook you may find that the transfer
of that data is taking an enormous amount of time because of the
inter-process communication delays.

different way and that may also solve the problem you are currently having.

load the data into a second workbook and then save the second workbook and
close itself (ie. the first workbook containing the autoexec macro). All
this is done independantly of AutoCAD so there are no timing issues.

 

That is interesting - are there examples of that somewhere? I couldn't use it in my project but it might be useful in the future.

 

GT,

If you want to play, you can try this class to enable automation to excel with DAO. Whenever your ready to try ADO give me a yell....unless this is completely confusing to you. If this is confusing many here can assist you.

This isn't what you've asked for but I can't find that exact class between AutoCAD and Excel...but I started with this to get there. Some don't program this way so I hope it helps you rather than hurt.

For those that push data to a fro Access, Excel, and AutoCAD this could do a sweet trick....or not.


Very good luck to you....

Bob Coward
CADS, Inc.

800-366-0946


Class : CExcel
' Description : Class for working with Microsoft Excel through Automation


Private m_objExcel As Excel.Application
Private m_objWorkbook As Excel.Workbook

Public Property Get AppExcel() As Excel.Application
' Returns: A handle to the current instance of Excel
'
Set AppExcel = m_objExcel

End Property

Public Property Get CurWorkbook() As Excel.Workbook
' Returns: A handle to the currently open workbook

Set CurWorkbook = m_objWorkbook

End Property

Public Sub CloseExcel()
' Comments : Closes Excel
' Parameters: None
' Returns : Nothing
'
On Error GoTo PROC_ERR

m_objExcel.Quit

Set m_objExcel = Nothing

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"CloseExcel"
Resume PROC_EXIT

End Sub

Public Sub CloseWorkbook( _
fSave As Boolean)
' Comments : Closes the current workbook
' Parameters: fSave - True to save changes, False to discard changes
' Returns : Nothing
'
On Error GoTo PROC_ERR

m_objWorkbook.Close SaveChanges:=fSave

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"CloseWorkbook"
Resume PROC_EXIT

End Sub

Public Sub CreateTableFromAccess( _
strDatabase As String, _
strDataSource As String, _
fFieldNames As Boolean, _
Optional varMaxRecs As Variant)
' Comments : Gets the contents of an Access table or query into
' the current document
' Parameters: strDatabse - full path and name of the Access database
' you want to read from
' strDataSource - name of a table or query in the database
' to read records from
' fFieldNames - True to put the field names in the first
' row, false otherwise.
' varMaxRecs - optional: set to the maximum number of
' records you want to retrieve. To include all records,
' don't specify this argument.
' Returns : Nothing
'
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intCounter As Integer
Dim intFieldCount As Integer
Dim lngRowCount As Long
Dim varField As Variant
Dim intRow As Integer
Dim intCol As Integer

On Error GoTo PROC_ERR

' Open the database objects
Set dbs = DAO.DBEngine.OpenDatabase(strDatabase)
Set rst = dbs.OpenRecordset(strDataSource)
intFieldCount = rst.Fields.Count

intRow = 1
intCol = 1

' Add the field names if specified
If fFieldNames Then
For intCounter = 1 To intFieldCount
m_objWorkbook.ActiveSheet.Cells(1, intCounter).Value = _
rst.Fields(intCounter - 1).Name
Next intCounter
End If

' Start inserting data on the second row of the table
lngRowCount = 2

With rst
' Loop through all records
Do Until .EOF

For intCounter = 1 To intFieldCount
' Add each fields value
varField = .Fields(intCounter - 1).Value

' Handle null field values
If IsNull(varField) Then
varField = "<null>"
End If

m_objWorkbook.ActiveSheet.Cells(lngRowCount, intCounter).Value = _
varField

Next intCounter

lngRowCount = lngRowCount + 1

' See if we are still in range
If Not IsMissing(varMaxRecs) Then
If lngRowCount > varMaxRecs Then
Exit Do
End If
End If

' Move to the next record
.MoveNext

Loop
End With

' Cleanup
rst.Close
dbs.Close

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"CreateTableFromAccess"
Resume PROC_EXIT

End Sub

Public Sub CreateWorkbook( _
strName As String)
' Comments : Creates a new workbook and saves it
' Parameters: strName - name for the new workbook
' Returns : Nothing
'
On Error GoTo PROC_ERR

Set m_objWorkbook = m_objExcel.Workbooks.Add

m_objWorkbook.SaveAs filename:=strName

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"CreateWorkbook"
Resume PROC_EXIT

End Sub

Public Sub InsertValue( _
strRange As String, _
varValue As Variant)
' Comments : Inserts values into cells
' Parameters: strRange - string defining the range to insert into
' varValue - value to insert
' Returns : Nothing
'
On Error GoTo PROC_ERR

m_objWorkbook.ActiveSheet.Range(strRange).Value = varValue

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"InsertValue"
Resume PROC_EXIT

End Sub

Public Sub OpenWorkbook( _
strFileName As String, _
fReadOnly As Boolean, _
Optional varPassword As Variant)
' Comments : Opens the named file and associates it with the class
' Parameters: strFileName - full path and name of the file to open
' fReadOnly - True to open readonly
' varPassword - Optional: specify the password if the
' workbook file is password protected.
' Returns : Nothing
'
On Error GoTo PROC_ERR

If Not IsMissing(varPassword) Then
Set m_objWorkbook = m_objExcel.Workbooks.Open( _
strFileName, _
, _
fReadOnly, _
, _
varPassword)
Else
Set m_objWorkbook = m_objExcel.Workbooks.Open( _
strFileName, _
, _
fReadOnly)
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"OpenWorkbook"
Resume PROC_EXIT

End Sub

Public Sub OpenWorkbookFromLib( _
strFileName As String, _
fReadOnly As Boolean, _
Optional varPassword As Variant)
' Comments : Opens the named file and associates it with the class.
' This version looks in the Excel library folder.
' Parameters: strFileName - name of the file to open
' fReadOnly - True to open readonly
' varPassword - Optional: specify the password if the
' workbook file is password protected.
' Returns : Nothing
'
Dim strLibPath As String

On Error GoTo PROC_ERR

strLibPath = m_objExcel.LibraryPath & _
m_objExcel.PathSeparator & _
strFileName

If Not IsMissing(varPassword) Then
Set m_objWorkbook = m_objExcel.Workbooks.Open( _
strLibPath, _
, _
fReadOnly, _
, _
varPassword)
Else
Set m_objWorkbook = m_objExcel.Workbooks.Open( _
strLibPath, _
, _
fReadOnly)
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"OpenWorkbookFromLib"
Resume PROC_EXIT

End Sub

Public Sub PrintSheet( _
intFrom As Integer, _
intTo As Integer, _
intCopies As Integer, _
fPreview As Boolean, _
fPrintToFile As Boolean, _
fCollate As Boolean)
' Comments : Prints the active workbook
' Parameters: intFrom - starting page number
' intTo - ending page number
' intCopies - number of copies
' fPreview - True for print preview
' fPrintToFile - True to print to a file. Excel will prompt
' for the filename when this is set to True.
' fCollate - True to collate copies
' Returns : Nothing
'
On Error GoTo PROC_ERR

m_objWorkbook.PrintOut _
intFrom, _
intTo, _
intCopies, _
fPreview, _
, _
fPrintToFile, _
fCollate

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"PrintSheet"
Resume PROC_EXIT

End Sub

Public Sub SortRange( _
strRange As String, _
strKey As String, _
Optional fAscending As Boolean = False)
' Comments : Sorts the specified range
' Parameters: strRange - range to sort
' strKey - range to use as the key for sorting
' fAscending - True for ascending, False for descending
' Returns : Nothing
'
Dim lngSort As Integer

If fAscending Then
lngSort = xlAscending
Else
lngSort = xlDescending
End If

m_objWorkbook.ActiveSheet.Range(strRange).Sort _
Key1:=ActiveSheet.Range(strKey), order1:=lngSort

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"SortRange"
Resume PROC_EXIT

End Sub

Public Sub StartExcel(fVisible As Boolean)
' Comments : Starts an instance of Excel
' Parameters: fVisible - True to make Excel visible
' Returns : Nothing
'
On Error GoTo PROC_ERR

Set m_objExcel = New Excel.Application
m_objExcel.Visible = fVisible

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"StartExcel"
Resume PROC_EXIT

End Sub

 

Although ADO is probably the way to go,
another way is to write to a text file in comma
delimited format, and save with a .csv extension.
I had some code go from a long wait to a couple
of seconds.

 

Not to "one up" you, Paul, but rather than make a physical CSV file then
use Excel to open it, just use XML. You create a virtual XMLDocument and
push it to Excel eliminating the whole file piece. If you want to make it
even sweeter, assuming you are writing the same data formats over and over,
use an XSD file so you're passing REAL data - not strings!

-- Mike
___________________________
Mike Tuersley
___________________________
the trick is to realize that there is no spoon...

 

Thanks Mike...One up me away. I like
this idea. Just what I needed something
to distract me form coding...;-) guess it's
past midnight, time to play some anyway!

 

Paul,

I think Mike is right on. My first exposure to XML was at the '99 VBITS conference and I actually thought it would revolutionize the way web search engines.

I can direct to the Codeproject.com that Exports Excel to XML in VBA...http://www.codeproject.com/useritems/xls2xml.asp

Please prepare you XMLDOM lecture and we'll all attend....Mike can play backup for moral support.

Good intrduction Mike.

Night all,

Bob Coward
CADS, Inc

800-366-0946

 

Thanks, guys =) Sorry if this'll keep you up late and away from your real
code;-)

Yeah, I started down the xml path back in 2001 but didn't do a lot because
I had so much legacy code in my bag that I re-used on each project. Now,
though, in .NET that isn't the case and its even easier!

-- Mike
___________________________
Mike Tuersley
___________________________
the trick is to realize that there is no spoon...

 

So how much can you do with XML? It is just a text file right?

I have used the RTF file format to create a complex Word document with tables, headers, footers etc. That is very fast since RTF is plain text and supports all of Word's capabilities.

What if you need to create a spreadsheet with complex formulas, formatting, coloring, page layout, multiple fonts, etc. Can that be done with the XML approach similar to what I did with RTF?

 

GT,

"Just a text file right"

NO - it is a transport medium between applications. Take a look at ADO 2.6 and up.

What is XML --- http://www.xml.com/pub/a/98/10/guide0.html
What is XSLT --- http://www.xml.com/pub/a/2000/08/holman/index.html
What is XML Schema --- http://www.xml.com/pub/a/2000/11/29/schemas/part1.html

Go forth and realize the wall of aptitude isn't that tall.

Good luck,

Bob Coward
CADS, Inc

800-366-0946

 

Excel 2003 is based around XML transport
as it's becoming the standard for transporting
data between applications. You don't want
to miss this ship... "Excel 2003 Programming"
by Jeff Webb got me hooked.

 

To use an oversimplified analogy, XML is to data what DXF is to a drawing.
While XML is a text file, it is self describing and can be formatted
through DTDs, schemas and style sheets.

-- Mike
___________________________
Mike Tuersley
___________________________
the trick is to realize that there is no spoon...