Import Workbook and append data

**desibabuji** · 11-02-2012, 12:36 PM

I am trying to create a macro that prompts to import excel files and then imports the worksheet in that file to a workbook.
What I wan to be able to do is to be able to:

Delete the first 4 rows, as it contains the header and other generic information about the worksheet (which is not needed)
If it is the first sheet to be imported then add as a new worksheet.
Check to see if data is already present and it is not the first import then add the data at the first blank row on the current worksheet.

The format of the data will not change, number of columns will remain the same but the number of rows will vary anywhere from 30 to 300 rows.

This is what I have so far, it prompts the user to select the file and then imports it as a new worksheet.

Please Login or Register  to view this content.

Can you please help ! Thanks.

**HaHoBe** · 11-03-2012, 06:13 AM

Hi, desibabuji,

If it is the first sheet to be imported then add as a new worksheet.

You give the new sheet a unique name in the code displayed - how can we find out if it is a new sheet or records need to be updated?

I personally would strongly recommend not to let the user do a Multi-Select of Files and the just assign one identical name to the worksheet - maybe you can be a bit more specific about the names of the worksheets to use as well (I would perhaps use part of the workbook´s name or from any column where unique data is stored).

Ciao,
Holger

**desibabuji** · 11-05-2012, 02:46 AM

Thanks HaHoBe.

That's what I am struggling with, the logic to find out of it's a new sheet or if records need to be appended. There will not be any "updates" or the old records need not be updated, the new records from the sheet should only have to be added beginning at the first empty row.

Can I give them an option to "only choose" one sheet at a time? I'm sorry but I am very new to Excel/VBA/Macros ! Can you give me any pointers to implement it?

Regards.

**HaHoBe** · 11-05-2012, 03:15 PM

Hi, desibabuji,

will the data be always on the same columns or will that vary (or will even the headings and contents vary?) The last question may be of concern as you can´t simply copy the contents across but need to make sure to fill the correct columns. Will the workbooks consist of one or more sheets of data for copying?

Could you please attach a workbook with a sample of data to be attached and a copy of where to attach (maybe point out if anything special has to be ztaken care of when copying data)? TIA.

Ciao,
Holger

**desibabuji** · 11-06-2012, 08:54 PM

Hi Holger,

Sorry I was out of town so couldnt reply earlier.

The data will always have the same column (even the headings will be the same) and the workbook will only have 1 sheet per workbook.

I came across this link on the forums for a page that is owned by one of the gentleman on this forum and this looks really close and interesting to what I want to do but instead of pulling all the files in a folder, I want to be able to choose the files that I want to be able to merge.
https://sites.google.com/a/madrocket...s-to-one-sheet

Also, please find attached 3 sheets, 2 of them are of sample data and the third one is of the consolidated sample.

Once again thanks for all your help.

**HaHoBe** · 11-07-2012, 04:33 PM

Hi, desibabuji,

if you change the name of the worksheet to suit (from Master to Export_1104) and adjust the path to the folder accordingly, the macro from Jerry will import the contents of any wortkbook found in the location into the sheet. I would recommend a sorting on the data after the import and maybe run a search for duplicates on the data assembled. If you need help on that code I think JBeaucaire would be the best person to ask but maybe I can handle that (not as good as Jerry but anyhow make it work - somehow).

If you want the user to take care of which workbook to amend or copy to a new worksheet in the macro workbook please feel free to come back and ask - I could supply a code which lets the user choose the file and then ask via MsgBox whether to amend or insert into a new worksheet.

Ciao,
Holger

**desibabuji** · 11-07-2012, 05:09 PM

Hi HahoBe,

Thanks for the reply. I want the users to be able to pick and choose a file and NOT sweep the folder.

So:
1. The user should browse and pick a file (one at a time).
2. If the Master does not exist then it creates it.
3. If the Master exists then it appends the data in the end.

Can you please provide the file that you had mentioned. I think that will solve the purpose as that's what I want.
Thanks

**JBeaucaire** · 11-08-2012, 11:53 AM

This macro should be installed into your Consolidation workbook in a standard code module.

When you run it, it will first detect/create an Export sheet with today's date, then let you select as many files as you want, one at a time, and import them.

Just edit the default path so the file selector opens in the best "starting folder" for your searches.

Please Login or Register  to view this content.

**desibabuji** · 11-08-2012, 12:51 PM

Hi Jerry,

Thanks a lot for your help. It works beautifully and also Thanks for the site that you maintain, it's such a cove of treasure for people like me who are learning excel and VBA. I really appreciate it.

**desibabuji** · 11-08-2012, 02:12 PM

Hi Jerry,

Can I request a small change/modification please? In the worksheets I realized that the header is made up of first 4 rows. (I know in my sample it is only the first row). Where and how does the code need to be modified to skip the first 4 rows when importing? I know you are skipping the header ! Sorry but I just realized it when i was running the script on some of the actual sample data. Sorry !

**JBeaucaire** · 11-08-2012, 02:17 PM

Change the Offset(1).Copy to Offset(4).Copy

**desibabuji** · 11-09-2012, 02:45 AM

Thanks Jerry,

I am just trying out all the possibilities and trying to explore/learn as well. Can you also please tell me what do I need to change, if I only want to import certain columns and not all the columns, if I want to only import column A, B, D, F G and H and want to skip C and E. Sorry for asking so many questions, I am just trying to figure it out and learn.

Regards,

**JBeaucaire** · 11-09-2012, 02:59 AM

I would import all the data, then delete columns C and E at the end.

**desibabuji** · 11-09-2012, 03:07 AM

That's what I was trying to do, I added this after the loop and before the End Sub

Please Login or Register  to view this content.

but then when I run the code (with pre-existing values from previous imports) it deletes the columns recursively. Where in the code should I put the delete commands? Within the Do Loop?

**HaHoBe** · 11-10-2012, 03:58 AM

Hi, desibabuji,

you should reverse the columns to be deleted: first column E and thereafter Column C. If you delete Column C first you should adapt your code because Column E would have shifted to D instead.

The code should be inserted beetween Loop and End Sub to only be run once at the end:

Please Login or Register  to view this content.

Ciao,
Holger

**desibabuji** · 11-12-2012, 02:04 AM

Thanks Holger for the pointer.

I was able to make it work.

Import Workbook and append data

LinkBack

Thread Tools

Rate This Thread

Display

Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Re: Import Workbook and append data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions