Excel Import Data with VBA

November 30, 2009 at 19:32:21
Specs: Windows XP
I'm working on a project where i need to import different data from several workbooks into a particular worksheet. I have been told that it will be best to write a VBA macro for the task but it seems quite a complicated one for me as i am still a new user. I will appreciate help on how to go about this. Thank you.

Topaz from Manchester

See More: Excel Import Data with VBA

Report •

December 1, 2009 at 05:46:00
We would like to help, but you need to provide a lot more information.

We would need examples of where the data is coming from, where it is going to, etc.

Some example of what the data looks like would help also.

Are you copying formulae or values, etc.?

Report •

December 1, 2009 at 05:46:02

If you post the details someone will likely give you some code to get you started.

Information required.
1. How many source workbooks.
2. What are the source workbook names - are they sequential e.g., Source01.xls, Source02.xls
3. What range of cells in the Source workbook are going to be copied and is it the same range from every workbook, e.g., Source01.xls Sheet1 Range A1 to B20
4. Where does the Source data go in your destination workbook - all on one sheet, but on subsequent rows, or into the same range on a series of worksheets with a separate worksheet for each Source workbook.

To enter VBA code there are several places where code can go which tends to make it a bit confusing at first.

There are two main places to keep code
1. In a module in the workbook you are working on. The code will always stay with the workbook. It will only be viewable if the workbook is open. It will travel with the workbook if you send it to someone else. The code cannot be used to open the workbook, as it is only available once the workbook is open

2. In a hidden workbook called Personal.xls. The code will always be viewable when Excel is loaded. The code will not travel with the workbook in question, but the code is available to the workbook. It is easy to link a button on your toolbar to this code. It can open your workbook as part of the code.

A third location is like option 1, with the code in the workbook in question, but instead of in a Module it is attached to a specific worksheet in the workbook. If you use the Controls toolbar to add a button to a worksheet to run your routine, this is where the code will go by default. It stays with the Workbook.

To enter code:
Enter the VBA window by clicking Alt + f11 (The left Alt key and function key #11 at the same time)
In the Project Explorer window (usually on left), find VBAProject(Your.xls)
Right click on it and select Insert, then Module (not Class Module)
Double click Module1 which is under the Modules folder
Enter the code in the main window.

or look for VBAProject(PERSONAL.XLS) and as above, add a new module.

Always have the words 'Option Explicit' as the first line of each Module. This reduces the risk of programming errors due to simple typos. A variable like myCounter may get written as myCounter = myCuonter +1. Option Explicit catches this error because every variable must be declared at the start of the code e.g., Dim myCounter as Integer
myCuonter will be detected as a problem before your code runs.

It is also a good idea to start each variable with text that identifies its type, such as str for string (text) e.g., strMyWord, then when writing code you are less likely to write strMyWord= 123.45, which is trying to make it a number. VBA is good at workarounds and will kindly convert the number 123.45 to text, when in fact you wanted a number, and an error later on in your code.

When you have written some code, use f8 (function key #8) to single step through it. f5 will run it non-stop.


Report •

Related Solutions

Ask Question