Visual basic 6 - Export to Excel 2010/2013 not working

Question

1.44/5 (2 votes)

See more:

Hi Friends,

I am stuck with this issue and need your help.

We have one VB6 application that reads an Excel file (basically like a template) and writes data into specific cells finally exports it to a xls.

The problem is that the excel sheet is not opening in office 2013 version. When I open the file, everything is blank. But the file size indicates that data got written into it. Moreover , the document is opening in Google docs but not in office 2013 version.

Below please find my code for it. (actual code is too long therefor can't paste it here).

VB

Set xlApp = Excel.Application
   Set xlBook = GetObject("file.xls")
   Set xlSheet = xlBook.Worksheets(1)
   Set xlsheet2 = xlBook.Worksheets(2)

 --logic

   xlBook.SaveAs output.xls

(Please note: I am using Windows 7 - 64 bit system. I am using all required DLL)

Any help would be appreciated. Thanks a lot in advance!!

Posted 11-Apr-14 9:24am

Joy1979

Add a Solution

Comments

Maciej Los 11-Apr-14 15:54pm

Please, provide more details about the way you declare variables...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-04-11T11:32:00

You did not provide enough information about your issue, especially about the reason of that behaviour. You did not provide your code too. Well, we can only guess...

First of all, i'd suggest to read about Using early binding and late binding in Automation[^], Early and Late Binding (Visual Basic)[^].

Once you are using:

VB

Set xlApp = Excel.Application 'early binding

and then

VB

Set xlBook = GetObject("file.xls") 'late binding

You need to decide which binding method do you want to use.

Secondly, you need to use Excel's object and methods:

VB

Dim xlApp As Object
Dim xlWbk AS Object
Dim xlWsh AS Object

Set xlApp = CreateObject("Excel.Application")
Set xlWbk = xlApp.Workbooks.Open("FullFileName.xls")
Set xlWsh = xlWbk.Worksheets("SheetName")

With xlWsh
    .Range("A1") = "Test"
    .Range("C3") = 3
End With

'...

'finally, clean up:
xlWsh = Nothing
xlWbk.Close SaveChanges:=True
xlWbk = Nothing
xlApp.Quit
xlApp = Nothing

Thirdly, there are known issues with MS Office 2010/2013:
Compatibility issues in Office 2013[^]
VB6 based add-ins may fail to work in Office 2013[^]

Finally, please refer this:
Essential Training for Visual Basic 6 Developers[^]

By The Way: I'd like to say: who wants to use VB6, when VB.NET is more powerful, efficient, etc.?