Free Microsoft Excel 2013 Quick Reference

automate data collection in excel

I am trying copy and paste data from an many excel files in a folder. The steps I am using to get data are as follows:

1. I have a file called Summary.xls to collect all information from many excel files (File1.xls, File2.xls...) in a folder.

2. Need to copy Entire rows in each excel file on the folder to Summary.xls, starting from Row35 (other files can have 100 rows below R35) and only if Column I has a value of "ADD", "DELETE", or "MODIFY". else move to next file.

3. Need to add 1 blank row to seperate each file after copied to Summary.xls

Managed to get a macro to run to parse the .xls files in a folder (from another forum)

But cannot do the copy and pasting part. hoping someone can help me. Thanks in advance.

Sub test()
Sub RunCodeOnAllXLSFiles()
    Dim lCount As Long 
    Dim wbResults As Workbook 
    Dim wbCodeBook As Workbook 
    Dim strTab As String
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
    Application.EnableEvents = True
    On Error Resume Next

    Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
         'Change path to suit
        .LookIn = "C:Test"
        .FileType = msoFileTypeExcelWorkbooks
         .Filename = "*.xls"

        If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 'Loop through all.
                 'Open Workbook x and Set a Workbook   variable to it
                Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                strTab = wbResults.Sheets(1)
                wbResults.Sheets(1).UsedRange.Copy _
                Destination:=ThisWorkbook.Sheets(strTab).Cells(1, 1)

                wbResults.Close SaveChanges:=False
            Next lCount
        End If
    End With
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub


Post your answer or comment

comments powered by Disqus
Subject : Automating the XML Data Mapping in Excel 2003

Issue: I am trying to get the XML data out from a XLS file. These are the
steps I am doing right now…

Opened the XLS file and mapped the schema to the xls file using XMAP object
Called the file as SAVEXMLDATA method using the Microsoft Excel Object
library 11.0 Interop dll.
It throwed an exception saying this file cannot be exportable
I checked the IS Exportable property it returned false.
If I drag and drop the individual column from the schema to the Excel
column, it's saves as a XML data.
How to achieve the above step programmatically using C#, I checked an MSDN
it's written in VBA. Some of the objects are not supported in .NET.

Please let me know what is the workaround.

I use external data queries in excel like crazy. That's how I look up data in my access databases because I have so many access and SQL databases it's just eiser to be able to do quick queries on multiple databases at the same time in one spreadsheet. And, I can save the external data query as a tool to use in future queries. I have tons of tools and external data query is the foundation of most of them.

What bugs me is EDITING data.

If I want to do some light simple editing in an SQL database I am required to use a linked table in Access or SQL Studio Express Manager. Great programs, but I want to integrate!

Is there no way to make excel a database editor? I can retrieve data from any database or program with excel just fine, but is there a way to use excel to DIRECTLY EDIT the data in an external database with EXCEL like I can in access linked tables?

What use is the data form in excel. I've just started the excel expert
online course and it shows you how to create lists using data forms. But why
do you need a function like this when you can just type straight into excel?
Am I missing something here?

I am trying create a chart 'on the fly' based on the user selected data range
in Excel 2007. I was trying to use Jon Peltier's 'interactive chart
creation' VB scripts. Somehow it does not work on Excel 2007. After Jon's
interactiveChartCreation.xls opens, both input boxes does not work, meaning
one can not input any data by mouse selection. I tried to click 'ok', but it
only selected the input box. I hope Jon or some one else can see this and
offer some help. Thank you all very much for your tips.

Hi All, Im using data connections in excel 2010 and using an sql server 2008
database query in it.
My requirement is to change the data source name each and everytime I
refresh the data. For this I need to change data source value in connection
string and also need to change the values in sql query(i need to pass

I request you to pls help me in resolving this.
Also your help is greatly appreciated if you would can give me a resolution
in excel 2007 instead of excel 2010.

Thanks in advance.

How do I set up Add-Ins for iSeries Access Data Transfer in Excel 2007

Issue with being able to select 64 bit Data Sources in Excel 2007

I am having a couple of issues with Excel 2007 and being able to retrieve
data in Excel 2007.
The first issue is that I cannot see a System DSN that was added through the
64 bit ODBC Administrator screen on Windows 7 in Excel 2007.
What I do is add the System DSN and when I open Excel 2007 and then click on
the Data tab and then click on From Other Sources then click on From
Microsoft Query the Data Source I added is not in the list.
So since the System DSN did not show up I removed it and added a User DSN
and did the same steps as above and when I open the table I do see that Data
This gets me to the next issue. When I click on that User DSN name and then
OK I get the following Microsoft Query error screen:
The specified DSN contains an architecture mismatch between the Driver and
I then when I click OK I get this next Microsoft Query error screen:
Couldn't read this file

Since Office 2007 loads both 32 bit and 64 bit files shouldn't be able to
open the 64 bit Data Sources?
Just to let you know I am going against a 64 bit Oracle database.


Is it possible to use an array or a collection in excel UDF

I have made use of both but the udf doesnt give any result. When i run in execution mode then it automatically stops at the line where i make use of array.

With collection it doesnt even start.. It gives a compile error.


Hi All,

I have been looking around the internet to see if I can undertake a panel data regression in excel but have not seen anything obvious. Everything I read says it can be done in STATA but I would still like to know if it can be done in excel. I want to do a fixed effects model:

yit = a + bxit + εit

Can I do this with Linest?

I have a panel of data for prices and demand for a good for a number of countries for a number of years and instead of modelling an individual elasticity for each country I would like to treat the data as a panel and get one elasticity.

Can the known y's in linest be two dimensional i.e. a seperate row for each individual countries demand tim series? Or do I have to put all the demand time series into one row somewhow? If the different country time series are in different rows as the known's can i still do multivariate regression i.e. a corresponding number of price rows for the different countries but some other explanatory variables too?


Hi everyone,

I need to automate data import in excel from csv files where:

- The files are stored in folder C:DATA and each file name is data_20101404.csv - data_yyyyddmm.csv

- I would like to include the info after the last row of data from the day before. Every file has the same number os colums but rows can vary daily.

I have no clue what so ever how to do it so any macro/script/vb would be highly appreciated.

Thank you very much

I got 2 data table in excel format, but I want to know the different between
2 table!
For instance,
Table 1, Table 2,

Inventory ID Qty InventoryID Qty
CC2210 5 AA1101 5
CC2211 8 CC2210 9
CC2212 100 CC2211 160

Between 2 table, I want catch the inventoryID AA1101 IS THE DEFFERENT.

How ahould I do ??




I have a workbook with multiple worksheets and over 25 Data Connections using MS Query to connect to different tables in an SQL Server database.

The problem is that the SQL Server database is now being moved to a different server. The database name and the schema of the tables will be unchanged. Only the server name needs to be changed from XXXXX to YYYYY.

I have been unable to find a way to successfully edit the data connections in Excel 2003. A web search produced one potential option. However, that did not work for me very well. Thought the code is designed to cycle through all tablequeries and pivotqueries, the change is only made to one of the data connections. Here is the option I tried:

Note that it is very easy to edit data connections in Excel 2007. So I also tried opening the .xls file in Excel 2007. I used the Data Connections button under the Data tab in the ribbon and was successful in editing the connections to point to the new server. However, the graphs used to display the queried data behave weird. The "format axis" settings and chart sizes change. In general, default display settings for charts seems to differ between Excel 2003 and 2007. I used Excel 2007 to only edit the data connections and saved the workbook in the original .xls format.

Does anyone have any ideas on editing server settings within data connections in Excel 2003. I am frustrated with the compatibility issues between 2003 and 2007. Excel 2007 makes editing data connections a snap, but messes up the display of the charts.

Any input is much appreciated.

I'm stuck with ms excel as an interface with ms access as my back-end. This
started off as a simple project and as simple goes, evolved into complex. My
problem is how to display a memo data type in excel OLE objects.

thanks in advance.

Hi there
So I am starting a new job soon, and what I would like to do for them is automate the data collection process for them. What I have set up is, the field employees send in daily reports in excel, then I take those reports, and import them into a larger database in Access. Is there a way to automate this, ie they send the excel sheet to a server, it a macro runs where it will append the data into its data base every time a new sheet gets sent to it. I know there are ways of doing this, and if someone could point me in the right direction of how to achieve this that would be great, thanks.

Hey I am new member. I have problem in labelling data points in a scatterred graph in excel. I have three columns-A,B and C. I want to do a scatter plot between Column A and B. But the label to be dispalyed for those points in the graph I would like to be the value of Column C. I have large dataset, so it is really hard for me to label it manually for each data point. So I would like to know an automated procedure where I can label all the dat points at once. Currenlty the excel allows to label for either values of X axis or Y axis. So I would highly obliged if any one can help me out.


I have a database in excel for my collection of books which contains various
columns, title, type, cost etc. In excel 2000 when I wanted to add a new
book I just clicked on form and a box opened with all my column names in it
and I just filled in the new data. In Excel 2007 I cannot find this command

Can anybody help.

i have collected feedback regarding the usage of timesheet as a part of my MBA project.
the feedback is in MS EXCEL.
I would like to do some data analysis and put the data in form of charts.
pie charts are too elementary.
my professor insists in statistical tools which i dnt have.
the questions are given and the employees were asked to give the opinion on based of 5 point scale.
Strongly Disagree 1
Disagree 2
Undecided 3
Agree 4
Strongly Agree 5
what can i do in excel? I have no clue about it...
pls help me out...


Well, here goes. It is all about the question, so i'll try to ask the right one!

Let's say I have a list of numbers (in excel, assuming we are in Column 'A' going from row 1 to row 10)


OK, pretty simple so far. Do you see the number '2' in that list, how it occurs a few times? What I am particularly interested in is what the number *was* that *followed* the appearance of the '2' the next time around.

In the first case the number following the '2' was a '4', the second case it was a '6' and in the third case it was a '5' (it doesn't matter if there are repititions).

Now I, living two caves down from Osama, would come up with a very primative solution to at least extract the following numbers to Column B:


Am I a genius, or what? From there I would fill down 10 cells, and the out put would look like ths (in column B):

(blank cell)
(blank cell)
(blank cell)
(blank cell)

(note the gaps, as the search for the numbers that follow appear in the adjoining cell, if I could eliminate the gaps initially, or if there is a better way to extract the data, I am all ears)

OK, great, so at least I have 'found' the desired numbers. Now in order for me to chart the numbers without the annoying gaps, there are a few solutions. Two of which are to construct the =if statement with an #n/a [=if(a1=2,a2,#n/a], this way I can highlight the entire range and it will chart without the gaps, but it leaves all these '#n/a's' all over the spread sheet. The next solution to eliminate the gaps for charting purposes is somewhat more elegant, and simple, and that is to select the column where the 'found' numbers are, and click on AutoFilter. Fron there click on the toggle button for 'Nonblanks' and voila! it sucks all the offening air out of the column and we see a nice consecutive list of mumbers.

Are you with me so far? OK, good, because I am no expert, and the next part is the big deal.

So I have found the numbers, and as a bonus I can eliminate the offending air in the column as to produce a chart that looks right (a simple line chart, nothing fancy). But now I am getting greedy, becasue I want to *AUTOMATE* the production of the charts on an *As needed* basis.

Since the data is continually in flux, it won't do me much good to just manually chart all thee columns of numbers, because the chart one day will look different the next, and all I am interested in is how the chart looks today. Plus, the number of charts to maintain is somewajhat staggering, and would really mushroom the spread sheet. And, on any gien day I will only be interested in loking at a few select charts out of the hundreds. Thus, the ability to say to excel:

"Go make Chart 1, Chart 3, Chart 12, and Chart 17, Chart 23, and Chart 41, from the data located on the sheet called 'adata', and place them on the sheet called 'chartoutput'"

would be sweet indeed!

Now, I have tinkerd with a macro (recording one, the stupid person's solution to creating a macro ) Since I know how to tell the spread sheet where to go (the 6 unique charts I want created) I would start out the macro by highlighting the cell that directed excel to the precise column where the chart data was located.

On a sheet called 'data' there is a cell which has the address of the column that has the chart data which is Column A on the sheet called 'adata'. After directing the macro to copy the addressed cell, I then say Find, and it moves right to the cell I want in 'adata'.

And that is the problem. I want to reuse the macro generically so that *no matter what the name of the cell i want is found, it will autofilter *that* column, and not the one with the original cell address*

Did you gt that? It is so hard to explain. When I record the macro, once it 'finds' the right cell in 'adata' it always remembers *that* cell! If the find led me initially to A1 in 'adata', it gets recorded in the macro to end up at A1, even though on the next go around the freshly copied address from the locator cell on sheet 'data' is completely different. What this leads to is I always end up with the same chart output from column A1.

There are over 250 distinct possibilities (for my purposes), and I am trying to avoid creating 250 macros that say "OK, you stupid program, go to column A, and make the chart, end macro" and then another "column B" etc. That would take forever. Even if I could point and click and get the chart out put just so, once I knew which ones I wanted to look at, I'd have to manually hunt through the list of 250 macros, and click on them individually to get the 6 different charts I want.

What I *want* to do, is find a way to go from the 6 cells that have the specific column addresses of where the chart data is, and from there create the chart, without the macro getting locked in to the same initial destination address. once i get around that bug, the rest of it is wasy.


Hi Pros,
I need to automate data import in excel where:
- I receive files everyday by email and saved in a folder C:TEST
- each file saved by name Name+daily date
• ( like: sale_20091214, sale_20091215, sale_20091216 )
- file is in csv/Comma delimiated format
- each file have 7 columns A to G and contain 256 rows
- i only need 7 or 8 rows & only need value in Column 'F'
- the rows i need are going to be same each time
I want to save automated data in a file name: MonthlySale located in c:
Fore each ROW data should be saved in new column next to last saved column.
data from file sale_20091214 should be saved in column B,
sale_20091214 should be saved in column C,
sale_20091214 should be saved in column D and onward.
I am an average user, any macro/script/vb would be highly appreciated.

I am trying to create a master template in Excel 2003 that collects data that
is entered and saves it to a separate collection file to be used for
historical purposes. I did this in Excel 2002 by using the “Template Wizard
with Data Tracking” add in, however this does not work in the 2003 version of
Excel. Any ideas on how to make this work?

Thank you for any help that you could provide,



For a Registration system that I am creating I would like to automate the following:
In excel file "Bewerksheet.xlsx" on tab "Invoer" I would like to fill cells B2 and C2 with data from another excel file, which is named "week.xlsx"
Then continue with cells B3 and C3, and so on.
In this way that first all data in "Week.xlsx" on Tab "maandag" will be copied. when the last entry of tab "maandag" has been copied (Cells A14and B14, then tab "Dinsdag" data should be copied, then "donderdag" and filnally "Vrijdag".

In "bewerksheet.xlsx" on tab "Gewenst" is shown what I would like to achieve.

I will add both excel files with this question.

so from the "maandag" tab cell A2 "naam1" with date from cell B2 "5 September 2011" is copied to "Bewerksheet.xlsx" tab "invoer" cells B2 and c3 resp.
from the "dinsdag" tab naam1 with date 6 september 2011 should then be copied to the first free ceel on the "Bewerkstheet.xlsx" tab "invoer" that would be cell B15 and C15.

Is this possible to automate?
The amount of data on the "week.xlsx" tabs will vary, so the "maandag" tab may have 14 entries, but the "dinsdag" tab may have 20 entries.
also, as they are week excel files, the "maandag" tab of the next weekfile may have 19 entries, etc, etc.

Your help is appreciated Greatly!!!

Kind Regards,

I have Office XP on my Dell computer at work and ever since I got XP I haven't been able to use the Import External Data function in Excel. I have tried uninstalling and reinstalling custom and still get the error message the file is in use when the file is not opened or linked to any other source. Has anyone else experienced this problem? What did you do to resolve it?

I crated a data base in Excel to use to print envelopes. I want the font to
be Lucida Calligraphy as that is the font I used in Word to crate the

I've tried to change the font by highligting the data and typing in the font
and size but it won't come up.

On the web site it shows this font available in Office 2003, which I have
but when checking my "Font" in Control Panel it does not show, however I did
use it to create the original documents in Word.

Help please. Thanks, SLJackson

Hi all,

I have a multi-worksheet workbook in Excel 2007.

The first sheet has a list of filenames in.

The other sheets are intended to each have the contents from one worksheet in one of the named filenames at a time.

So, for example, my sheets so far might be entitled "Setup", "File 1 import" and "File 2 import", where "Setup" has had entered the filenames for the files that need to be imported.

Now, if I was going to do a fixed data import, column for column, that would be easy - in cell A1 of the "File 1" worksheet, for example, I'd have:


...and copy that across the remaining cells to the size of the worksheet being imported.

The files that the data are coming from are all the same format (.xlsx), they are all the same size and have the same sheet names (basically they're timesheet files held in Excel). So "SheetName" in the above example is always the same. It's literally just the filenames that are different.

So, let's say that cell B2 in "Setup" contains my first filename. What I need in cell A1 of the "File 1 import" worksheet is to somehow say:

='[ Setup!B2 ]SheetName'!A1

However, I can't figure out any way (at least, any way which works) of dynamically putting the result of "Setup!B2" into the file opening part of the above equation.

The whole reason for doing this is obviously because the filenames in "Setup" may be changed, and if that happens, I'd like the contents of the the relevant worksheet for that filename to dynamically change.

Has anyone got any ideas?

Many thanks in advance,


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