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

[aggiemarine07]

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!

 

[MARK858]

Is there a way I can attach the spreadsheet to this thread so that you can see what Im talking about?

That is probably is the only way we will get to the bottom of the problem.

First try Fazza's suggestion in the last post, if that doesn't work then...

With the stats.XLS file
Go to a free file hosting site like Box.com.
Upload your file.
Remember to mark the link for sharing at Box.
Post a link to the file in the thread.

 

[aggiemarine07]

That is probably is the only way we will get to the bottom of the problem.

First try Fazza's suggestion in the last post, if that doesn't work then...

With the stats.XLS file
Go to a free file hosting site like Box.com.
Upload your file.
Remember to mark the link for sharing at Box.
Post a link to the file in the thread.

That did not work either.

the following link is where you can download the excel file that when exported from our stats system for some reason puts everything in "text". this isnt the master.xls file I was talking about, its the stats.xls file.

https://dl.dropboxusercontent.com/u/6268510/RosterGrid.XLS

 

[MARK858]

The code below worked for me with your sample. Change the sheetname to suit.

Sub Txt_Col()
    Dim lc As Long, i As Long
    Sheets("Sheet").Cells.NumberFormat = "General"
    lc = Sheets("Sheet").Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
    For i = 1 To lc
        Sheets("Sheet").Columns(i).TextToColumns Destination:=Sheets("Sheet").Cells(1, i), DataType:=xlDelimited, ConsecutiveDelimiter:=False _
        , FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    Next
End Sub

 

[aggiemarine07]

so I pasted your code in my sheet and then attempted to to run the code. it comes up with a "unable to set NumberFormat Property of Range Class" error? I see that in your code is "General" should I change it to number?

Below is all the code that I currently have in the sheet, it has an auto-sort vba script in here as well as your own:

'Auto-sort code BEGIN

Private Sub Worksheet_Change(ByVal Target As Range)

Range("B6:AP805").Select
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub


'Auto-sort code END

Sub Txt_Col()
Dim lc As Long, i As Long
Sheets("DATA").Cells.NumberFormat = "General"
lc = Sheets("DATA").Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
For i = 1 To lc
Sheets("DATA").Columns(i).TextToColumns Destination:=Sheets("DATA").Cells(1, i), DataType:=xlDelimited, ConsecutiveDelimiter:=False _
, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Next
End Sub

Thanks for all your help.

 

[MARK858]

First of all the code goes in a regular module and not the sheet module. Put it there
Second have you protected the sheet? If you have unprotect it.
Third no don't change it from general, if you have carried out all the above and you still can't get it to work post a link to the actual file and I will look at it again when I get in.

 

[aggiemarine07]

Thanks for the reply, I will try your recommendations when I get back to the office on Monday.

 

[MARK858]

Seeing as you won't be looking at it until Monday (and I will be at work then during daylight hours).

Just in case it helps I have attached a link to the file you put in post #12.

In the file I have placed the macro in the correct place and added a subtle button on the sheet with an obviously instruction on the button what to do.

Hopefully this will aid you a bit.

Obviously this sheet is not protected.

http://app.box.com/s/5xc6nsgrtkwd22sxcyiv

 

[aggiemarine07]

I tried it this afternoon and I see it working and it works great however, its converting the cells in the master.xls document rather than the cells in the rostergrid.xls document.

Is it possible to create something like what you made (button, code and all) to open up the external file (rostergrid.xls) and convert those cells? I think this ultimately would accomplish what im seeking to do.

My thinking is that if I download an updated version of the rostergrid.xls file, I can simply click on a button in the master.xls file and it will convert those cells. Does that make sense? Thank you so much for working with me on this issue!

 

[MARK858]

I will see if I get time to look at this tonight but I need some information / clarification.
Can you provide the following...

1) the full filepath of the file you are calling rostergrid.xls

2) the actual name of the file rostergrid.xls (yes I know it might be the same but I want it clarified)

3) the actual sheetname/s on the file you are calling rostergrid.xls you want the code to rum on

 

[aggiemarine07]

1) the full filepath of the file you are calling rostergrid.xls

Ive been using dynamic paths in my other VBA code, can you use that here? If so, then the rostergrid.xls resides in the same root folder as the master.xls (~\master.xls ; ~\rostergrid.xls)

2) the actual name of the file rostergrid.xls (yes I know it might be the same but I want it clarified)

rostergrid.xls will be the name of the file when I export it from our data system

3) the actual sheetname/s on the file you are calling rostergrid.xls you want the code to rum on

this code will be ran on the "DATA" sheet within the master.xls document