Convert data to number from external source and then Auto-sort data

A

aggiemarine07

New Member
Joined
Nov 5, 2013
Messages
46
Happy New Year!

I have a question for the general populace here on the forums since yall have been excellent at helping me with my many excel issues. I have yet another question to further help me automate some of our processes around the office.

I currently have a excel file that pulls data from 5 other files within the same folder. 4 of these files are fine as they are user generated and then simply inserted into the folder and in turn auto-updating the master excel file (named master.xls). However, one of these files is generated by a system and is then exported to a simple excel file (named stats.xls). The data within this excel file is automatically formatted as text. In order for it to work within the master excel file it needs to be converted to a number.

Currently, I have to manually convert the file to a number (open it up, select all, then select convert to number and finally save it).

My question is this, is it possible to have the master excel file (master.xls) pull the data from the system excel file (stats.xls) and automatically convert that pulled data to a number in order for the master.xls formulas work correctly? If it is possible what vba would I need to do it? Thanks in advance!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Not sure what you by this
select convert to number
Click to expand...
but you can try the code below...
Code: 
Sub Txt_Col()
    Dim lc As Long, i As Long
    lc = Sheets("master").Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
    For i = 1 To lc
        Sheets("master").Columns(i).TextToColumns Destination:=Sheets("master").Cells(1, i), DataType:=xlDelimited, ConsecutiveDelimiter:=False _
        , FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    Next
End Sub
 
Last edited:
Upvote 0
MARK858 said:
Not sure what you by this but you can try the code below...
Code: 
Sub Txt_Col()
    Dim lc As Long, i As Long
    lc = Sheets("master").Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
    For i = 1 To lc
        Sheets("master").Columns(i).TextToColumns Destination:=Sheets("master").Cells(1, i), DataType:=xlDelimited, ConsecutiveDelimiter:=False _
        , FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    Next
End Sub
Click to expand...

Thanks so much for your reply, I'll try it tomorrow when I go back to my office.

What I mean by convert to number is when in excel you have data in a cell and excel thinks its a number. What excel does is it puts a green triangle in the top left of the cell. When you click on that cell it pops up a yellow square with a exclamation mark in it. When you click on the box it asks you to "Convert to Number". Do you know what Im talking about? Again thanks for the vba, I'll let you know how it works sometime tomorrow.
 
Upvote 0
Do you know what Im talking about?
Click to expand...

Do now, its part of the standard error checking in later versions.
What I don't know is what they use for the underlying conversion out of the various options.
 
Upvote 0
So I was able to test your code and it did not work.

I can tell if it works or not by looking at the result of my formula's. If it doesnt work the output will be #N/A if it does work, it will compute the correct number.

Any idea why the "convert to number" is different than writing code?

Is there a way I can attach the spreadsheet to this thread so that you can see what Im talking about? Thanks.
 
Upvote 0
The master file pulls the data from the other files. That sounds like a query. If so, a solution may be to edit the query's SQL - no need for code or formulas. If there are numbers coming through as text change the SQL from fieldname to CDbl(fieldname), or fieldname*1 or fieldname+0
 
Upvote 0
=[stats.XLS]Sheet'!A1=[stats.XLS]Sheet'!B1
=[stats.XLS]Sheet'!C1
=[stats.XLS]Sheet'!A2
=[stats.XLS]Sheet'!B2
=[stats.XLS]Sheet'!C2
=[stats.XLS]Sheet'!A3
=[stats.XLS]Sheet'!B3
=[stats.XLS]Sheet'!C3

<tbody>
</tbody>

How would I go about doing that?

The master.xls file is linked to the stats.xls file like the above.

I also don't have access to the system that exports the excel data as all text.

Thanks for all yalls help on this.
 
Upvote 0
That (formulas) is not what I was expecting.

Nonetheless, the same idea may be OK. For a particular column, just edit the formula by adding *1

Say it is column B with formula
=[stats.XLS]Sheet'!B1

change to
=[stats.XLS]Sheet'!B1*1

How is that?
 
Upvote 0
I just tried that, it displays a #VALUE (error in value) within the cell.

Is there a way I can paste a portion of the stats file on here so that you can see how the stats.xls file is displaying the data?
 
Upvote 0
What about
=value([stats.XLS]Sheet'!B1)

Pasting? Mark in the initial reply has some links in the footer to the post. There is a sticky thread at the top of the Excel sub-forum. Maybe too you can just post an image (I'm not sure)?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA: Open and close all source files to refresh Master Workbook data
Replies
1
Views
442
t0ny84
T
GMC The Macro Man
  • Solved
VBA Script - Return to named file
Replies
6
Views
118
mumps
mumps
R
  • Question
VBA to pull data from closed workbooks
Replies
3
Views
460
Logit
Logit
M
  • Question
Macro - Merge all files into one sheet
Replies
2
Views
197
MountainFog
M
C
  • Question
Extracting Specific Fields from multiple PDFs with VBA (Excel 2019)
Replies
1
Views
185
chickyguy
C

Forum statistics

Threads
1,214,630
Messages
6,120,634
Members
448,973
Latest member
ChristineC

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top