Processing
 

VB6 extracts in Excel 2013

06/09/2016 18:52#1

Bobski

Member

Joined at: 4 months ago

Post: 1

Thank: 0

Thanked: 0

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. VB6 extracts in Excel 2013 VB6 extracts in Excel 2013

07/09/2016 17:07Top#2

Joined at: 4 months ago

Post: 1

Thank: 0

Thanked: 0

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

07/09/2016 16:34Top#3

Joined at: 4 months ago

Post: 1

Thank: 0

Thanked: 0

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

Similar articles

Excel More conditions in one formula?

4 months ago - Reply: 2 - Views: 171

Get Workbook reference from Open File dialog

4 months ago - Reply: 2 - Views: 128

Add Calculated Field To Pivot Table

4 months ago - Reply: 0 - Views: 5

Nested IF Functions in Excel not working

4 months ago - Reply: 3 - Views: 163

Formulating Date in Excel 2013

4 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

5 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

5 months ago - Reply: 1 - Views: 19

excel search and copy in/from another file

5 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

5 months ago - Reply: 0 - Views: 4

Excel static conditional formatting area

5 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re