[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Preserving formatting after data refresh in a table in Excel

Posted on 2016-10-04
3
Medium Priority
?
167 Views
Last Modified: 2016-10-26
I have an Excel document that pulls data from a database into an Excel table and e-mails this file to certain staff. I've set up formatting on this table so that it sorts by certain columns, has another column with currency formatting, and has data slicer filters ready to be used. However it seems that this all disappears every time there is a data refresh (which is every time task scheduler runs the command to refresh data). What are some ways that I can preserve some or all of these formatting?

I've seen suggestions that apply to pivot tables, but this isn't one, and doesn't look as good as a pivot table. I've also seen suggestions on having another worksheet reference the data, but I haven't found a good working solution yet. Another workbook and a query import probably wouldn't work since it's e-mailing the data.

(I am using Jet Reports, for those familiar with it)

Thanks.
0
Comment
Question by:ruhkus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Accepted Solution

by:
Vijay R earned 2000 total points
ID: 41828505
Hi
I guess the program to export data from Database to the excel is not written in VBA and giving my solution. Please let me know if it is not.
insert a ActiveX control button in the excel menu Developer in say "Sheet1". Right click the button and click "View code" to start the coding. Write the below lines between the "Private Sub CommandButton21_Click()" and "End Sub". This is the procedure name for mine, it may be different for you.
Range("I1:I10").Select
Selection.NumberFormat = "$#,##0.00"
if you want to have absolute number (positive value) then formula is
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
For sorting write as follows in the same procedure (Or sub)
 ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G1:G5"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("G1:G5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
0
 
LVL 8

Expert Comment

by:LajuanTaylor
ID: 41828506
@ruhkus - Have you checked the Jets Reports Support site? I'm not sure of your version or how much of their tool suite you have available, but I think there are several options. For example:
Using Report mode versus Design mode -
https://jetsupport.jetreports.com/hc/en-us/articles/219402737-Difference-between-Design-Mode-and-Report-Mode-

If you have Dynamics NAV, you might be able to resolve the formatting issue:
https://jetsupport.jetreports.com/hc/en-us/articles/219403337-Uploading-Budget-Data-to-Dynamics-NAV

Lastly,

Take a look at leveraging a report template:
https://jetsupport.jetreports.com/hc/en-us/articles/219402677-Additional-Resources-for-Jet-Essentials
0
 

Author Comment

by:ruhkus
ID: 41834339
Thanks for the feedback. I was able to get what I need by using a macro, but I was really hoping not to go that route, since as IT Manager, I'm always encouraging staff to not press that button to enable macros!

Lajuan, I looked at those links but I wasn't able to find anything I could use. Perhaps I'm too new to JET Reports, but did you see something in particular that would address my need?
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question