We help IT Professionals succeed at work.

saving cell format in excell sheet

297 Views
Last Modified: 2014-12-18
hi
i'm posting my sales transactions in ms access
but i need to print a summary in excell sheet daily basis
i format one excel sheet ( borders , colours , fonts ,, etc ) to be ok for the print out
then , i have  to export from ms access query to the  excell sheet
but always i have to again adjust the format of the excell sheet
is there any way or any macro to save the format of excell sheet
Comment
Watch Question

KimputerIT Manager
CERTIFIED EXPERT

Commented:
This really depends on a LOT of factors, and you need to provide a lot of details (how/what/who is the export done? what happens with your layout after it is imported? What changed to what ? etc etc)
For now, my gut tells me you should import it to a temp excel file, and then load this temp excel file into an the excel file WITH the correct layout, and macro (which only imports the text from the excel file, as text shouldn't adjust your layout except for normal boundaries).
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
You could save that Excel file as a Template, and then use the Template when creating a new Excel file for your report. I'm not sure how you're creating the Excel file, so can't help much more with that. If you could post code that shows what you're doing, we might be able to offer more specific advice.

Author

Commented:
i'm not using any code for the export . i'm just using ms access wizard to export query output to ms excell
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
i'm not using any code for the export
Then you'll need to change your process. As Rey has shown, you can use CopyFromRecordset.

Author

Commented:
will the headers of the excel file be always the same ?
yes



     
xlObj.workbooks.add <path to template>\yourtemplate.xltx"
you mean the pre formatted sheet ?

   
 xlobj.range("A2").copyfromrecordset rs
shall i determine A2:M20 if my templete in this range ?

   
 xlObj.activeworkbook.saveas "c:\mydocuments\newFile.xlsx"
which new file ?
CERTIFIED EXPERT
Top Expert 2016

Commented:
<which new file ? > new file is the name you will give to the excel file with the new records

<shall i determine A2:M20 if my templete in this range ?> No need to determine, since your columns does not change, last column is M, you can delete the contents with

.range("A2:M100").clearcontents

xlobj.range("A2").copyfromrecordset rs  >>> will copy the whole recordset starting at A2


<you mean the pre formatted sheet ? > yes, normally you should save this  fiel as a template, extension  .XLTX

Author

Commented:
<which new file ? > new file is the name you will give to the excel file with the new records
sorry i confused here  , you mean this code will copy the query to a new file first ? ,or directly to my templete ?
sorry again
CERTIFIED EXPERT
Top Expert 2016

Commented:
when you open the template, you will create a new workbook
the records will be copied to the new workbook, then you have to save the workbook with this code

  xlObj.activeworkbook.saveas "c:\mydocuments\newFile.xlsx"

to the folder c:\mydocuments (which you can change)
and with a excel filename newFile.xlsx, newfile.xlsx could be any name you want to be the name of the newly created excel file

Author

Commented:
i have 2 issues here
1- i designed the templete as what i need
2-when it copied , the columns not copied in same order of the query , because some columns formatted as date , some as number ,, etc
i want to investigate first the normal copy ( without templete ) to see is will copied in same column order or not , what is the code will be ?

Author

Commented:
oh , the mistake in my query itself
it is ok now for ordering
remaining 2 things :
1- the raw height in the new book not coming as same the templete
2-some clomuns only which formatted as number , not coming in same format
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
1 - What do you mean by "raw height"?

2 - Did you set the formatting in the Column in your Excel template? If not, please explain further, with examples.

Author

Commented:
1 - What do you mean by "raw height"?
sorry , row height

Did you set the formatting in the Column in your Excel template?
yes i did
for example one column formatted as number with 2 dicimal places , then after copy , it becomes with more than 2 dicimal places , where i have to adjust it manually
CERTIFIED EXPERT
Top Expert 2016

Commented:
upload a copy of your template and a copy of the db.

Author

Commented:
ok
tomorrow i will
thanx

Author

Commented:
give me chance till tomorrow also , because i'm trying to adjust the format of my db
if not success , i will send it
by the way , if you please answer my other question
https://www.experts-exchange.com/Database/MS_Access/Q_28580925.html
the solution there may be like this way , i think to copy the query to an existing excel sheet
with pre define of weeks range in excell
but i need to adjust the code there , instead of using templete , i want to copy it directly to an existing sheet
CERTIFIED EXPERT
Top Expert 2016

Commented:
i can post a solution to that thread if you can provide the information from the table, query

in short, i need a db to play with..  with the tables/query "WeekAvg"

Author

Commented:
Ok،I will

Author

Commented:
rey
i reveiewed my query , i found my mistake , i were format calculated number like this
format = standar
input mask = 0
when i removed the 0 , it become ok
now it is ok , except this 2 things
1- how to open the templete excel to adjust it in the future instead of creating new one
2-how to adjust the code , if i want to select copy the data from my query to an existing sheet ( not to craete new one with templete formatted one )

thanx
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
great
i think it will be helpful
i will try it
thanx
CERTIFIED EXPERT
Top Expert 2016

Commented:
how about the sample data for your other thread?

Author

Commented:
YES OK
it is ok
saturday , i will upload the db for the other question
thanx

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.