output results to excel spreadsheet 1

[maswien]

I wrote VBA code to output the query results to the spreadsheet, there are more 6751 rows of results and 16 columns, the output process is very slow, I think it's
because the results is too big for Excel.

Is there any way to make it faster?

Thanks in advance.

 

[maswien]

It's seems not that inviting to get answered.

I want change the question to:

What's the fastest way to write to a worksheet?

Thanks

 

[brucegn]

what you have posted is not too much for Excel to hold, without seeing your query or how you are putting into Excel it will be difficult to determine where the process is getting bogged down. A few things you can try though is to set Application.ScreenUpdating to False at the very beginning and also set Calcualation to Manual. If you have some formulas excel is trying to calculate at every change in the cell.

So your first lines of code should read

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'code to run
'
'
Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual
Application.CalculateFull

You can use the calculation methods at sheet level as well

 

[fredericofonseca]

Agreed.

And in order for us to give you a better answer, it would be nice to see how you are doing your updates at the moment, and eventually changing it slightly may be enough.

SO.. Please post your CODE.


And if your data is the result of a SQL (ADO/ODBC) query, then you may be able to do it automatically by using the CopyFromRecordset method

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[maswien]

I tried put those lines of code into my code, the VBA
keeps pop up follow message:

Excel is waiting for another application
to complete an OLE action.

and VBA didn't write anything to the worksheet.

Following is my code:


Set ObjWB = AppXls.Workbooks.Add
For shtnum = 1 To maxsheets
Set xlsht = AppXls.Sheets(shtnum)
cmd.CommandText = Qy(i)
cmd.CommandType = adCmdText
cmd.CommandTimeout = 120
Set cmd.ActiveConnection = Conn
Set rs = cmd.Execute()
Do While Not rs.EOF
For i = 1 To rs.Fields.Count
xlsht.Cells(j, i) = rs.Fields(i - 1)
Next
j = j + 1
rs.MoveNext
Loop
next
ObjWB.SaveAs (outpath)
ObjWB.Close SaveChanges = True

 

[SkipVought]

Hi,

I'd recommend PULLING data rather than PUSHING data.

This can be accomplished from Excel WITHOUT CODE using Data/Get External Data - Access - open your database, find your query - [Next], [Next], [Next], [Finish]

You can have this run every time the workbook opens using settings in Data/Get External Data/Data Range Properties.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[maswien]

Thanks skipVought,

The VBA code is used to generate excel files. This excel files are kind of reports for analyzing the business problem. So everything need to be done in VBA and no manual
operation is allowed.

 

[SkipVought]

So what changes in Excel from today's report to tomorrow's?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[maswien]

Thanks fredericofonseca,

Method CopyFromRecordset indeed a lot faster than loop the resultset, but the problem is it start A1 but I need add some contents before the results. Also there will be some
format changes depend on the result, can you suggest a better way for that?

 

[fredericofonseca]

Hum. The way you have your code means that you will always have less than 65535 rows.

Use copyfromrecordset instead. A lot faster.

replace your loop
Do While Not rs.EOF
with
xlsht.Range("A1").CopyFromRecordset rs

Note that you may need to set RS to nothing before executing it again.

And add the code that brucegn mentioned, as that will speed up things also

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[maswien]

SkipVought,

This is not a everyday based report, it's generated once per year. I guess you are thinking about generating a report using 'get external data' first and update it every time when the data changed. But the problem is as I wrote to fredericofonseca that it's a formated reports, I'm not sure if 'get external data' can preserve the format of the data.

 

[fredericofonseca]

Oops posted at same time.

As you can see by my last example you can say where it starts copying. My example is on A1, but it could be anywhere.

As for formating you may always do another loop afterwards, but your code did not mentioned anything about this.

What type of changed do you need to do and when/how?


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[maswien]

Thanks fredericofonseca,

What the Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual mean?
Why they can make the process faster?

The VBA I wrote isn't completed yet, the idea is to add
subtotal for the aggregation columns, also the subtotal is customized which means it's similiar as the format of the Excel subtotal but not exactly the same. For example if
the subtotal group by column value is repeated then only display it when it first appears.

 

[fredericofonseca]

Basically everytime you change something on the excel spreadsheet it does two things.

1- redisplay the screen changed
2- recalculate every single formula on the workbooks.

So the first option prevents the update of the screen until you have loaded all data,
and the second prevents the calculations to be done.

As for your sub-totals.

Whatever you do you need to load the data first, and only then do the group/totalling.

The only exception to this would be if you had to put the data on different places from the same record set.
e.g. records with fieldx = "A" go to worksheet "A", anb fieldx="B" go to worksheet "B", or something similar that could not be handled as part of the SQL.

I have a small Excel add-in that loads several files, combines then together into a final worksheet, and after this applies heading, deletes some columns, and groups/totals by group, and also sets some Page Setup variables.
All this is done AFTER the data is loaded.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[maswien]

Thank you very much Frederico! That makes a lot sense.

I wrote a very lengthy VBA code for subtotal(not using the excel subtotal), the format is exactly what we want, but it's not robust because the subtotal group by columns could be changed, for that I need to amend my code, so I decide to throw out my subtotal code and use Excel subtotal functionality, I'm not familiar with that, I guess I need to record the macro to customize the format of the report after being subtotaled.