awilson1111
asked on
Excel - will not format cell
I am exporting(by linking)data from Access that is formatted as currency into a prepared Excel file where the target cells are also currency but the data does not show up as currency and I can't make it display as currency.
Any suggestions?
Any suggestions?
Brian Mulder
Hi Awilson1111, how is the data showing up, as general or something?
If you put your cursor in the cell and hit F2 {which is edit} and ENTER, what happens?
Does the format change?
Does the format change?
Oh... by the way. Also check for leading and trailing spaces in the cell as you edit it (them).
ASKER
When if select a cell and hit F2 the data is showing up with a leading '.
What I am doing is downloading a data file from a mainframe and my Access database is linking to that file, then a query is run that copies the data from the downloaded file(linked table) into another linked table that is a prepared Excel spreadsheet.
In the query I format the data as currency, and the "from" file the data is formated as currency. But when the data gets into the prepared Excel sheet it has the leading ' which prevent it from being formatted.
Any suggestions?
What I am doing is downloading a data file from a mainframe and my Access database is linking to that file, then a query is run that copies the data from the downloaded file(linked table) into another linked table that is a prepared Excel spreadsheet.
In the query I format the data as currency, and the "from" file the data is formated as currency. But when the data gets into the prepared Excel sheet it has the leading ' which prevent it from being formatted.
Any suggestions?
If the data is first saved in a text file, you can use the import tool (automatically when you open the file) to strip out that leading tick mark.
Just tell Excel to delimit using the tick mark.
Just tell Excel to delimit using the tick mark.
another option <but this could backfire> run a find/replace on the leading ' character
ASKER
The leading ' is not in the source file, the process (in Access) of moving the data from the source to the destination is adding the '.
bruintje - I thought of that, but the whole purpose of this process is to make it automated - just one click to download from the mainframe to exporting it from the source to the destination template - as little hands on as possible.
bruintje - I thought of that, but the whole purpose of this process is to make it automated - just one click to download from the mainframe to exporting it from the source to the destination template - as little hands on as possible.
the you could use a bit of code
in the excel sheet
-bring up the vb editor with ALT+F11
-insert a new module
-paste the code
Public Sub Convert2Num()
Dim c
Range("A1:Z1000").Select
For Each c In Selection
If c.Value <> "" Then c.Value = Val(c.Value)
Next
End Sub
-then save
-you can call this from the access export
-or you can call this in the workbook_open event but then the data has to be imported first before this is run
-of course range("A1:Z1000") can be any other range
:O)Bruintje
in the excel sheet
-bring up the vb editor with ALT+F11
-insert a new module
-paste the code
Public Sub Convert2Num()
Dim c
Range("A1:Z1000").Select
For Each c In Selection
If c.Value <> "" Then c.Value = Val(c.Value)
Next
End Sub
-then save
-you can call this from the access export
-or you can call this in the workbook_open event but then the data has to be imported first before this is run
-of course range("A1:Z1000") can be any other range
:O)Bruintje
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi awilson1111 any update on this?
ASKER
See next question - I have reopend this problem with another question.