Tools for turning highly formatted text files into Excel Reports

[Tesla]

Question about your products: Do you have a suggestion for turning highly formatted text files into Excel Reports with subtotal formula inserted?

We have used SQL Server to make very highly formatted accounting reports.
They include the title, the data in specific sorted column order, and even added a field on the Right - that indicates a Sub Total and Report Total.
The goal is to create VBA (in MS Access or Excel) that reads the output text report (from SQL Server) and create a spreadsheet.
The spread sheet for example will recognize a change in the first column category, insert a line with bold bar and insert the Sub Total Excel Formula.
The outline function will allow expansion or collapse of these categories.
the Final Total will add up all of the Sub Totals.
Naturally, there will be different numbers of Row items for each month's.
Needless to say, the text for Currency would need to be converted to numbers and all of the usual.
Note: this becomes code for a monthly routine for around 100 different reports.

I have been programming Excel using Remote Automation since 1997. However, it just seems that there would be some tools.
While I can build all of this over time, any tool to purchase would be a huge time and schedule savings.
It just seems there should be something out there?
Convert Text to Excel formulas on google, just brings up all of the worthless convert excel to web dashboards.

Accounts and budget managers want Excel with formulas on the sub totals (so they can do what-if) with all of the other features.
The SQL Server uses multiple SQL DB / Oracle Linked Servers to put the whole picture together.
The SQL out put formats are highly programmed to be converted into Excel reports.
If you have any suggestions, please post them.j

 

[Smitty]

Have you taken a look at PowerQuery? It's perfect for converting/importing data and works great with SQL Server data. Then you can use PivotTables for your reports.

 

[Tesla]

Thanks for the suggestion, looked into it. Looks like an interesting tool!

In my case, all of the Crosstab queries and Unions have been done in SQL Server.
The view is ready for automated copy recordset, paste into a new Excel Worksheet.
It has the Headers already named on the first row.
Simple Example:

Mo Region Sales
01 South 100
01 South 100
01 South 100
01 North 100
01 North 100
02 South 200
02 South 300
03 North 500

the automation I plan to write will basically group and add formulas

The final code will read a specific SQL View - then format it, formatting the header, insert the sub totals and total, add the additional format dressing (underlines, bold, ...). It will run as a batch file, create the Excel workbook, copy data, then save the reports as into network folders for later distribution.
The Sub Totals and Grand totals need to be Excel formulas, not just text.

It just seems as if there would be some libraries or tools for sale. Or, am I the only one out there doing such a thing?
The customer doesn't want share point. It is a useful tool for some things. This is for generating reports from very complex BI requiring extensive T-SQL with non-relational data sources. So, the BI is ready for one view per Excel report.

Mo Region Sales
01 South 100
01 South 100
01 South 100
01 South Sub 300 <- These will be Excel Formulas for What-If
01 North 100
01 North 100
01 North Sub 200
02 South 200
02 South 300
01 South Sub 500
03 North 500
03 North Sub 500

Grand Total 1,500

 

[Smitty]

I PivotTable will do what you want, no formulas involved. Just format as desired when you're done.

Take a look at: Create a PivotTable.