atljarman
asked on
Losing Column Formatting when Custom Export from Access Table to Excel
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
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
ASKER
Not sure what you mean.
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.
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.
ASKER
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.
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.
ASKER
Thanks Cmangus. they have to prevent the q from closing before I can assign points.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Its coming up with Ghost instances of Excel. Additionally, you can't overwrite a sheet that is already in the DB. Any thoughts?
<you can't overwrite a sheet that is already in the DB> what sheet in the DB ?
ASKER
Sorry... just to clarify. I can't overwrite the excel spreadsheet if it exists.
ok.. what do you want to do if the excel workbook exists ?
ASKER
Write over it or create a new one with the current date.
you said
<I can't overwrite the excel spreadsheet if it exists.>
then you said
<Write over it ... >
where are we going here ???
<I can't overwrite the excel spreadsheet if it exists.>
then you said
<Write over it ... >
where are we going here ???
ASKER
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.
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.
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..
from your excel file sampledata.xls, delete first the sheet "Entering_Min_trans" before running the codes from the form..
ASKER
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.
This isn't a problem, per se, it's just Excel trying to decide what the data is.