VB 6.0 with Excel 2016.

Hi,

I have an application in VB 6.0 which runs Ok on MS Office 2003 with Excel 2003.
Excel object is used in the code, but now the machine is upgraded with excel 2016 and it runs very slow on excel 2016.

Please suggest what changes I need to do in the code so that it runs fast on excel 2016?
Pranjal ShirsatAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Hello,

Depend on how big the application, I suggest you create a gig project for this.
The first thing to do is analyze where the application is eating time, and why.

There are many optimisation techniques availables, alas without seeing the source code, there is Nothing we can do.
0
Pranjal ShirsatAuthor Commented:
I am getting slowness while converting .csv file to .xls

Please suggest.
0
Fabrice LambertFabrice LambertCommented:
Still can't answer.

We don't know how your application is coded.
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Pranjal ShirsatAuthor Commented:
I can provide you the application code. But not the input to the application since it is sensitive information of our client.
0
Fabrice LambertFabrice LambertCommented:
At least the part converting csv to xls....
0
Pranjal ShirsatAuthor Commented:
I have provided the code in attached file.
Please check and suggest.
Code.txt
0
Fabrice LambertFabrice LambertCommented:
Well, you cmdCreateTool_Click() function do too much things.
By respect to SRP (Single Responsibility Principle), You should split it into smallers functions that do only one thing (and do it well).

Other things I noticed:
Avoid refreshing in loops.
Instead of opening your CSV and transfert its data cells by cells into an excel workbook, open it directly with Excel and save it in xlsx format.
Excel is perfectly able to handle csv files.

Or, load your CSV data into a 2D array, then transfert the data directly to your worksheet with a single line, as shown in the code snipet below:
Dim data() As Variant
Redim data(0 to 3, 0 to 3)
Dim i As integer
Dim j As Integer

    '// fill up the array
For i = 0 To 3
    For j = 0 To 3
        data(i, j) = i + j
    Next
Next

Dim app As Object
Set app = createObject("Excel.Application")

Dim wb As Object
Set wb = app.workbooks.add

Dim ws As Object
Set ws = wb.Workseets(1)

Dim rng As Object
Set rng = ws.Range("A1")
    '// ensure the target range is of he same size as the data array
Set rng = ws.range(rng, rng.offset(ColumnOffset:=Ubound(data, 1), RowOffset:=Ubound(data, 2))
    '// transfert data to the worksheet
rng.value = data

    '// cleanup ect ...
Set rng = Nothing
Set ws  Nothing
wb.saveAs "c:\...................."
wb.Close
Set wb = Nothing
app.Quit
Set app = Nothing

Open in new window

0
Pranjal ShirsatAuthor Commented:
Thank you for reviewing the code. I will check and implement your suggestions and get back to you.
0
Pranjal ShirsatAuthor Commented:
Hi,

Before working on the above suggestion I would like to give 1 observation, the application with 2016 Excel reference is working Ok on Excel 2003 but working slow on Excel 2016.
So if the issue is with coding then  how it is working fast on 2003 excel?
I think the issue is something different.

Can you suggest any other solution to this?
0
Fabrice LambertFabrice LambertCommented:
Depend on your workbook structure, maybe you can query the workbook.
See sample with the attached files.

As for performances issues, I'm not sure it is VBA related (I don't see any reason).

Did you test on a 2nd computer with Office 2016 ?
Test.xlsm
data.xlsx
0
Pranjal ShirsatAuthor Commented:
Please tell me how I can query the workbook using the files given by you?
0
Pranjal ShirsatAuthor Commented:
The issue got resolved when I opened the template .sam file, which is being used in the application, in Excel 2016 and saved it as Excel 97/2003 .xls file.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pranjal ShirsatAuthor Commented:
After R & D it is working Ok, when file saved in Excel 2016 as .xls Excel 97/2003 workbook.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.