Free Microsoft Excel 2013 Quick Reference

Trying to combine multiple large excel sheets into one power pivot table

I have 11 (separate) excel files (one for each month from Feb - Dec of 2011). Each file is about 30mb large. I want to combine them to utlize centrally to create pivot tables and charts and am trying to do this via power pivot.

While I successfully (not without pain) added the file for for Feb to a power pivot table, I cannot figure out why I am having problems importing the file for March.

When I use the import tool I get the following error:

Error Message:
Unspecified error
Failed to connect to the server. Reason: Unspecified error
Call Stack:
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.InitializeConnectionObject(String connectionIdentifier)
at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.InitializeConnectionObject(String connectionIdentifier)
at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.Open(String& connectionIdentifier)
at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.Open()
at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.TestConnection()
at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.ClickTestConnection(Object progressControl)

Any assistance would be very appreciate.
Thank you,

Post your answer or comment

comments powered by Disqus

Could someone give me the code to combine multiple excel sheets into one. It doesn't mattter how many rows are in between, just as long as nothing over laps.


I am attempting to combine data from several worksheets into one sheet for
analysis. Other than copying and pasting each sheet, is there another way to
do this? Once combined I will be doing a pivot table to capture the
information I need.


I am trying to combine x number of columns into one single column.
Column B would begin in cell immediately at end of column A, Column C then
wold begin immediately at end of now column A and Column B etc.

I need to know an easy way to combine multiple rows of data into a single row
of data for a labor report that I'm working on.
My problem is that I have a report that generates 12 columns. I add an
additional 12 columns to this spreadsheet, one for each month. The first 9
columns contain specific data for each employee, the next 2 columns contain
data that is specific to the amount of labor each individual works in a given
month. The given month is the 12th column. So if the person charged to the
project 4 different months, I get 4 rows entries in the table.

I'd like to compress these 4 lines down into one, and so on for all
employees in the report. The first 9 columns contain duplicate data, since
the employee data is the same. For each of the month columns I've done a
test on the 12th column to tell what month the individual charged labor to,
and populated the labor amount from column 11 into the appropriate month
column. Now if I subtotal each month, I can see one line, by employee and
the amount of labor for each month on one line. I'd like to not have to
subtotal and see this same report with all the data from the first 9 columns
as well.

Any help would be greatly appreiciated. Thanks, JLM

In one of the columns there is data that I'd like entered into about 10
fields (columns), for each row there maybe 5 occurances of the first 9 pieces
of data. I want the the
10th column put in a new column (month)

Hello -

I am wanting to have a macro take approx. 200 Excel workbooks and
append them all into one workbook.

* I have 200 workbooks each with it's own unique name. 1.xls,
2.xls, 3.xls, ... 200.xls.
* Each workbook contains only 1 worksheet
* Each workbook contains the same 4 column headings
* Workbooks all have different amount or rows
* Looking to combine everything regardless of duplicates into one
large workbook with the same 4 column headings.

Can anyone provide some newbie VBA code/Macro which could faster then
the old "cut and paste" method.


Hi All,

Ok, I am hoping, really hoping this is an easy thing to do. However I don't think so.

Here is what I need to do. I need to take three excel sheet that have about 70% of the same info on them (Large company with way to many people doing there own thing). In each of the sheets is a unique SKU number. What I need to do is migrate all the sheets into one. The problem is that the skis in the second and third sheets are not alway in the same row but the information is alway in the same column.

Any help is appreciated



I am using MS excel 2003,I have an excel file for the each day named"summary_01 and summary_02 and so on...up to summary_31.

Each excel file contains three rows 1st headings ,2nd row contains GUJ details and 3rd RAM details.

Now I want to make summary of All GJ and All RM details showing day wise details.

How can I combine these excel sheets into one.??



I've no idea about VB macros:-(. I want to combine 5 excel sheets into one excel workbook. I want a macro to do that and also attach that macro to a button placed on an excel sheet. So, if I click the button the macro should run.

Any help is appreciated!


When trying to open 2 large excel files receiving error "Excel cannot complete this task with available resources. Choose less data or close other applications". This computer has dual xeon processors and 3GB of RAM. One file is 60 MGB and the other is 80 MGB. Any Ideas?

I need to copy an entire excel sheet into an access table. The table will most likely have data in it already and I want to clear the entire table and populate it with the sheets data from excel.

The sheet will already be open but the table won't be open.
When done, the sheet will still be open and the table doesn't need to be open.

I want to do this with vba programming. I have done the reverse of this already, access to excel, but don't quite get how to go excel to access.

I have been looking into this for a while now and I not seeing a solution that I'm understanding.

Thank you in advance for your help.

Is there any way in excel to combine two rows of data into one and have it also sum the combined data

I have five EXCEL 2003 workbooks containing columns of names, addresses,
phone numbers, and other columns. Each workbook is formatted the same. I
want to combine these five files into one workbook that includes all of the
information in the five separate files. I searched for EXCEL help on-line
(“combine workbooks”) but the solutions seem complicated.

What is the easiest way to combine these five EXCEL files into one big file
that includes all of their data? I will appreciate advice.

I have been searching for how to export an Excel sheet into a New Access Table and have not been understanding how to do this.

The spreadsheet is 40,000 rows by 50 columns with headers in row 1.

I know I can go into Access and import it by the "Get External Data" import but it is too slow of a process and I want to do it with VBA from excel.

One of my concerns is the formatting. Almost all of the data is text but some is numerical.

I have seen examples of the following code but I don't know if its used from inside Access or Excel.

 '    TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But, at any rate it seems that I don't have much control of the formatting this way.

The below code allows control of formatting of headers but I can't come up with how to add the rest of my 40,000 rows of data.

 db = OpenDatabase("C:AccessFileTestFile.mdb") 
Set MyNewTable = db.CreateTableDef("NewTable") 
With MyNewTable 
    .Fields.Append .CreateField("ID", dbGUID) 
    .Fields.Append .CreateField("Part Number", dbText, 255) 
    .Fields.Append .CreateField("Description", dbText, 255) 
    .Fields.Append .CreateField("Part category code", dbDouble, 2) 
    .Fields.Append .CreateField("Stocking Item", dbDouble, 2) 
    db.TableDefs.Append MyNewTable 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I don't have a lot of experience with Access so help with this would be greatly appreciated.

Thanks in advance.


My goal of my VB form is to be able to select certain things and then
display a filter spreadsheet using the Excel component in Microsoft
Office Spreadsheet 10.0 control.

Can someone tell me how to load an entire excel sheet into my component
sheet? After that I should be able to figure out how to filter and
display only certain rows/columns.


I have got two excel files with one common filed name.The first file has account number,name,city and state.And the 2nd file has got account number and status fields.I want to combine both these excel files into one file by using the common filed account number.Can someone please help me with this.I'm not an expert in excdel so please answer in detail.

many thanks

I've started to regularly receive a .pdf file from a client that I need to get into Excel to do some work on. I have a .pdf-to-.xls conversion program, but the output from the program (and the program doesn't let me change much how it generates the output) will place page 1 of the pds on Sheet1, page 2 on Sheet2, etc. etc. so that what I get is an .xls with a harge number of sheets that the count of them corresponds with how many pages were in the .pdf.

What I'd like to do is use some VBA to merge all of these sheets into one new worksheet where the "pages" (the Sheet1, sheet2, etc.) are concatenated one after the other so as you scroll through the new worksheet, you'd see "page" 1, "page" 2, etc.

Could anyone share with me the VBA code that would do that? And keep in mind that the number of pages in the .pdf is variable, it can change from month to month, so the number of sheets the .pdf-to-.xls conversion program is variable as well.

Thanks in advance!


Is there a way to combine row A and B into one row? For example, I have
data in both rows. Both rows are for the same borrower. I am trying to
combine row A and B into one row that has all the data both A and B had.

I know I can cut and paste each line, but the spreadsheet is large. Any

Thank you

I am trying to combine data from various cells into one cell.....using
a formula such as =c9&d9&e9 however if one of the cells were blank I
get a 0 in the end combination. What can I do to eliminate the zero?

Or pehaps there is a better way to do this. I have text and numbers in
15 rows / 4 columns. I would like to enter into a separate cell the
combined information from row a1:d1 then a2:d2 etc.

Unfortunately today, I can not seem to think of a better solution. any

Karmen's Profile:
View this thread:

How to Create multiple copies of sheet, at one go.

We have an option "Move or Copy". With this we can copy a sheet only once.

But, how to create multiple copies of sheet (at one-shot)

Thanks & Rgds,

I am trying to combine data from various cells into one cell.....using a formula such as =c9&d9&e9 however if one of the cells were blank I get a 0 in the end combination. What can I do to eliminate the zero?

Or pehaps there is a better way to do this. I have text and numbers in 15 rows / 4 columns. I would like to enter into a separate cell the combined information from row a1:d1 then a2:d2 etc.

Unfortunately today, I can not seem to think of a better solution. any suggestions?


I'm trying to combine multiple dates and criteria into one if statement formula.

I want to know if a particular date within a cell meets the following criteria:
1. if the date in cell is more than 60 days from today, less than 60 days, or if the cell is blank

answers: if it is more than 60 days, it is 'good', if it is less than 60 days it is 'bad', if it is blank then it is 'never'

I'm self taught with Excel, so forgive me if this is a very rudimentary question.

I need to combine information from two columns into one column. For example, in column one I have ABC. In column two, I have 123. I need to combine this information so it reads ABC123. Is there a way to do that?

Using Power Pivot(which is perhaps not relevant), I am trying to combine two sets of data into one pivot table.
I have created in each table a key column which I use to succesfully relate the tables.

But I cannot get the two corresponding sets properly aligned within the pivot table;

One set gets displayed as totals (as sum of or number of)

Is it not correct to assume that values withing two related tables should be aligned in the pivot table?

I have 6 pivot tables on 6 different tabs in Excel and I want to combine them
so I can have one main pivot table. Does anyone know how to do that?

No luck finding an answer? You could always try Google.