Import data

Hi,

I am trying to import data from different spreadsheets "about 100" to a principal spreadsheet which will have all the centralized data. I included the principal spreadsheet layout and a sample of two detail spreadsheets. The information that I want to import is from column B 1 to 5 from the detail spreadsheets and put into the principal spreadsheet from row D to G.

Is there any way to import this information automatically, like creating a macro or something similar.

Regards,

Hello Carlos.Peres,

Your attached file never got posted. Please try again.

Thanks,
Leith Ross

here is the attachment

Hello Carlos.Peres,

From looking at your workbooks, you want to transfer the vendor name from the Details worksheet over to the Principal worksheet. Is that correct? Will there be any other data to transfer?

Thanks,
Leith Ross

I need to transfer the following information into the principal spreadsheet.

Sales
Days Outstanding
score
Complexity

Regards,

Hello Carlos.Peres,

The attached zip file has both workbooks. The macro is installed in the Principal workbook. It checks that the Details workbook is open. If not a message box tells you the workbook must be open to run the macro. I added an Update List button on the Principal worksheet. Just click it to run the macro.
Macro Code

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Is there any way to do this with the detail workbook closed. I will have abou 10 different detail workbook with 10 more sheets for workbook. Like detail1, detail2, detail3, etc.

Regards,

Hello Carlos.Peres,

It can be done that way. Are all the workbooks in the same folder? Does the macro need to prompt for the folder?

Thanks,
Leith Ross

Hello Carlos.Peres,

I finished and tested the macro code for retrieving data from the closed workbooks. This was a learning experience for me as well, which is why it has taken this long to complete. The biggest drawback with using closed workbooks is data in columns must be the same type (Text, Numbers, Dates, etc.). The ADO server samples the recordset (range) and uses the majority type. All other types are cleared. With 100 + workbooks, changing your data format isn't really an option at this point. The workbooks could be opened and closed automatically. The big drawback with this is you can't access the workbook's data if another user has it open.

There are three macros. The main macro prompts for the folder, and then loops through the folder looking for workbook files (xls extensions). The workbook data are retrieved from each sheet in the closed workbook, and posted to ActiveSheet of the open workbook. There is one macro to launch the Browse For Folders user interface using the API, and the other macro setups and manages the Closed Workboooks using ADO and SQL commands.

Due to length restrictions on posting I will make a second post with that includes the Browse For Folder macro.

Closed Workbook Macros

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hello Carlos.Peres,

Here is the code for browsing the folders using the API...

Browse For Folder API Macro

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Many thanks for your help. I am trying to run the macro and I am having problems. Could please attach the excel file.

Regards,

Hello Carlos.Peres,

I am not really sure what your question is. Do you want me to attach a workbook that uses the macro? If you attached your workbook with the last post, it didn't attach.

Thanks,
Leith Ross

Hello Leith Ross

If you can attach your workbook with the complete macro that you create to open the files and import the data into the workbook.

Hello Carlos.Peres,

the attached workbook has the macro installed. Just click on the button on Sheet1 to run it.

Sincerely,
Leith Ross

Leith Ross,

Here is what I really need to do. Enclosed are 3 excel's files. The Main_Table.xls is the Vendor list where I want to import all the data from the ER.xls and JI.xls files. There are about 100 excel's files in the same format as ER.xls and JI.xls. Right now if I press the update bottom in the Main_table.xls file it will import the ER.xls data into the Main_Table.xls file which is fine. What I would like to do is the following. I'd like to import the data from the other files at the same time and I don't know how to do it. I don't know if I can create a kind of conditional to look for the 100 files or look for all the files in a specific folder and import the data from all the files into the Main_Table.xls file.

Any help will be appreciate it.

Hi Leith,

I am working on a Project database. The purpose of this database (in the form of a spreadsheet) is to record and monitor the progress across 3 areas of the county (East/North/Central).

These spreadsheets are kept on a shared network.

At the moment I have created 3 spreadsheets that all record the progress for their respective areas on a monthly basis (please see the files attached). I am working on designing a 4th spreadsheet which reflect the progress from the 3 spreadsheets for each area, by way of a 'Drop-down filter' (East/North/Central) or possibly macro, which when selected will automatically update/reflect the progress for the respective area (East/North/Central). This 'County-wide spreadsheet would look almost identical to the other 3 spreadsheets, however, this would be able to 'filter-out' the commissioning intentions progress for each area using the drop-down box.

Leith, I was wondering whether you may be able to assist us in designing this as we here you are very proficient with Excel…?

look forward to hearing from you very soon.

imran