Solved

saving cell format in excell sheet

Posted on 2014-12-15
23
225 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
0
Comment
Question by:NiceMan331
[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
  • 12
  • 7
  • 3
  • +1
23 Comments
 
LVL 36

Expert Comment

by:Kimputer
ID: 40500028
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).
0
 
LVL 85
ID: 40500044
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.
0
 

Author Comment

by:NiceMan331
ID: 40500122
i'm not using any code for the export . i'm just using ms access wizard to export query output to ms excell
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 40500332
will the headers of the excel file be always the same ?
if it is, you can simply export the information to excel using the CopyFromRecordset function

Sub export2XL()
dim rs as dao.recordset, xlObj as object
set rs=currentdb.openrecordset("yourQuery")

set xlObj=createobject("excel.application")
      xlObj.workbooks.add <path to template>\yourtemplate.xltx"
      xlobj.range("A2").copyfromrecordset rs
      xlObj.activeworkbook.saveas "c:\mydocuments\newFile.xlsx"
     xlObj.quit
rs.close
end sub
0
 
LVL 85
ID: 40500363
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.
0
 

Author Comment

by:NiceMan331
ID: 40500770
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 ?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40500852
<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
0
 

Author Comment

by:NiceMan331
ID: 40500934
<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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40500966
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
0
 

Author Comment

by:NiceMan331
ID: 40502104
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 ?
0
 

Author Comment

by:NiceMan331
ID: 40502141
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
0
 
LVL 85
ID: 40502282
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.
0
 

Author Comment

by:NiceMan331
ID: 40502397
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40502526
upload a copy of your template and a copy of the db.
0
 

Author Comment

by:NiceMan331
ID: 40503128
ok
tomorrow i will
thanx
0
 

Author Comment

by:NiceMan331
ID: 40504526
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
http://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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40504540
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"
0
 

Author Comment

by:NiceMan331
ID: 40504556
Ok،I will
0
 

Author Comment

by:NiceMan331
ID: 40506664
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
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40506816
Sub export2XL()
dim rs as dao.recordset, xlObj as object
set rs=currentdb.openrecordset("yourQuery")

set xlObj=createobject("excel.application")

 'open existing excel file
      xlObj.workbooks.open "c:\folderName\MyExcel.xlsx"
'show the open excel
      xlObj.visible=true
'select the sheet
      xlObj.worksheets("NameOfSheet").select
'copy the recordset
      xlobj.range("A2").copyfromrecordset rs
'save the workbook
      xlObj.activeworkbook.save
     xlObj.quit
rs.close
end sub
0
 

Author Comment

by:NiceMan331
ID: 40506822
great
i think it will be helpful
i will try it
thanx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40506828
how about the sample data for your other thread?
0
 

Author Comment

by:NiceMan331
ID: 40506832
YES OK
it is ok
saturday , i will upload the db for the other question
thanx
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

690 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