combining data from other worksheets to master sheet 1

[LBTaurus]

Hi.. I have a bunch of data in the form of worksheets in a folder. ALL of the worksheets have the same format. The relevant data i need in each is from c6 to c307.

This folder is continuously updated with newer worksheets. Each newer worksheet added into this folder has a number that is higher than those inside the folder, although not necessarily consecutive.

I also have a master file, in which i compile the data from each worksheet.

I need a macro that when i run it, finds the newest worksheet in the folder, adds the data from c6:c307 it to the master file. each time data is added, it has to go to the next open column, and stay consistent in that each first row is the value from c6. ( i need the consistency in rows because i already have an avg macro that should run after the next column is added.)

Is there some way to do this? I've already recorded a couple of macros that do this, but each time its for a specific file and puts the data in the specific place. How do i add the "+1" clause? Should i put an if then statement or two?
I basically need the macro to scan the folder, find the files that aren't already in the master, and take the c6:c307 data from them, and put that data into the next available column.

Let me know if anything doesn't make sense.

Anything at all would help.. I'm fairly new to all of this.

 

[Remou]

The most recent file in a folder can be found using the FileSystemObject:

Function MostRecentFile(fldr)
 'Reference: Microsoft Scripting Runtime
 Dim f As File, fl As Folder, fs As FileSystemObject
 Dim fd As Date
 Dim fname As String

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set fl = fs.GetFolder("C:\Docs")
    
    For Each f In fl.Files
        If IsNull(fd) Or f.DateCreated > fd Then
            fd = f.DateCreated
            fname = f.name
        End If
    Next
    MostRecentFile = fname
End Function

 

[cowski]

This folder is continuously updated with newer worksheets.

Do you only need to add the latest file or do you need to add all those files that have not yet been added to the master? If you need all the sheets that have not yet been added it will complicate things, you would need some way to record which sheet has/has not been recorded. Also, what happens when you run out of columns in the master? Add them to sheet 2? Delete older data? Start adding data to lower rows? I realize that there are a lot of columns on a sheet, but eventually you will run out.

 

[handleman]

There really aren't a lot of columns if you are not using Excel 2007. Any earlier version has a limit of 256 columns.

-handleman, CSWP (The new, easy test)

 

[LBTaurus]

First off... Thanks guys. very helpful

Remou... where in my macro should this snippet be going? Will this be in the beginning? Also, will this just find the most recent file or files? Again, im fairly new to excel VBA.

Cowski... I'm using 2007... I think at max there will be about 40 new files a month. i believe there are 16k columns in it, so its not too huge of a concern. We also dont want to run into the problem of having too much data per worksheet, for accessability reasons, so after every hundred or so data sets we'll change to a new file.

I'll post my macro that i have as of now in a just a bit.

 

[LBTaurus]

Sub SearchAdd()
'
' SearchAdd Macro
'

'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\Users\ketan.dhawan\Desktop\Mandrel Tests\4935W01.xls;DefaultDir=C:\Users\ketan.dhawan\Desktop\Mandrel Te" _
), Array("sts;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;")), Destination _
:=Range("$U$6")).QueryTable
.CommandText = Array( _
"SELECT `Data$`.`OM-CP-PROCESS101`" & Chr(13) & "" & Chr(10) & "FROM `C:\Users\ketan.dhawan\Desktop\Mandrel Tests\4935W01.xls`.`Data$` `Data$`" & Chr(13) & "" & Chr(10) & "WHERE (`Data$`.`OM-CP-PROCESS101` Not Like '%Channel 1%' And `Data$`.`OM-CP-PROCESS10" _
, _
"1` Not Like '%Current Recorder%' And `Data$`.`OM-CP-PROCESS101` Not Like '%M93958%' And `Data$`.`OM-CP-PROCESS101` Not Like '%Proc%')" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_Excel_Files_1"
.Refresh BackgroundQuery:=False
End With
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 172
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 177
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 194
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 220
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 222
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 227
ActiveWindow.ScrollRow = 229
ActiveWindow.ScrollRow = 230
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 236
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 249
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 254
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 256
ActiveWindow.ScrollRow = 257
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 261
ActiveWindow.ScrollRow = 263
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 273
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 278
ActiveWindow.ScrollRow = 280
ActiveWindow.ScrollRow = 282
ActiveWindow.ScrollRow = 283
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 283
ActiveWindow.ScrollRow = 278
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 269
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 261
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 244
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 230
ActiveWindow.ScrollRow = 227
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 190
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 190
ActiveWindow.ScrollRow = 191
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 220
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 227
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 249
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 254
ActiveWindow.ScrollRow = 256
ActiveWindow.ScrollRow = 257
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 261
ActiveWindow.ScrollRow = 263
ActiveWindow.ScrollRow = 265
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 269
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 271
ActiveWindow.ScrollRow = 272
ActiveWindow.ScrollRow = 273
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 276
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 276
ActiveWindow.ScrollRow = 277
ActiveWindow.ScrollRow = 278
ActiveWindow.ScrollRow = 279
ActiveWindow.ScrollRow = 280
ActiveWindow.ScrollRow = 281
ActiveWindow.ScrollRow = 282
ActiveWindow.ScrollRow = 283
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 288
Range("U308:U330").Select
Selection.ClearContents
ActiveWindow.ScrollRow = 287
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 284
ActiveWindow.ScrollRow = 283
ActiveWindow.ScrollRow = 282
ActiveWindow.ScrollRow = 279
ActiveWindow.ScrollRow = 276
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 271
ActiveWindow.ScrollRow = 269
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 256
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 157
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 133
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
End Sub




SO all of the active window stuff is garbage from what i can gather. This just went into the folder, took out the file using query, and also got rid of the useless data. It was put into u6 because that was the next available column.

I finished by deleting the data beyond 300 readings.

 

[Remou]

You can paste this code into a module in a copy of the master worksheet to test. It should copy c6:c307 into a new column from the newest file.

Function MostRecentFile(fldr)
 'This uses late binding, so a reference
 'is not required.
 'Note that there is no error coding - it
 'should be added.

 Dim f As Object, fl As Object, fs As Object
 Dim fd As Date
 Dim fname As String

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set fl = fs.GetFolder(fldr)
    
    'Go through each file in the folder
    For Each f In fl.Files
        'If we haven't got a file yet ot the
        'file is newer
        If IsNull(fd) Or f.DateCreated > fd Then
            fd = f.DateCreated
            'save the name
            fname = f.Name
        End If
    Next
    'return value
    MostRecentFile = fname
End Function

Sub GetData()
'Run this procedure to update the file
Dim strFile, LastCol

'Gets the most recent file by calling
'the procedure above.
strFile = MostRecentFile("c:\docs\dat\")

'Connection string to most recent file
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
    
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

'The data will be got from sheet1, range c6:c307
strSQL = "SELECT * FROM [Sheet1$C6:C307]"

rs.Open strSQL, cn

'Find the last column, if you mess with the columns or add
'new columns this will return the wrong answer, so
'save before running.
LastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

'Copy the data we picked up from the most recent file
'to the last column +1, ie, new column.
ActiveSheet.Cells(6, LastCol + 1).CopyFromRecordset rs

End Sub

 

[LBTaurus]

remou!!

Thanks a whole lot. THis is working well with a few exceptions.

i used only the code you gave me in a new module.

first of all, i replaced fldr in line 12 with my folder location that has the files i want.

I also replaced c:\docs\dat\ in line 32 with this same folder.

I also replaced sheet1 with data ( line 44) because that is what the sheet is called in the files that contain the values i want.

Is this correct?

what happens when i run it is that it immediately looks in my docs folder and cant find a specific file. Why is it looking for a specific file? an why in my documents folder instead of the one i specified?

The specific file it tries to find happens to be the oldest ( not most recent) one in the folder i told it look in, but the error it gives me says its looking in an entirely different place ( my docs folder).

Just for kicks, i put the file it kept looking for into my docs folder, and it ran perfectly. However, when it pastes the new values into my master file, it pastes numbers as text. This makes each entry box have a little green tab next to it telling me that it is a number stored as text.

HOw do i fix these problems?

Thanks so much for your help...