Solved: Import Spreedsheet from a closed workbook to another workbook

[nepotist]

Hello,

I am working on creating a excel application. and I need some guide line/help with some stuff.
1) I would like to Import a spreadsheet from a closed workbook(The whole spread sheet) like when I click a button it should show the file dialog box and should import the first sheet of that file in to the application. In my search I found codes that would define a range , but my range is not constant.

2) If I have to skip the Part of Importing I would like to create a New toolbar that would be accesible to all the workbooks. I do understand and I was able to make it work. but my toolbar is seen in the quickacces toolbarwhich isn't what I want. If possible I would like to create a Tab like the home, data reviewtabs in excel 2007 or atleast show the custom toolbars in the Main Ribbon rather than in quck access toolbars. (I would rather prefer the first one).

I am done with my application and this is theonly thing that I need to know so that I can finally work on the design which way I need to go.

Any help or guide line would be of great help and appreciated.

Thanks

[Bob Phillips]

1) Just open the file, copy the worksheet, close the file. Simplest way.

2) To do this you need to write some XML to add a separate ribbon, and write callback code to handle the button clicks. It is not hards, but it is not intuitive, take a look here http://www.oaltd.co.uk/Excel2007ProgRef/Default.htm

[nepotist]

hEY xld Thanks for the reply... well when it comes to the first point.. I want to give the user the functionality to import data and I dont want them to open copy and paste it .. you see what I am saying.. and I dont have that book with me but will try to do a search if I can come across some thing similar to it

[Bob Phillips]

I am not saying the user opens and copies, do it all in code, the user won't even know.

As to the book, you don't need it, Chapter 14 is availabel on that page.

[nepotist]

I have never worked with such things before do you have some links that would explain it.

Thank you

[Bob Phillips]

It is something as simple as

[vba]

Set ThisWB = ThisWorkbook
Set DataWB = Workbooks.Open("C:\filepath\filename.xls")

DataWB.Worksheets(1).UsedRange.Copy
ThisWB.Worksheets(1).Range("A5").Paste
DataWB.Close SaveChanges:+False
Set DataWB = Nothing
[/vba]

Obviously, it will need to tailored.

[veatraev]

hi all,
i have also same problem... i need to copy a range from closed workbook, which is protected by password. Also, the range which should be copied changes by time, as datas are sorted according to months. so if its march, then it should copy datas of march and april... the source workbook isn't possible to change as i mentioned before, its protected and only possible thing is to open as "read only". I found some codes but they only copy data, where I need cell formats.... any ideas on this issue?
thanks

[mduff]

Not sure if this will help

But

http://www.vbaexpress.com/forum/showthread.php?t=22737

[veatraev]

i have already tried that one but its only copying raw data, and not cell formats. To be more precise:
there are 2 excel files(A and B). The datas should be taken from file A, where datas are sorted by months, so when month changes in file B, new datas should be copied. The file A is protected with password and only can be opened as "read only". Tha data which should be copied to file B is datas for 2 months(current and next).
Thanks beforehand for your help