Search code examples
vbaexcelvb6excel-2013

VB6 extracts in Excel 2013

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

Solution

  • 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