?
Solved

Losing Column Formatting when Custom Export from Access Table to Excel

Posted on 2011-10-31
17
Medium Priority
?
237 Views
Last Modified: 2012-05-12
I have a function that I've been working on and using in a couple of Access DB projects.  I noticed that the function makes a nice Excel spreadsheet, but loses the column formatting.  For example, some of the number columns are converted to a date of 1/1/1900 and other number and date mishaps.  I've spent a few weeks trying to figure out how to dynamically format the columns to make them the same format as in the table, but haven't been successful.  

I've also been trying to delete the sheet that the program creates and have not been successful in that either.  

With the program attached, you can import and transpose data.  Then the data is added to the same excel sheet with _trans added to the name.

Any thoughts?
SampleData.xls
Sample.mdb
0
Comment
Question by:atljarman
  • 8
  • 5
  • 3
16 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 37059935
Try adding a text qualifier around your number and data data when you export from MSAccess.  

This isn't a problem, per se, it's just Excel trying to decide what the data is.
0
 

Author Comment

by:atljarman
ID: 37060094
Not sure what you mean.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 37060160
When exporting from MSAccess you can specify certain setting relating to how it formats a csv file.  Most often, people specify the delimiter, like a comma, between fields.  You can also specify a text qualifier which says, when fields are output they get wrapped in single quotes, double quotes, etc.

So, your csv goes from looking like this...

field1,field2,field3

...to...

'field1','field2','field3'

It may help Excel determine you're sending a textual value and not just numbers.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:atljarman
ID: 37060243
Thanks cmangus.  Have you tried the file by chance?  The file actually exports an excel spreadsheets then formats it while it is open.  I am wondering if there is a way with VBA to detect the query or table column formats then to set the formats in the excel spreadsheet dynamically while it is open.  I would like to use this function for a variety of programs, so I would need to identify the field each time.  It would be great if I could do that automatically.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 37064606
I don't have an answer for you on the dynamic formatting of the columns in Excel, but I suspect it is possible, assuming you can always identify the particular column by either reference or name.
0
 

Author Comment

by:atljarman
ID: 37064898
Thanks Cmangus.  they have to prevent the q from closing before I can assign points.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37065356
test this



SampleRev.mdb
0
 

Author Comment

by:atljarman
ID: 37065636
Its coming up with Ghost instances of Excel.  Additionally, you can't overwrite a sheet that is already in the DB.  Any thoughts?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37065656
<you can't overwrite a sheet that is already in the DB> what sheet in the DB ?
0
 

Author Comment

by:atljarman
ID: 37066736
Sorry... just to clarify.  I can't overwrite the excel spreadsheet if it exists.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37066748
ok.. what do you want to do if the excel workbook exists ?
0
 

Author Comment

by:atljarman
ID: 37068850
Write over it or create a new one with the current date.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37069147
you said
<I can't overwrite the excel spreadsheet if it exists.>
then you said
<Write over it ... > 

where are we going here  ???
0
 

Author Comment

by:atljarman
ID: 37069281
For example, if the data is updated in the source sheet, say Sheet4.  The choose a measure, say Impact.  If they already have a Impact_trans in the same spreadsheet, there is an error.  I would like to either overwrite it as stated in the question, but with a prompt so that the user knows that they are overwriting the old data.

The data from this transposing will be run in an Excel macro that creates charts for the user.  The user might want to keep the old chart, update the data, then create  a new set of data for comparison.  That's why I was thinking possibly appending the current date to the end of trans?  So in the example above you might have Impact_trans as the old data and Impact_trans11.2.11 as the second sheet.

Sorry for not clarifying sooner.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37069502
did you try the sampledb i posted ( re:formatting of the excel sheet)
from your excel file sampledata.xls, delete first the sheet "Entering_Min_trans" before running the codes from the form..
0
 

Author Comment

by:atljarman
ID: 37073709
Capricorn1, yes.  You've made it so the columns are formatted correctly.  The mdb I submitted already added a sheet if it did not exist.  What is needed is to either delete the sheet if it exists or rename it so that it is a new unique sheet with the variable name,_trans, and the date for example.  This won't work of course if there is more than one update per day.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

864 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