3

I have a few applications writting in legacy VB6 that have extract code built into to create EXCEL extracts. We are moving from Excel 2007 to Excel 2013 and I've run into some issues. I'm running Windows 10. In 2007, this is my declarations and Set statements that used to work just fine...

Option Explicit
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheetA As Excel.Worksheet

These I have at the top of the module.

In my Function that writes the data to extract I do this...

Set xlApp = New Excel.Application
Set xlBook = XlApp.Workbooks.Add
Set xlSheetA = xlBook.Worksheets.Add

When it gets to XLBook - it hangs up and everything stops working. I get an error:

This action cannot be completed because the other program is busy. Choose "SwitchTo' to activate the busy program and correct the problem.

Now, before I run my extract I make sure Excel is not running in the background, to check I go to Task Manager -> processes and delete it if it's there. What am I doing wrong? how should I change my code?

EDIT SCREENSHOTS: The screensnhots do not display the code that i have in my application. this was a new project just to show all the code I'm using. code error

BobSki
  • 1,531
  • 2
  • 25
  • 61
  • Interestingly, Access doesn't give me any issues. I opened the application and ran a few reports in access - 2013 and everything went just fine. – BobSki Sep 06 '16 at 18:56
  • 2
    Just to clarify, the code is VB6 code in a VB6 app, referencing the Excel object model? If that's the case then I think the [vba] and [excel-vba] tags are confusing things. – Mathieu Guindon Sep 06 '16 at 19:17
  • Also, it doesn't seem right that `xlApp`, `xlBook` and `xlSheetA` are module-scoped and assigned in a function's scope. A `Function` shouldn't have side-effects, and yours is changing global state (well, module state anyway). leaving `xlSheetA`, `xlBook` and `xlApp` with dangling object references after the function exits. Also - just a coding style minor point, but using `Private` instead of `Dim` for declaring module-level variables would make it clearer that you're referring to private fields. – Mathieu Guindon Sep 06 '16 at 19:22
  • @Mat'sMug - VB6 and VBA is exactly the same code - especially when i'm writing data to extract in VB6 - i run macros and paste it into VB6. I'm declaring xlApp, xlBook, xlSheetA at a modular level, at the end of the function before I exit it - i set it to NOTHING in each case. It works perfectly fine in 2007 - but i get the error in 2013 – BobSki Sep 06 '16 at 19:24
  • The difference between VB6 and VBA is that in VBA you would have a host application and a global `Application` object variable waiting to be used - your answer would simply be "use the global `Application` object instead of making a new instance". VB6 only *looks* like VBA. If your code is VB6, tag with VB6; if it's VBA, tag with VBA. As for your variables, if they only live while the function is running, then they should be scoped to that function. Are you calling `.Quit` or just setting it to `Nothing`? – Mathieu Guindon Sep 06 '16 at 19:26
  • @Mat'sMug - i save it, set it to nothing then close it. My bad - i just assumed since it's excel related VBA experts might give me a hint or two. – BobSki Sep 06 '16 at 19:28
  • 2
    How do you close it after you set it to `Nothing`? – Comintern Sep 06 '16 at 19:32
  • 4
    Try setting `xlApp.Visible = True` right after `Set xlApp = New Excel.Application` and see if Excel is getting hung up on some pop-up/dialog. – Tim Williams Sep 06 '16 at 19:35
  • 1
    @Comintern - i don't - i save it, then close it , then set to nothing – BobSki Sep 06 '16 at 19:36
  • @TimWilliams - same thing - i think it doesn't like the SET xlAPP = New Excel.Application because it hangs up every time whatever the code after it – BobSki Sep 06 '16 at 19:39
  • @Bobski Does Excel do anything when you start it manually? Do you have any autoloading addins? – GSerg Sep 06 '16 at 19:42
  • @GSerg - when i Open it - it shows me Recent Documents, and lets me choose blank document, academic calendar, etc – BobSki Sep 06 '16 at 19:43
  • If you put a breakpoint on the `Set xlBook = XlApp.Workbooks.Add` and wait 5-10sec before continuing, does it still error ? – Tim Williams Sep 06 '16 at 19:45
  • @TimWilliams - I get the same error "Component Request Pending" This action cannot be completed because the other app is busy...... – BobSki Sep 06 '16 at 19:47
  • 1
    Maybe I need to tell it to open the spreadsheet right away - because when I initially open it it goes to RECENT documents - so it cannot add a workbook, just a thought? – BobSki Sep 06 '16 at 19:50
  • 1
    I had a similar problem with that start screen, but it was a MailMerge problem the other day where Word's DDE would fail to interact with a new installation of Excel 2013 because of the Start Screen in Excel 2013. I had to uncheck the *Show the start screen when the application starts* option for Excel. (which you should do anyway). – ThunderFrame Sep 06 '16 at 20:12
  • I've also seen problems with GetObject/CreateObject not returning *any* version of Excel if I use `"Excel.Application"`, if more than one version of Excel is installed, and one of the versions is Excel 2013 or later. – ThunderFrame Sep 06 '16 at 20:16
  • @ThunderFrame - I unched Start screen - same issue. In regards to 2nd comment - I only have one version of excel on my box. Is there a way for me to specify how to createobject in 2013 only – BobSki Sep 06 '16 at 20:20
  • Yes, you can append the version as a suffix. Eg. `"Excel.Application.15"` – ThunderFrame Sep 06 '16 at 21:19
  • You don't have to, VB6 will create the excel application as specified in your project reference. Unless you want to use createobject("") in which case I prefer to use version free "Excel.Application" – Jules Sep 06 '16 at 22:14
  • ahhhhhh nothing seems to work - don't know what my other options are. – BobSki Sep 07 '16 at 13:23

2 Answers2

2

Your function1 is in a private sub, should be called as a function. Your code shown and the screenshots also differ. I have created a quick sample and it works perfect in Excel 2013. Changed the sub to function...

Option Explicit

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheetA As Excel.Worksheet

Private Function function1()

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheetA = xlBook.Worksheets.Add

Set xlSheetA = xlBook.Sheets.Item(1) ''the sheet you want to use

xlApp.Visible = True

xlApp.ActiveSheet.Cells(1, 1).Value = "TEST"

xlApp.Workbooks.Close

MsgBox "Excel Done"
End Function

Private Sub Command3_Click()

Call function1
End Sub

Hope this helps.

Edit: See screenshot below, working fine...

Open Excel

AlwaysConfused
  • 450
  • 4
  • 13
  • 1
    Also make sure you have the newest Microsoft Excel Object Library referenced, think you mentioned you only have 1. – AlwaysConfused Sep 07 '16 at 17:09
  • 1
    Just for a bit of clarification, the switch error is related to calling another sub (not function) whilst the current sub is still trying to execute some processes or commands. – AlwaysConfused Sep 07 '16 at 17:15
  • well the first part of the code is from my actual application, the screenshots are from me doing a brand new project - displaying exactly what's going on - its easier for me to do it this way since my code inthe application is in different places – BobSki Sep 07 '16 at 17:20
  • ur saying this works on your machine? What SP do you have? 6? – BobSki Sep 07 '16 at 17:23
  • can you please tlel me which references you have checked in your IDE – BobSki Sep 07 '16 at 17:26
  • 1
    I'm using SP6, referenced Microsoft Excel 16 Object Library. The exact code above opens Excel, "TEST" is in col/row 1 and it wants to close asking me to save the workbook. Are you getting any other erros or still the same? – AlwaysConfused Sep 07 '16 at 17:27
  • i have Microsoft Excel 15 Object Library - but i'm getting the error – BobSki Sep 07 '16 at 17:28
  • 1
    See screenshot above, works fine... Even with object 15 it should work fine. Try to run it exactly the same as my code and see if it works. – AlwaysConfused Sep 07 '16 at 17:35
  • It doesn't work :( -- but i'll accept this as answer as it obviously works on your machine. I'll have to reinstall vb6 studio and then go from there. It looks like there was an issue with installing it, and that's where the root cause is. Thank you! – BobSki Sep 07 '16 at 17:37
  • 1
    Are you running VB6 as administrator? On Windows 7 on it will crash if not used as admin. – AlwaysConfused Sep 07 '16 at 17:39
  • Im running Windows 10 - and yes, VB6 is run as administrator – BobSki Sep 07 '16 at 17:57
  • Wait you have windows 7? I hope that's not where the issue is – BobSki Sep 07 '16 at 17:58
  • 1
    Nope, running Win 10 :). It is a long process re-installing VB6 again, not sure why this won't work on your side. Just tested on Win 8.1 and Win 7 on my side, worked through all of them. – AlwaysConfused Sep 07 '16 at 18:08
  • You da best. Thanks a bunch - I know but will need to reinstall again with all the SP's. Thanks again! – BobSki Sep 07 '16 at 18:09
  • I tried reinstalling VB6 again and i keep getting the same issue. How did you do your installation - did you install VB6 first and then office 2013 or the other way around? Did you have to do anything unusual to get them both to work. We've reinstalled VB6 and office 2013 to no avail. – BobSki Sep 09 '16 at 16:42
  • Nope, my excel and vb6 is installed normally, normal updates in place etc. Show me a screenshot of your references in vb6 please. – AlwaysConfused Sep 10 '16 at 11:10
  • So it looks like theres some sort of issue with office 2013 because we installed office 2007 on windows 10 and everythimg ran fine. Looking firther into it we realized that 13 has some issues. – BobSki Sep 11 '16 at 18:38
1

Long back I had similar issue. Not sure if this relates to your scenario. My code was updating an opened excel which had a cell in edit mode. After trying many possibilities, I turned off screen updating and visibility until code finished. Something like xlApp.ScreenUpdating = False xlApp.Visible = False

Mukul Varshney
  • 3,131
  • 1
  • 12
  • 19
  • this unfortunately doesn't help it. I see what you are doing. The issue here is that once it hits the line set xlapp = new excel.application with office 2013 - it doesn't give it a value, in 2007 when i run it and it hits that line - xlAPP become "MICROSOFT EXCEL" when I hover over it with the mouse, it 2013 it doesn't say anything. – BobSki Sep 07 '16 at 16:52