Importing an excel sheet to another excel workbook

[bkeat]

Hi

I am a beginner with vba and I am stuck in trying to import another excel workbook with its location being flexible as the file-name will change.

I have a dialog bix opening up to select the file. I am trying to import data from one workbook sheet into my master file sheet. I will manipulate the data once in myself through another macro. But as the workbook i am importing is not consistent naming convention or location but will have the same format data in the sheet I want to be able to select the file but the data format in the workbook i am importing from will not change.

What i am struggling with is the code to import the data from that file. I have imported text files already and this is working but the code does not work for excel files.

Workbook1 is the data source file i want to import from and Workbook2 is the master file i will be using.

Workbook1 sheet name: "REPORT"

Workbook2 sheet name master file: "Forecast"

It is just the one sheet in the Workbook1 i want to import from and its the only sheet in the Workbook1 and it is all the data as it is in Workbook1 Sheet not any specific range. I will manipulate once i have it in Workbook2.



This is what i got so far

Please Login or Register  to view this content.

Having difficulty in building the next block of code for the import into my Workbook2.



I am using excel 2010.

Any help would be most appreciated.

Thanks.

[arlu1201]

Try this code

Please Login or Register  to view this content.

Put this code in a standard module of the destination file which contains the tab called "Forecast".

[RHCPgergo]

Hi,

I've made some modifications to your code. There was a small mistake in the syntax of GetOpenFilename. The below macro opens the workbook you pick and copies the first worksheet from it to the active workbook. Feel free to play with it.

Please Login or Register  to view this content.

[bkeat]

Thanks very much this works perfect. It copy the sheet over as is.

If i wanted the macro to run in a specific sheet already create called "Forecast" how could i do this?

[arlu1201]

Did you try the code i provided, bkeat?

[bkeat]

Yes i have now.

It opens the file I am trying to access, could this be avoided?

Also i got this error:

Run-time error'1004':

To paste all cells from an Excel worksheet into the current worksheet, you must paste into the first cell(A1 or R1C1).

[arlu1201]

What do you mean by this - It opens the file I am trying to access, could this be avoided?

To avoid getting the 2nd error - change this line from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[bkeat]

Yes that works your code is perfect.

What i meant was when i access the sourced data file and select it to import the data that workbook opens up just wondering could this be avoided as i only want the data from it, its only a minor issue.

[RHCPgergo]

You need to close it afterwards. You can do it by pasting this line at the end of Arlette's code. (But before End Sub of course.)

Please Login or Register  to view this content.

[bkeat]

Thanks you everyone so much for the help especially Arlette, I hope i'll be as good as all of you guys in time.

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.