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]

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

I probably worded the question poorly, I wanted the sheetname of the sheet in rostergrid that needs the formatting changing but you can change that (see the red font below).

dynamic paths in my other VBA code

Don't know what you mean by "dynamic". ThisWorkbook.path (which is what "same root folder as the master.xls" indicates to me) isn't dynamic it is fixed based on the current workbooks path.

The nearest you normally get to dynamic is bringing up a dialogbox.

Anyway, code below in the master workbook.

Sub getit()
    Dim wb As Workbook, lc As Long, i As Long

    Set wb = Workbooks.Open(ThisWorkbook.Path & "\rostergrid.XLS")

    With wb.Sheets("[COLOR="#FF0000"]DATA[/COLOR]")

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

    End With
End Sub

Is it possible to create something like what you made (button, code and all)

I am sure that you are capable of creating a button and assigning a macro to it on the master sheet yourself

 

[aggiemarine07]

Sweet! Code works really well!

I said dynamic path but I think the term that I meant is a relative path.

Again, thank you for all your help with this!