Using VB.net to read an Excel Spreadsheet and run Macros.

[DBDivaAuto]

I am using VB to programmatically read a spreadsheet then call another spreadsheet to run a series of macros - the first macro is refreshing all of the data to match the item in the list view box. This macro takes about 2 full seconds to run. I am trying to slow down my code calls to stop the Macro from running on top of itself... I put DoEvents in the VBA of the Macro. My vb code looks like this (I threw asterisks in - I have had time up to 1200 no help):

If IO.File.Exists(filename) Then
			Dim Proceed As Boolean = False
			Dim xlApp As excel.Application = Nothing
			Dim xlWorkBooks As excel.Workbooks = Nothing
			Dim xlWorkBook As excel.Workbook = Nothing
			Dim xlWorkSheet As excel.Worksheet = Nothing
			Dim xlWorkSheets As excel.Sheets = Nothing
			Dim xlCells As excel.Range = Nothing
			Dim ColumnNumber As Integer = 1

			xlApp = New excel.Application
			xlApp.DisplayAlerts = False
			xlWorkBooks = xlApp.Workbooks
			xlWorkBook = xlWorkBooks.Open(filename)
			xlApp.Visible = True
			xlWorkSheets = xlWorkBook.Sheets

			Dim i As Integer = 0

			For Each item In Pick_Tickets.lvPalms.Items
				xlWorkSheet = CType(xlWorkSheets(Name1), excel.Worksheet)
				xlWorkSheet.Activate()

				xlWorkSheet.Cells(5, 3) = Left(Pick_Tickets.lvPalms.Items(i).SubItems(1).Text, 2) & Pick_Tickets.lvPalms.Items(i).SubItems(1).Text.Substring(3, 2)
				'MessageBox.Show(Pick_Tickets.lvPalms.Items(i).SubItems(0).Text)
				xlWorkSheet.Cells(6, 3) = Pick_Tickets.lvPalms.Items(i).SubItems(0).Text
				Dim pick1 As excel.Range = CType(xlWorkSheet.Cells(15, 7), excel.Range)
				Dim pick2 As excel.Range = CType(xlWorkSheet.Cells(19, 7), excel.Range)

				Do Until pick1.Value = pick2.Value '******************************************************************

					xlApp.Run("Macro9")
					If pick1.Value = pick2.Value Then
						Exit Do
					End If

					Threading.Thread.Sleep(500)
				Loop '**************************************************************************************************

				Dim getMyMacro As String = GetMacro(Pick_Tickets.lvPalms.Items(i).SubItems(2).Text, Pick_Tickets.lvPalms.Items(i).SubItems(0).Text, Pick_Tickets.lvPalms.Items(i).SubItems(1).Text)
				'MessageBox.Show(getMyMacro)
				'If getMyMacro <> "" Then
				'xlApp.Run(getMyMacro)

				'xlWorkSheet = CType(xlWorkSheets(Pick_Tickets.lvPalms.Items(i).SubItems(2).Text), excel.Worksheet)
				'xlWorkSheet.Activate()
				'xlApp.Run("Button1_Click")
				'End If

				i = i + 1
			Next

			xlWorkBook.Close()
			xlApp.UserControl = True
			xlApp.Quit()
			xlCells = Nothing
			xlWorkSheets = Nothing
			xlWorkSheet = Nothing
			xlWorkBook = Nothing
			xlWorkBooks = Nothing
			xlApp = Nothing

		Else
			MessageBox.Show("'" & filename & "' not located. ")
		End If

	End Sub

 

[SkipVought]

Hi,

I’m an Excel VBA guy. Maybe I can help.

You are calling Macro9 and getMyMacro. You post neither???

Since they seem to be running asynchronously, you need a DONE indicator, rather than trying to slow your calling procedure an indeterminate amount of time.

Make the called procedures Functions, rather than Subs.

Function Macro9(bFinished As Boolean)
‘Your code here
   bFinished = True
End Function

The call like this...

‘ 
   Dim bFinished As Boolean = False
   XlApp.Run(“Macro9” bFinished)
   Do Until bFinished
      DoEvents
   Loop

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DBDivaAuto]

GetMyMarco is VB code Function to choose which macro to run in the spreadsheet. It prints a PDF.

If JobType = "Hoods"  Then 
]MacName = "Macro7"
		Else
		If JobType = "Start Up" Then
			MacName = "Macro8"

Marco9 is the Macro inside the Excel Spreadsheet getting called in VBA programmatically from VB. Putting in waits just stops the code and it had to finish when it starts up so not helping.

Sub Macro9()
'
' Macro9 Macro
' Macro recorded 7/10/2008 by David Smith
'

'
    Sheets("Equip Info").Select
    Range("A5").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    DoEvents
    Range("L5").Select
    ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
    DoEvents
    Range("W5").Select
    ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
    DoEvents
    Range("AG6").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    DoEvents
    Range("AP6").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    DoEvents
    Range("AY6").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    DoEvents
    Range("U33").Select
    ActiveSheet.PivotTables("PivotTable11").PivotCache.Refresh
    DoEvents
    
    Sheets("Finish Query").Select
    Range("B9").Select
    ActiveSheet.PivotTables("PivotTable11").PivotCache.Refresh
    DoEvents
    
    Sheets("Labor Info").Select
    Range("A5").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    DoEvents
    Range("L5").Select
    ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
    DoEvents
    Range("W5").Select
    ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
    DoEvents
    Range("AH5").Select
    ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
    DoEvents
    Range("AS5").Select
    ActiveSheet.PivotTables("PivotTable10").PivotCache.Refresh
    DoEvents
    Range("BD5").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
    DoEvents
    Range("BY5").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    DoEvents
    Range("CJ5").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    DoEvents
   
    Sheets("Dashboard").Select
    Range("C5").Select
     
    Application.Wait (Now + TimeValue("00:00:05"))
    
End Sub

 

[DBDivaAuto]

Make the called procedures Functions, rather than Subs.
CODE
Function Macro9(bFinished As Boolean)
‘Your code here
bFinished = True
End Function

The call like this...
CODE
‘
Dim bFinished As Boolean = False
XlApp.Run(“Macro9” bFinished)
Do Until bFinished
DoEvents
Loop

Skip - I got confused - so make the actual Macro9 a Function call in VBA. and code a DoEvenets in VB? DoEvents doesn't really work in VB.

 

[SkipVought]

Like I stated: I’m an Excel VBA guy.

Somehow you need to stop the execution of the next VB statement until bFinished, returned from Macro9, is True.

I’m accustomed to, when calling an asynchronous process, using a loop to test some return parameter. Typically the DoEvents makes other processes possible in m environment.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DBDivaAuto]

So here's the deal - setting it up as a function is not working. There is a full half second of code still needing to run and because it's getting trampled on still it causes a continuous loop. I have literally spent 12 hours on this one issue. Next - I am going to try rearranging the order of the refreshes to the SQL pull - I didn't write the code and don't know how much I will break it but I am so frustrated.

Sub Renew()
    Dim bFinished As Boolean
    bFinished = False
    
    Do Until bFinished = True
        Macro9 bFinished
        DoEvents
    Loop
    
End Sub
Function Macro9(bFinished As Boolean)
'
' Macro9 Macro
' Macro recorded 7/10/2008 by David Smith
'

'
    Sheets("Equip Info").Select
    Range("A5").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    DoEvents
    Range("L5").Select
    ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
    DoEvents
    Range("W5").Select
    ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
    DoEvents
    Range("AG6").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    DoEvents
    Range("AP6").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    DoEvents
    Range("AY6").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    DoEvents
    Range("U33").Select
    ActiveSheet.PivotTables("PivotTable11").PivotCache.Refresh
    DoEvents
    
    Sheets("Finish Query").Select
    Range("B9").Select
    ActiveSheet.PivotTables("PivotTable11").PivotCache.Refresh
    DoEvents
    
    Sheets("Labor Info").Select
    Range("A5").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    DoEvents
    Range("L5").Select
    ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
    DoEvents
    Range("W5").Select
    ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
    DoEvents
    Range("AH5").Select
    ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
    DoEvents
    Range("AS5").Select
    ActiveSheet.PivotTables("PivotTable10").PivotCache.Refresh
    DoEvents
    Range("BD5").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
    DoEvents
    Range("BY5").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    DoEvents
    Range("CJ5").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    DoEvents
   
    Sheets("Dashboard").Select
    Range("C5").Select
    
    If Range("C9").Value = "Yes" Then
        bFinished = True
    End If
    
End Function

 

[SkipVought]

Sub Renew()
    Dim bFinished As Boolean
    bFinished = False
    
    [b]Macro9 bFinished[/b]

    Do Until bFinished = True
        DoEvents
    Loop
    
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DBDivaAuto]

I don't know what is happening because it says RUNNING BACKGROUND QUERY and not doing anything - so I have to assume it is stuck again trampling on itself.

I hate when it should be so easy and it is so not. I am going to try breaking up the refreshes into different calls.

 

[SkipVought]

Why the If here?

If C9 is not Yes then bFinished is False???

‘
    If Range("C9").Value = "Yes" Then
        bFinished = True
    End If

Why not simply...

bFinished = True

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DBDivaAuto]

I know that if the girls in that dept are data inputting and running that macro it has to run 2x before field c9 equals Yes. They push a button to run it. I am not sure why - what isn't updating the first time around that does the second. I also know that you have to wait for that macro to finish or it doesn't calculate right. We have a girl there who types 90 wpm and was manually over running it. I had to tell her to be less efficient - take a pause.

I just need the code to run all the way through before starting again and the only way now to do that is deconstruct - which just got it pushed to the back burner

 

[SkipVought]

Then you don’t want to run your VB until that refresh has tasken place MANUALLY, eliminating the need to run Macro9.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DBDivaAuto]

Good Morning Skip -
I am automating a manual process. I know when the process is run manually - with a human clicking the button that runs the macro we ran into problems because our data person types faster that a normal person. Programatically, I am having issues because no matter what I have tried, the macro is still running when the 2nd attempt is made.

I do not know "WHY" it takes two attempts for the the macro to run before the data is refreshed to match a new entry because I didn't write the macros, workbook, etc. just that it does.

Placed a msgbox in the VB code and could see the message a full .5 second before the macro finished - therefore I know it is causing a loop because the macro isn't finished running and getting trampled on with a new run.

I need to slow down the code. Making changes in the VBA to DoEvents or a function call to complete before moving on isn't helping, writing sleep code into the VB isn't working. I do not know how to tell that second run to wait until that process is done completely.

 

[SkipVought]

therefore I know it is causing a loop...

This condition is not causing a loop. A loop is caused by a programmer putting a loop in the program.

What you have is an asynchronous process where you need a means of detecting then that process has completed.

Under normal synchronous conditions, a program executes statement by statement, which is what synchronism means.

Did some Googling. You might try this, of which I was totally unaware...

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
Application.CalculateFullRebuild
Application.CalculateUntilAsyncQueriesDone

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]