Avatar of cozmo_troll
cozmo_troll
 asked on

Excel combine several worksheets data into one

I have a workbook that has several worksheets that are laid out in the identical manor but contain different data. I need to make one worksheet that shows the consolidated data from all 5 worksheets. How do I go about this?
Microsoft Excel

Avatar of undefined
Last Comment
Anthony Mellor

8/22/2022 - Mon
gowflow

first you post this sample workbook making sure you scrub sensitive data then will go from there on
gowflow
cozmo_troll

ASKER
attached
Eoin Ryan

2 questions:

Does your header row only take up 1 row, as in the data starts at A2. If not starting at A2, where does the "real" data start?

How "wide" is your active data. does it go to column, D,E,F,G or even wider? Let me know the letter of the last active column.

I have something that will work when I know the answer to the above 2 questions.

Thanks.
Your help has saved me hundreds of hours of internet surfing.
fblack61
cozmo_troll

ASKER
Header row takes up 1 row and data starts in A2. It goes to column K
Eoin Ryan

alt+F11 for vba editor
right click on "thisworkbook" and choose INSERT then MODULE
copy and paste code below.
you can run it from the editor or go back to sheet view and use alt+F8 to choose the macro from there.

it creates a new workbook so as not to mess up any source data.

Sub EEConcatenateSheets_AtoK()

'change these if you add more sheets
Const iStartSheet As Integer = 1
Const iEndSheet As Integer = 5

'variable declartion
Dim i As Integer
Dim dRowCount As Double
Dim aWb As Workbook
Dim oWb As Workbook

'current workbook for reading info
Set aWb = ThisWorkbook

'new workbook for results
Set oWb = Workbooks.Add

For i = iStartSheet To iEndSheet

    'read sheet and copy out a to k to the last row
    aWb.Sheets(i).Activate
    dRowCount = ActiveSheet.UsedRange.Rows.Count
    Range("A2:K" & dRowCount).Select
    Selection.Copy
    
    'activate new sheet and paste the sheet 1
    oWb.Sheets(1).Activate
    dRowCount = ActiveSheet.UsedRange.Rows.Count
    Range("A" & dRowCount + 1).Select
    ActiveSheet.Paste


Next i

'tidy up
    Set aWb = Nothing
    Set oWb = Nothing
End Sub

Open in new window

cozmo_troll

ASKER
Ok, that works to setup a new workbook one time. What I'm looking for is something that is easily ran/updated by any user at any time. Basically, what we're trying to do is take 5 peoples workbooks, put them together into one to get consolidated data that I can then set as report info.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Eoin Ryan

so are we dealing we 5 workbooks (5 separate files) now, and not 5 sheets in 1 workbook?

Do you want the source cleared when copied over? what if it gets run again after only 1 line is updated? all old data gets recopied for just that one updated line.

I can keep it within the one workbook without too much trouble, but the issue of duplicating data still exists.

Would you like it all to be added to sheet 1? so sheet 1 gets everyone else's data written to the bottom of it.
cozmo_troll

ASKER
I have used a Get External Data function to take the data from the individual 5 workbooks to consolidate it into 1 workbook. What I need now is to take the data from those 5 worksheets and put all consolidated on one worksheet in that book.
Eoin Ryan

ok, this makes sheet1 the master sheet and copy over sheet2-5 placing their data underneath.

Sub EEConcatenateSheets_AtoK_toSheet1()

'change these if you add more sheets
Const iStartSheet As Integer = 2
Const iEndSheet As Integer = 5

'variable declartion
Dim i As Integer
Dim dRowCount As Double
Dim aWb As Workbook


'current workbook for reading info
Set aWb = ThisWorkbook


For i = iStartSheet To iEndSheet

    'read sheet and copy out a to k to the last row
    aWb.Sheets(i).Activate
    dRowCount = ActiveSheet.UsedRange.Rows.Count
    Range("A2:K" & dRowCount).Select
    Selection.Copy
    
    'activate new sheet and paste the sheet 1
    aWb.Sheets(1).Activate
    dRowCount = ActiveSheet.UsedRange.Rows.Count
    Range("A" & dRowCount + 1).Select
    ActiveSheet.Paste


Next i

'tidy up
Set aWb = Nothing

End Sub

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
cozmo_troll

ASKER
I just tried this and it doesn't do anything, but when I close out of the module, it moves me way down on my current sheet
Eoin Ryan

you may have alot of "dead" whitespace areas on each sheet which is being copied over too. Can you scroll back up to see if there's data, then blanks then more data. That would indicate whitespaces being coped too.

If you can delete from the first empty row to the bottom of the sheets before running, it should just have "good" data on the sheets with no whitespace filler.
cozmo_troll

ASKER
Ok, that seems to be what is happening. Is there any way to do this without the user having to go into every sheet and delete the "whitespace" prior to running the macro? The point is to make this as simple as possible for the user that will be accessing the consolidated report.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Mellor

use MS QUERY or Powerpivot (e.g create linked table) to JOIN  these tables.
cozmo_troll

ASKER
I don't know how to do this
Anthony Mellor

I'll come back with an append solution. However, there is an old fashioned way to do this.
Save each file in .CSV format, then use the old DOS copy command to:

copy file1.csv + file2.csv + file3.csv +file4.csv + file5.csv > fileall.csv

then open fileall.csv in Excel. The headers will be repeated, unless deleted before save as csv; which presumably you could demand users do, since they will only be doing this "save as" for the file to send to you.

However, like I said, that's a bit old fashioned and I keep reading that dos is dead :-)

Come to think of it, I expect VBA could be made to do the above file manipulation. (not my field).




my earlier reply:

ok, that's what we are here for. It's all point and click, but better done with an example. I'll create one unless someone beats me to it. Also would be useful to have some example data - what helps to know is if each table contains a column that can be used as a unique index (or "Key Field"). Really it depends on the nature of your data and your purpose, which is why example data is needed better to help you. Without it we risk guessing incorrectly.

Both MS Query and PowerPivot are indeed reporting engines in Excel for just the type of question you have posed.

Do of course anonymise anything confidential.
As you can see from these answers there are always various ways to do something and we await your choice ;-)

PowerPivot needs Excel 2010, are you using that version?
MS Query is much older, though if using a Mac then ODBC becomes an issue which I  haven't yet solved.

Meanwhile google INNER JOIN and OUTER JOIN to get a flavour of joining tables; it's not what you will need to know in detail, but it is the underlying technology and illustrates what can be done.

I'd love to give you your full reply right now, but time of day prevents me.

EDIT: apologies you want to append the table, will get back to you. Same tools, different feature.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Anthony Mellor

Here is how it's done: (PowerPivot solution)
http://blog.contextures.com/archives/2010/09/06/powerpivot-from-identical-excel-files/

The solution uses a UNION ALL in SQL terms so MS Query should also be able to do this.
I will be working through this myself, so again, feel free to provide some test data.

again, VBA will be able to automate it if needed (not sure yet).
cozmo_troll

ASKER
file attached
CONSOLIDATED.xlsm
Anthony Mellor

I am just viewing the Video linked from within the link I give above, it really seems to be a demo of exactly what you want.

Remember the use of a Pivot Table from a PowerPivot does not mean you cannot have your line item detail if you want it.


Once saved you the results will auto update for ever, no need to redo the process every time.

http://www.microsoft.com/en-us/bi/powerpivot.aspx

I think PP may be a sledgehammer to crack a/this nut, but I like this sledgehammer. It's mostly point and click and easy to use.

and from the comments "Thierry"

SELECT [Sheet1$].*
FROM [Sheet1$]

UNION ALL
SELECT * FROM `C:\$link trials\2008 short example trial.xlsx`.[sheet2$]

UNION ALL
SELECT * FROM `C:\$link trials\2008 short example trial.xlsx`.[sheet3$]

which is the syntax to combine three sheets, so you could hack that for your five sheets.
Once written, it remains in place. However, I would be inclined to do this from teh respective files rather than bringing the data into one file first. You could even make your file report directly from their respective live files, avoiding "spreadmart" issues.


"spreadmart" being "spreadsheet data mart" issue.
Regret cannot offer worked example as no access to my PC as this time.
Will do later if problem persists.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Eoin Ryan

whitespace removal included.

    
    Sub EEConcatenateSheets_AtoK_toSheet1()

'change these if you add more sheets
Const iStartSheet As Integer = 2
Const iEndSheet As Integer = 5

'variable declartion
Dim i As Integer
Dim dRowCount, dCurrentRow As Double
Dim aWb As Workbook


'current workbook for reading info
Set aWb = ThisWorkbook

' Cleansheets, remove whitespace at end of sheets
For i = 1 To iEndSheet
    
    aWb.Sheets(i).Activate
    dRowCount = ActiveSheet.UsedRange.Rows.Count
        
        For dCurrentRow = dRowCount To 2 Step -1
            
            If Cells(dCurrentRow, 1).Value = "" Then
                Rows(Trim(dCurrentRow) & ":" & Trim(dCurrentRow)).Select
                Selection.Delete Shift:=xlUp
            End If
            
        Next dCurrentRow
Next i
'finished cleaning


For i = iStartSheet To iEndSheet

    'read sheet and copy out a to k to the last row
    aWb.Sheets(i).Activate
    dRowCount = ActiveSheet.UsedRange.Rows.Count
    Range("A2:K" & dRowCount).Select
    Selection.Copy
    
    'activate new sheet and paste the sheet 1
    aWb.Sheets(1).Activate
    dRowCount = ActiveSheet.UsedRange.Rows.Count
    Range("A" & dRowCount + 1).Select
    ActiveSheet.Paste


Next i

'tidy up
Set aWb = Nothing

End Sub

Open in new window

cozmo_troll

ASKER
Ok, when i run this, it flashes on my screen and scrolls through lines 49000 back to 0 over and over and over and won't quit. I have to eventually force the program to quit
cozmo_troll

ASKER
Unfortunately, we are still on 2007. I'm assuming no way to make PowerPivot work for that :(
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Anthony Mellor

No, not a chance. BUT (fanfare!) MS Query does work as it has been built in for many years.
Please tell me you are PC based and not Mac?

The underlying process I was using in PP is an SQL Query, which can also be run in MS Query, which is accessed via get data external (iirc). I'll expand on this when you tell me you are using a PC which I think you must be with version 2007? 2008 being the Mac version.

In fact I think the process in that link and video will work just as well.
cozmo_troll

ASKER
Yes, we are PC based with MS Office 2007
Anthony Mellor

http://office.microsoft.com/en-001/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx

I just lost a huge reply! Anyway, look down to this bit in the above Excel 2007 specific link, ignore the noise about 2010, it is an advertisement:

Connect to a data source

snip

To set up a data source by using Microsoft Query:

On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.

Do one of the following:

To specify a data source for a database, text file, or Excel workbook, click the Databases tab.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cozmo_troll

ASKER
I've already got the data from the 5 individual workbooks built into this one workbook. What I need now is just to combine the data from the 5 sheets into 1.
Anthony Mellor

once or on a regular basis?

regular I guess...
cozmo_troll

ASKER
it will be on a regular basis; I want to make it as user friendly as possible, hopefully they just need to push the refresh all button and it is set.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Anthony Mellor

OK, that matches my assumptions.

May not mean anything to you yet, but I just "cracked it" with this:

SELECT `Sheet1$`.ONE, `Sheet1$`.TWO, `Sheet1$`.THREE
FROM `J:\DesktopJ\001.xlsx`.`Sheet1$` `Sheet1$`

UNION ALL
SELECT `Sheet1$`.ONE, `Sheet1$`.TWO, `Sheet1$`.THREE
FROM `J:\DesktopJ\002.xlsx`.`Sheet1$`

UNION ALL
SELECT `Sheet1$`.ONE, `Sheet1$`.TWO, `Sheet1$`.THREE
FROM `J:\DesktopJ\003.xlsx`.`Sheet1$`

UNION ALL
SELECT `Sheet1$`.ONE, `Sheet1$`.TWO, `Sheet1$`.THREE
FROM `J:\DesktopJ\004.xlsx`.`Sheet1$`

UNION ALL
SELECT `Sheet1$`.ONE, `Sheet1$`.TWO, `Sheet1$`.THREE
FROM `J:\DesktopJ\005.xlsx`.`Sheet1$`

BACK SHORTLY.
Anthony Mellor

I can give you a choice here between combining the sheets in to one sheet, OR combining the same data from within each of the original files into which the data was entered.

I am uncertain here, I am wondering if the users maintain each sheet in the same file or maintain a sheet in a separate file which is then transferred into this master Consolidation file.

We can do either or even both. I have in my mind's eye five users jostling for time using the shared file, or someone copying and pasting their data into the one file.. so you have some choices here, maybe to match what really happens or to take advantage of being able to make Excel do all the collating, which it will do in real time for you, as I recall I think you won't even have to click an update button (have to check that).

Meanwhile I will make a version of my above "code" that will work on sheets in the one file instead of on five separate files.
Anthony Mellor

also, I think this method means your file will not be an XLM, not a macro/vba file.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cozmo_troll

ASKER
I'm not exactly sure what you're asking... we don't need to see each individuals separated out into different sheets in the cosolidated file as long as all consolidated data is on one sheet. The sheets are all maintained in one folder on our shared server folder on the network.

Does that make sense? Were you able to see my file that I uploaded?
Eoin Ryan

The reason my solution takes so long is that you have in excess of 40,000 cells of white space per sheet in some cases. I'm not sure if everyone else's solution will run into these too.

you're bloating your file sizes with it also, when the white spaces are removed you drop from 1.8Mb to less that 10% of that for a final file size. It's just good excel practice and makes it easier to provide solutions if you're sanitising your data before running macros etc.
cozmo_troll

ASKER
is there something i need to do to get rid of the whitespace before hand?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Eoin Ryan

on each sheet go to the first empty row, click on the row number, for example row 40.
all the cells along row 40 get highlighted.

press ctrl+shit+down arrow it will highlight from row 40 all the way to the bottom of the sheet.

right click on a row number and choose "delete"

Code now deals with sheets with only a header row, still does the whitespace tidy up in case of a few stray white spaces.

cleaned file attached, you can run the macro to see it in action or run it on your own sheet.

    Sub EEConcatenateSheets_AtoK_toSheet1()

'change these if you add more sheets
Const iStartSheet As Integer = 2
Const iEndSheet As Integer = 5

'variable declartion
Dim i As Integer
Dim dRowCount, dCurrentRow As Double
Dim aWb As Workbook


'current workbook for reading info
Set aWb = ThisWorkbook

' Cleansheets, remove whitespace at end of sheets
For i = 1 To iEndSheet
    
    aWb.Sheets(i).Activate
    dRowCount = ActiveSheet.UsedRange.Rows.Count
    If dorowcount > 1 Then
        For dCurrentRow = dRowCount To 2 Step -1
            If Cells(dCurrentRow, 1).Value = "" Then
                Rows(Trim(dCurrentRow) & ":" & Trim(dCurrentRow)).Select
                Selection.Delete Shift:=xlUp
            End If
        Next dCurrentRow
    End If
Next i
'finished cleaning


For i = iStartSheet To iEndSheet

    'read sheet and copy out a to k to the last row
    aWb.Sheets(i).Activate
    dRowCount = ActiveSheet.UsedRange.Rows.Count
    If dRowCount > 1 Then
        Range("A2:K" & dRowCount).Select
        Selection.Copy
    
    'activate new sheet and paste the sheet 1
        aWb.Sheets(1).Activate
        dRowCount = ActiveSheet.UsedRange.Rows.Count
        Range("A" & dRowCount + 1).Select
        ActiveSheet.Paste
    End If

Next i

'tidy up
Set aWb = Nothing

End Sub

Open in new window

EE-Cozmo.xls
cozmo_troll

ASKER
Just tried and it did the same thing as before; it puts the lines all over with whitespace still present. Do you mean i need to go in and remove all white space manually before running it?
Eoin Ryan

yes, or the users of each sheet should be made aware of "The whitespace issue" and maintain their individual sheets better.

again, just makes better excel practice.

While I know the script takes a long time if there are white spaces, if you let it run to completion it achieves what you requested.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cozmo_troll

ASKER
OK.
cozmo_troll

ASKER
tried what you said to get rid of the deadspace on each individual sheet but it still doesn't work
cozmo_troll

ASKER
I tried it in your sample also, for whatever reason it puts things together without the dead space, but it keeps adding the same things to the list over and over each time the macro is run rather than just gathering the information once
Your help has saved me hundreds of hours of internet surfing.
fblack61
Eoin Ryan

try saving the file after removing white spaces, sometimes it needs to refresh the sheet to drop the white spaces.

I had queried you about duplicating data above. Here's the update for it to clear what it copies over.

    Sub EEConcatenateSheets_AtoK_toSheet1()

'change these if you add more sheets
Const iStartSheet As Integer = 2
Const iEndSheet As Integer = 5

'variable declartion
Dim i As Integer
Dim dRowCount, dCurrentRow As Double
Dim aWb As Workbook


'current workbook for reading info
Set aWb = ThisWorkbook

' Cleansheets, remove whitespace at end of sheets
For i = 1 To iEndSheet
    
    aWb.Sheets(i).Activate
    dRowCount = ActiveSheet.UsedRange.Rows.Count
    If dorowcount > 1 Then
        For dCurrentRow = dRowCount To 2 Step -1
            If Cells(dCurrentRow, 1).Value = "" Then
                Rows(Trim(dCurrentRow) & ":" & Trim(dCurrentRow)).Select
                Selection.Delete Shift:=xlUp
            End If
        Next dCurrentRow
    End If
Next i
'finished cleaning


For i = iStartSheet To iEndSheet

    'read sheet and copy out a to k to the last row
    aWb.Sheets(i).Activate
    dRowCount = ActiveSheet.UsedRange.Rows.Count
    If dRowCount > 1 Then
        Range("A2:K" & dRowCount).Select
        Selection.Cut
    
    'activate new sheet and paste the sheet 1
        aWb.Sheets(1).Activate
        dRowCount = ActiveSheet.UsedRange.Rows.Count
        Range("A" & dRowCount + 1).Select
        ActiveSheet.Paste
    End If

Next i

'tidy up
Set aWb = Nothing

End Sub

Open in new window

cozmo_troll

ASKER
still doesn't work
Eoin Ryan

included the code to take in the L column too as you have "general notes" there.

open file, run macro. takes info except the headers.

if anyone else would like to run it and test also, as there may be version issues.
EE-Cozmo-1.xls
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Mellor

I now have a working solution or thought I did, I get a lot of blank rows. Can't help wondering if this is the same "white space" problem. I MIGHT be able to tell the Query to ignore blanks, but I concur that not having it would be better. I also cannot help being curious how it gets there..

One sheet alone has nigh on 42,000 blank rows. What on Earth is happening?
I will see if I can exclude blanks. Your data file really does need some attention.
Anthony Mellor

MS QUERY: This works, and EXCLUDES all whitespace based on the assumption that if the name is blank, so is the rest of the row.

Here is an image of the results, please advise if it matches your example file consolidated.xls, which I have used as my sources.
Beneath I give the MS Query text.
It works with one click.

Consolidated data from five sheets


SELECT `Casey$`.*
FROM `J:\DesktopJ\CONSOLIDATED.xls`.`Casey$` `Casey$`
WHERE (`Casey$`.name Is Not Null)

UNION ALL
SELECT `Rachael$`.*
FROM `J:\DesktopJ\CONSOLIDATED.xls`.`Rachael$` `Rachael$`
WHERE (`Rachael$`.name Is Not Null)

UNION ALL
SELECT `Ryan$`.*
FROM `J:\DesktopJ\CONSOLIDATED.xls`.`Ryan$` `Ryan$`
WHERE (`Ryan$`.`Company Name` Is Not Null)

UNION ALL
SELECT `Tod$`.*
FROM `J:\DesktopJ\CONSOLIDATED.xls`.`Tod$` `Tod$`
WHERE (`Tod$`.name Is Not Null)

UNION ALL
SELECT `Zach$`.*
FROM `J:\DesktopJ\CONSOLIDATED.xls`.`Zach$` `Zach$`
WHERE (`Zach$`.name Is Not Null)


These sheets are NOT all identical, as regards headings in each sheet, so we are not combining identical sheets into one sheet.

J:\Desktop\ is my pathname. Give me yours for me to finish please.

(I am looking to see if I can make it work with a generic path, not so far not seen if it is possible. )
Anthony Mellor

Sunday pm edit: it occurs to me that the "white space" issue might be created by the "get external data" mentioned in ID: 39048635 and not be user related. If so, any solution including my solution below if applied directly to the workbooks/copies of, would avoid the issue altogether. After all users would not create such space on purpose. Surely not. Hypothesis: Excel formatting being acquired as if it is data.
-------------------------------------------------------------------------------------------------------------------------------------------

Right then: A WORKING SOLUTION HEREIN please place your CONSOLIDATION.xls file in C:\
(this means the VBA gets deleted, let me know if you need it to work with XLM FILES.)


Save the attached file to C:\

EDIT: Change extension from TXT to DQY because EE won't let me upload a DQY file.

double click the saved attached file. (maybe a connection dialogue, I click "ok")

Presto!

You will/should instantly (no delay) see your sheets all collated into one, in a fresh Excel file.
It should look like the screen shot in my above post.

That's it.

-------------------------------------------------------------------------------------------------------------------------------------------

NOTES

There are many ifs/buts and maybe's but the above works here, instantly, no processing delays, no white space (excluded if "name" column is blank, so if you have a row with data, where the name is blank, such a row is excluded. I had to make an assumption)

Your Consolidation file: I think it includes VBA code, which I have not touched, but is not required for this process, at all. Hence use of Save As with xls extension.

This process will also work for multiple remote network files; it would nee dthe paths to be saved.

This process does not require any entries to be made in the CONSOLIDATION file, so it can be used over and over again (double click the DQY file) and each result will be a fresh Excel file.

No one needs to do anything PROVIDED that the Consolidation file name, EXTENSION, sheet names, headings, FOLDER LOCATION OF C:\ , remain unchanged.

Pros: instant processing, white space ignored, no user intervention, no Excel file editing, double click operation, new file every time. The Query file is ordinary text editable with Notepad.

Cons: all the parameters (names) are hardwired (VBA can change that and so can Notepad).
I have had to use C:\ as the only reliably likely folder everyone will have.

My opinion: I keep wondering if this isn't five different files being maintained by five different people on the file-server, copied into the one CONSOLIDATION file.

Edit:
cozmo_trollPosted on 2013-04-04 at 20:15:42ID: 39048635

I have used a Get External Data function to take the data from the individual 5 workbooks to consolidate it into 1 workbook. What I need now is to take the data from those 5 worksheets and put all consolidated on one worksheet in that book.

If that is the case, then the Query would be better written to read each file from the file-server, or at least a local copy of each file to fix the data entry cut-off point. All each user need do is Save As to said shared local folder. I am of course guessing the circumstances, I'll read the thread again... now done see edit immediately above. Seems to me the Get External Data would be more efficient included within the Consolidation Query process. I'll re-write the Query also to do this, later. Indeed all that is required is to edit the filenames into the SQL Query code I show below. Replace C:\CONSOLIDATED.xls with each related file.

Here is my ODBC MS "SQL" Query (which is what this is, an ancient rite yet quite new to myself too, hence my interest in using it to solve this Question.)

XLODBC
1
DSN=Excel Files;
DBQ=C:\000.xls;
DefaultDir=C:;
DriverId=1046;
MaxBufferSize=2048;
PageTimeout=5;

SELECT `Casey$`.*  

FROM `C:\CONSOLIDATED.xls`.`Casey$` `Casey$`    
WHERE (`Casey$`.name Is Not Null)        

UNION ALL  
SELECT `Rachael$`.*

FROM `C:\CONSOLIDATED.xls`.`Rachael$` `Rachael$`    
WHERE (`Rachael$`.name Is Not Null)    

UNION ALL  
SELECT `Ryan$`.*  

FROM `C:\CONSOLIDATED.xls`.`Ryan$` `Ryan$`    
WHERE (`Ryan$`.`Company Name` Is Not Null)    

UNION ALL  
SELECT `Tod$`.*  

FROM `C:\CONSOLIDATED.xls`.`Tod$` `Tod$`    
WHERE (`Tod$`.name Is Not Null)    

UNION ALL  
SELECT `Zach$`.*  

FROM `C:\CONSOLIDATED.xls`.`Zach$` `Zach$`    
WHERE (`Zach$`.name Is Not Null)


name	Active?	Type	Company Name	Contact Name	Phone or Email Address	Contact Date	Potential Sale?	Probability %	Approx# $	Actual Sale $	General Notes

Open in new window

You need not do anything with the above snippet, as it is in the attached file, but I put it here for reference  18:10 Sunday
ConsolidationQueryCX.txt
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
cozmo_troll

ASKER
Ok, I think we're getting there, but the location of all files is actually S: not C:. Could this be changed?
Anthony Mellor

Attached

I noticed that I needed to Save As the attached file to stop my browser opening it as a text file.

REMEMBER to change the extension from TXT to DQY as per above instructions.
ConsolidationQuerySX.txt
cozmo_troll

ASKER
I saved it as a DQY, the problem is that the code is looking for C:/ not S:/ and the rest of the network path and I don't know how to change it.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Anthony Mellor

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Anthony Mellor

thanks! I was wondering how you were doing. :-)