Clicky

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
    • 29 KB

    Sample data to be imported into the Sample.mdb below

  • Sample.mdb
    • 5.7 MB

    Transpose function database. formatexcelsheet is the function that formats the spreadsheet.

asked 10/31/2011 07:01

atljarman's gravatar image

atljarman ♦♦


16 Answers:
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.
link

answered

cmangus's gravatar image

cmangus

Not sure what you mean.
link

answered 2011-10-31 at 15:31:27

atljarman's gravatar image

atljarman

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.
link

answered 2011-10-31 at 16:23:15

cmangus's gravatar image

cmangus

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.
link

answered 2011-10-31 at 16:46:48

atljarman's gravatar image

atljarman

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.
link

answered 2011-10-31 at 17:23:11

cmangus's gravatar image

cmangus

Thanks Cmangus.  they have to prevent the q from closing before I can assign points.
link

answered 2011-11-01 at 10:18:43

atljarman's gravatar image

atljarman

test this


link

answered 2011-11-01 at 10:51:36

capricorn1's gravatar image

capricorn1

Its coming up with Ghost instances of Excel.  Additionally, you can't overwrite a sheet that is already in the DB.  Any thoughts?
link

answered 2011-11-01 at 11:50:17

atljarman's gravatar image

atljarman

<you can't overwrite a sheet that is already in the DB> what sheet in the DB ?
link

answered 2011-11-01 at 12:24:39

capricorn1's gravatar image

capricorn1

Sorry... just to clarify.  I can't overwrite the excel spreadsheet if it exists.
link

answered 2011-11-01 at 12:27:40

atljarman's gravatar image

atljarman

ok.. what do you want to do if the excel workbook exists ?
link

answered 2011-11-01 at 15:30:22

capricorn1's gravatar image

capricorn1

Write over it or create a new one with the current date.
link

answered 2011-11-01 at 15:32:20

atljarman's gravatar image

atljarman

you said
<I can't overwrite the excel spreadsheet if it exists.>
then you said
<Write over it ... >  

where are we going here  ???
link

answered 2011-11-02 at 03:36:24

capricorn1's gravatar image

capricorn1

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.
link

answered 2011-11-02 at 04:50:04

atljarman's gravatar image

atljarman

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..
link

answered 2011-11-02 at 05:18:36

capricorn1's gravatar image

capricorn1

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.
link

answered 2011-11-02 at 05:55:19

atljarman's gravatar image

atljarman

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×60
×89
×1
×83

Asked: 10/31/2011 07:01

Seen: 254 times

Last updated: 12/15/2011 09:59