Talking back from Excel 2

[JCruz063]

Hi All,
I have an application that creates/displays certain reports using Microsoft Excel (2002 to be exact). What I do is the following:

1 - Collect the data that needs to be displayed:
2 - Create an instance of an Excel application using a previously-created template workbook which contains the modules with subs and functions that perform all the work.
3 - Call a sub/function in the Excel application, sending it, as parameters, all the data needed.

That sub/function on step three is one that I created priviously, like I said, and it's in charge of taking the data and displaying it on the worksheet nicely formatted. When the sub/function completes its operation, it shows Excel (which was previously hidden) and the user is very very happy.

OK - enough background info. Well, that sub from step 3 above could take quite a while (even up to 15 seconds or more on fast machines) to complete. I would love to provide the user with:

1 - A progress bar that shows the percentage of work remaining.
2 - A cancel button to allow the user to stop the sub prematurely.

But you see, as of right now, I cannot do that beause I don't know how to talk back to my VB6 app from Excel. Once I call that sub on step 3, control passes to Excel and the program just waits, and waits until Excel is done and execution comes back to it. If I could have the Excel sub run on a different thread and call back a function on my VB6 app every once in a while to say hi and to inform of the progress, it would be great.


Thus, the questions are:

1 - Is there anyway to talk back to my application from Excel so that I could update a progress bar to inform the user of the progress?

2 - In case the user wants to cancel the operation, how then would I talk back to Excel to tell it to stop working.

JC

Friends are angels who lift us to our feet when our wings have trouble remembering how to fly...

 

[MichaelRed]

No answer from here, but at least one additional question. What/why is the report in Excel? What functionallity exists there that you need for the reporting?





MichaelRed
mlred@verizon.net

 

[JCruz063]

Michael,

1 - I already have Excel and that means I don't have to buy an additional grid control to create the report.

2 - Excel's functionality works as expected. I've used other grid controls before and they leave too much to be desired. Obtaining a nice one is expensive and the cheap ones are just that: cheap!

3 - I'm used to having complete control on a cell-by-cell basis of what I want in the report. Most of the controls I've tested require me to bind them to some data source and let them do the rest. I don't like that! I want full cell-by-cell control, and Excel gives me just that!

What's wrong with Excel? Have you used it? It works beautifully! Plus, when the report is printed, the user has complete control of it (which is something I want), as it's just a plain Excel document.

JC

Friends are angels who lift us to our feet when our wings have trouble remembering how to fly...

 

[MichaelRed]

I have used most all of the MS. Office suite of 'productivity apps' both in theier native stand alone confuguration and as office automation objects. While they have various strengths (obviously accorduing to their purpose / intent), I find no particular advantage to Excel as a 'reporting tool'.

I am not attempting to start a 'discussion' of the pros and cons of this (or other products), just to understand your specific needs.

As far as I know, there is no particular way to have the two programs have an interactive dialog, reporting the progress of Excel back to VB. What could, perhaps, be accomplished is to have VB accomplish a finer grain of control over the Excel function(s). If, for instance, the Excel collection of activities is broken down into 100 (just for example) seperate/independent procedures, then you could create your progress bar based on having VB call (activate) each of the procedures sequentially, and counting each as 1% of the progress. Another option might be to simply "test" the run/delay time of the process with a range of the final reports' configuration, and bgased on those results generat4e an arbitrary 'progress' based on the anticipated time to complete based on the current configuration. A third alternative which comes readily to mind i9s to simply 'translate' your VB app to operate in the Excel environment. Of course without knowing anything about your two applications, the advantage/disadvantage of these -or any of the myriad of alternatives- is more or less an exercise in speculation.




MichaelRed
mlred@verizon.net

 

[JCruz063]

Michael,
Thanks for your reply!

Unfortunately, none of the suggestions are feasable :-( for the following reasons:

1 - Once I invoke the Excel application, all the necessary code runs in Excel. The job of my VB app is simply to lunch Excel and give it the data to work with. This happens in two lines of code. Having VB do the work Excel does would take too long as communication with EXE servers is very slow. Thus I write all the code in Excel and simply give it the data to work with.

2 - The time it takes Excel to do its work varies depending on how much memory and MHz the machine has, thus I cannot have a specific amount of time to wait for. This does sound like what I may end up doing, though.

3 - The Excel reports are one tiny bit of my entire application, thus I couldn't 'translate' it to Excel.

Thanks again Michael!

JC

Friends are angels who lift us to our feet when our wings have trouble remembering how to fly...

 

[jjames]

You can pass a reference of a VB form to your Excel application.

In Excel...

Private Sub GenerateReport(frm as object)

frm.backcolor = 0

End sub

In VB, call the sub

objExcel.Run "GenerateReport", frmProgress

 

[rookie7799]

Try this

Dim cnnExcel as ADODB.Connection, rstExcel as ADODB.Recordset
Dim item, fee

set cnnExcel = New ADODB.Connection
set rstExcel = New ADODB.Recordset

With cnnExcel
If .State = adStateOpen Then .Close
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open strFileName
End With

strWorksheetName = "Sheet1"

rstExcel.Open strWorksheetName, exConn, adOpenStatic, adLockOptimistic, adCmdTable
rstExcel.MoveFirst

ProgressBar1.Max = rstExcel.RecordCount

Do Until rstExcel.EOF
ProgressBar1.Value = Abs(rstExcel.AbsolutePosition)
item = rstExcel.Fields(0)
fee = rstExcel.Fields(1)
If Not IsNumeric(item) Then item = 0
If Not IsNumeric(fee) Then 0
rsFees.AddNew '<<--- rsFees is a global recordset that I am saving the new data in but you can do whatever you want with the rstExcel object.
rsFees!schedule = lngScheduleID
rsFees!item = item
rsFees!fee = fee
rsFees.Update
rstExcel.MoveNext
Loop
MsgBox "Import completed"
rstExcel.Close
Set rstExcel= Nothing
cnnExcel.Close
Set cnnExcel = Nothing

 

[JCruz063]

Guys, thanks again for your replies!

jjames,
I should have thought of that, Thanks! I haven't tried it yet but I'm afraid that (1) it will be very slow, and (2) the VB form with the progress bar will not be refreshed appropriately. In any case, I'll try it out and let you know how it goes. Thanks again!

rookie7799,
Thanks for your reply. I need to communicate from Excel to VB and your code does the opposite . Thanks again!

JC

Friends are angels who lift us to our feet when our wings have trouble remembering how to fly...

 

[rookie7799]

Hey JC
As I understood your application is running inside Excel as Macro right?
I'm working on the same thing, anyways if you inside Visual Basic Editor, here are the steps...

http://68.198.213.153:2121/decal/vb/step_1.jpg

http://68.198.213.153:2121/decal/vb/step_2.jpg

http://68.198.213.153:2121/decal/vb/step_3.jpg

http://68.198.213.153:2121/decal/vb/step_last.jpg

and then use the code I supplied earlier

hope that helps

 

[JCruz063]

rookie7799,
Thanks again for your reply!

I have a full-blown Visual Basic application that runs independently from Excel. The VB app delegates the printing of reports to Excel where I have a few functions (macros) that take care of the printing (format the data and display it to the user). I don't have any problems with any of the two applications. The Excel app isn't visible until the report is completed. The problem is that when the VB app tells the Excel one to print the report, Excel takes a long time do it. In the mean time, the user is just there looking at the VB app impatiently. What I need is a way to have Excel send messages to VB about its progress so that I could display a progress bar in the VB app to let the user know how much more he needs to wait.

OK, so to wrap things up... I need Excel to inform the VB application (which is running on a separate address space) how much work it has done. The code you provided doesn't communicate with VB and that's why I can't use it.

I appreciate your response, although perhaps you didn't understand me correctly before. But thanks anyways!

JC

Jesus Christ! I was unaware of all the smileys available here at tek-tips.

 

[rookie7799]

Ohhhhhhh I see now, yes I did misunderstood you. But that is really interesting question!
I'll try to come up with smth

 

[JCruz063]

rookie7799,
Thanks a lot! I appreciate your interest. To help you in your thinking, take a look at what jjames suggested in the thread before yours:

You can pass a reference of a VB form to your Excel application.

In Excel...

Private Sub GenerateReport(frm as object)

frm.backcolor = 0

End sub

In VB, call the sub

objExcel.Run "GenerateReport", frmProgress

I think I'll try that although, like I mentioned in a previous post, I think it'll be very slow. I'm currently working on some other project that I'll finish in about 3 or 4 days, and then I'll go back to this and try it out. Thanks again!

JC

Jesus Christ! I was unaware of all the smileys available here at tek-tips.

 

[rookie7799]

Ok I think I came up with something...
It just worked for me but I couldn't test it proparly, you will though since you said it takes a while to generate printing report.
this can be added to module in Excel

Public intVal As Integer, intTotal As Integer

Public Sub savetoreg()
a = 1
Do
a = a + 1
Loop While Worksheets("Sheet1").Cells(a, 1).Value <> ""
SaveSetting "Book", "book", "total", a '<----- THIS IS FOR PROGRESS BAR MAX VALUE,

a = 1
Do
intVal = Worksheets("Sheet1").Cells(a, 1).Value
SaveSetting "Book", "book", "val", intVal '<------ saves every other record to registry.
a = a + 1
Loop While Worksheets("Sheet1").Cells(a, 1).Value <> ""
End Sub

And here is a simple form in VB with 2 buttons, text1 field, and timer

Private Sub Start_Click()
Timer1.Enabled = True
End Sub

Private Sub Stop_Click()
Timer1.Enabled = False
End Sub

Private Sub Timer1_Timer()
Text1.Text = GetSetting("Book", "book", "val")
End Sub

if you run excel application and a stand alone VB application at the same time you will be constantly updating registry from excel and constantly getting them from VB app.

Try it out

Have fun

 

[rookie7799]

Here is the updated version with progress bar

Private Sub Form_Load()
ProgressBar1.Max = GetSetting("Book", "book", "total")
End Sub

Private Sub Start_Click()
Timer1.Enabled = True
End Sub

Private Sub Stop_Click()
Timer1.Enabled = False
End Sub

Private Sub Timer1_Timer()
Text1.Text = GetSetting("Book", "book", "val")
ProgressBar1.Value = Text1.Text
End Sub

 

[JCruz063]

rookie7799,
Looks very interesting and fun to me. I'll sure try it. My app is multi-user so I guess, I'll have to pass to Excel the name of the user currently logged on so that, when Excel writes to the registry, it writes the name of the user along with each entry and thus, avoids any conflict in the data. Also, before I give control to Excel, I'll sure clear those registry values so that I start out with a clean set of numbers.

I have to say that this is a very clever solution, although I'm not sure if writing to and reading from the registry many times will hurt performance. Too bad I won't get a chance to try it until about 3 or 4 days from now. I can't wait to do it. In the mean time, here's your well-deserved star!

Thanks again!

JC

 

[jlvfromthenorth]

Hi,

My application does the same as yours => talk to Excel.

1) The progressbar:
The user send a rapport to excel. I use a module to do all the exchange with excel.
Ex:
In the form:
Form1 sent a dataset and the progressbar to the module

Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click

PBarExport.Visible = True

ExportToExcel(Dataset,PBarExport)

PBarExport.Visible = False
End Sub

In the module:
Function ExportToExcel(ByVal myDataset As DataSet, ByVal PBarExport As Object)

' myCount is the number of rows in my dataset
PBarExport.Step = 1
PBarExport.Maximum = myCount
PBarExport.Minimum = 1
PBarExport.Value = 1

Do While MyDSRowIndex < myCount
' increment the progressbar
PBarExport.PerformStep()

'read the dataset and write in the excel book
objSheet.Cells(MyExLineNbr,1)= mydataset.datas

MyDSRowIndex += 1
MyExLineNbr += 1

Loop


This works for me it's all right...
I hope it works for you...

2) To stop the Excel application ??????
I don't now yet, if you have some more information since the last time , please let me now...

 

[JCruz063]

jlvfromthenorth,
Thanks for your post!

You know, I really don't know how such a simple thing like this could slip my mind. Why didn't I think of simply sending the progress bar itself to Excel ? Well, I guess, sometimes your brain just doesn't work the way it's supposed to. Now that you said it, it sounds so obvious, but before, it just didn't come to my mind. In any case, I'm not quite sure it'll work, but it does sound like the most logical solution... Thanks!

As for how to stop Excel, I'm planning to use the Windows Registry, as rookie7799 suggested. I'll create a variable in the Registry that will be saved under my application's root. I'll call it something like "stopValue" and it will be either True or False. I'll have a "Cancel" button in the VB6 application that, when clicked, will set the value of a form-level variable in the application to True. Then, using a Timer, I'll save the value of this variable to the registry every, say 3 seconds. Now, from Excel, I'll check the registry every couple of seconds as well, to see what the value of this variable is. If I find it to be true, I'll exit Excel and display what has been printed so far. Cool, isn't it?

One last point: I told rookie7799 that I didn't know how wise it was to update/check the registry that often, but there's only one way to find out. I haven't had time to try it yet, as I'm working on something else at the moment, but as soon as I'm done, I'll give it a shot! It wouldn't be bad if you tried before I do so that you tell me how it works. I hope you're using VB6, though, as the example you gave me above uses VB.NET. Thanks again!

JC

Oh Wow!!! I was unaware of all the smileys available here at tek-tips.