×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-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!
  • Students Click Here

*Tek-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

Jobs

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

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

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

(OP)
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):

CODE --> VB

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 

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

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.

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

CODE --> VB

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.

CODE --> VBA

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 

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

(OP)

Quote (SkipVought)

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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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


CODE --> VBA

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 

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

CODE

Sub Renew()
    Dim bFinished As Boolean
    bFinished = False
    
    Macro9 bFinished

    Do Until bFinished = True
        DoEvents
    Loop
    
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

Why the If here?

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

CODE

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

Why not simply...

bFinished = True

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

Quote:

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

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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 Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

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

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

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

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close