Dear All,
I am trying to format the data to DMY in the excel file which is exported to excel using the transfer spreadsheet command. I am trying to format using text to columns as udner. -
.Range("AA2", xlapp.Range("AA65536").End(xlUp)).Select
-
.Selection.TextToColumns Destination:=xlapp.Range("AA2"), DataType:=xlDelimited, _
-
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
-
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
-
:=Array(1, 4), TrailingMinusNumbers:=True
-
The concern here is that the date format does to change to dmy for example 01/07/2011 post the code above changes to 07/01/2011.
request help ... Thank You in advance.
7 7676 NeoPa 32,556
Expert Mod 16PB
You need to decide first of all whether you are dealing with dates (in which case only the formatting of the cell will determine how they are displayed) or with strings that look like dates (in which case a formula would be required ="01/07/2011" or a single-quote (') would need to be used as the first character of the value '07/01/2011).
The field contains dates stored as strings/text. Also, i tried the text to columns after exporting the raw file where all dates are stored as text within excel. The conversion was as desired in dmy format. Hence am a little confused as to why the text to columns in excel does not work with the same results through access.
Hence as an alternative, can we include an excel module in the file eported through access whcih can be run on opeing the excel file.
NeoPa 32,556
Expert Mod 16PB
Vishnu, when dealing in technical matters your answers need to be clearer. "The field" can mean so many dferent things in this context that it tells me nothing. The rest of the wording tells me nothing either as it doesn't even indicate that you understand the difference between the two possibilities (I'm happy if you do of course, but I need to know it before we can proceed).
The second question is premature at this stage.
Please answer these questions clearly : - Is the table field where the date data is stored defined as Text?
- When the data is exported into Excel is the data there a string or a date value? You can determine this by selecting the cell and changing the format to another numeric/date format. String values will appear the same as before, whereas numeric/date values will change to match the new formatting.
NeoPa, I am sorry, I was not clear...
1) This is a text field. The field contains the spouse date of birth for employees. It also holds blank fields.
2)The exported data is a string.
Thank You
NeoPa 32,556
Expert Mod 16PB
That's a good clear answer.
Now what confuses me is exactly what you are doing to reformat the data. Text to Columns should work with textual data (as you have) but I cannot see how the code you've posted would be expected to change the format to DMY. Is that what you want to do? If not then please explain exactly what it is you are trying to do with the data.
I was able to fix the concern by adding the following code before executing text to columns. -
.Selection.NumberFormat = "dd/mm/yyyy;@"
-
It is working for now..
Thank you NeoPa for your guidance...I will need more of it in the future.
NeoPa 32,556
Expert Mod 16PB
Interesting. I'm not sure I follow exactly why that would result in a recognisable string value, but if it does then that takes us back to my suggestion in post #2. Perhaps you should have explored that a little more carefully before your post #3.
Not important though. You have a solution now and that's the most important thing. It's often hard to notice the answer when you're confused yourself, even when you see it.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: jt |
last post by:
I posted this yesterday, but I am not seeing this out yet:
I am having problems with updating a date field in a certain format.
The data is stored in an Oracle database. The date is...
|
by: Neil10365 |
last post by:
I am sure I must be doing something wrong but here it is:
I import an excel spreadsheet that has dates in the format mm/dd/yyy.
The receiving table in access has a date field set to mm/dd/yy,...
|
by: insomniux |
last post by:
Hi,
I am having a problem with formatting the default value of a date
field. It has been discussed earlier in many topics, but still I cannot
solve the problem. What's happening:
I have various...
|
by: krupalreddy |
last post by:
i have a problem with date
i have two dates. one is in Date type and the other one is in String type.
i need to compare these two dates.
for this i try to convert date which is in String type to...
|
by: ARC |
last post by:
Hello all,
General question for back-end database that has numerous date fields where
the database will be used in regions that put the month first, and regions
that do not.
Should I save a...
|
by: fercic |
last post by:
Hi folks !
I'm not able to get a date before 01/01/1930 formatted as short date (dd/mm/yy).
It is always formatted as dd/mm/yyyy.
Any idea?
fercic
|
by: Bface |
last post by:
Hi all,
Hope everyone had a good holiday. I am having a difficult time changing the date format of a field from Excel. I have never had this problem before. I link the excel spreadsheet to my DB,...
|
by: Dirk Hagemann |
last post by:
Hello,
From a zone-file of a Microsoft Active Directory integrated DNS server
I get the date/time of the dynamic update entries in a format, which
is as far as I know the hours since january 1st...
|
by: accessvbanewbie |
last post by:
I would like to export a recordset from access to excel but after each record is exported I want to insert a new row.
The first recordset does this ok. However, the second recordset onwards does not...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |