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

[Dan]

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

[Mike Tuersley]

Are you using sendcommand for moving the data?

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

[Frank Oquendo]

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

[Dan]

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

"Frank Oquendo" <foquendo@gmail.com> wrote in message
news:424c3ca6$1_2@newsprd01...

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

[Dan]

No use of sendcommand is issued.
From my AutoCAD VBA code: ActiveWorkbook.Close SaveChanges:=True
"Mike Tuersley" <mtuersley_NOT_@rand.com> wrote in message
news:gef1g893xjnq$.144o49v8k8zxk.dlg@40tude.net...

Are you using sendcommand for moving the data?

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

[Frank Oquendo]

Dan wrote:

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

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

[Jorge Jimenez]

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

--
Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica


"Dan" <danderson@nospamculpandtanner.com> wrote in message
news:424c56dd$1_1@newsprd01...

No use of sendcommand is issued.
From my AutoCAD VBA code: ActiveWorkbook.Close SaveChanges:=True
"Mike Tuersley" <mtuersley_NOT_@rand.com> wrote in message
news:gef1g893xjnq$.144o49v8k8zxk.dlg@40tude.net...
Are you using sendcommand for moving the data?

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

[GTVic]

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.

[Norman Yuan]

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.

"GTVic" <nospam@address.withheld> wrote in message
news:22460158.1112308770824.JavaMail.jive@jiveforu m1.autodesk.com...

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.

[GTVic]

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.

[bcoward]

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
bcoward@mindspring.com

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

[Paul Richardson]

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.
"Norman Yuan" <NotReal@NotReal.not> wrote in message
news:424c8b6a_1@newsprd01...

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.

"GTVic" <nospam@address.withheld> wrote in message
news:22460158.1112308770824.JavaMail.jive@jiveforu m1.autodesk.com...
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.

[Mike Tuersley]

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

[Paul Richardson]

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!
"Mike Tuersley" <mtuersley_NOT_@rand.com> wrote in message
news:14c6ufgnayxxq.6g4mzrpnmgmr.dlg@40tude.net...

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

[Dan]

WOW! What a flood of great information. Many people in this conversation
have help me with my very new limited knowledge of VBA, and I wanted to say
thank you. Much of this is way over my head at the moment, but I will dive
in, and start swimming. Thanks for the help, guidance, and support.

Dan

"Dan" <danderson@nospamculpandtanner.com> wrote in message
news:424c3216_3@newsprd01...

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