PDA

View Full Version : Solved: Import Spreedsheet from a closed workbook to another workbook



nepotist
03-19-2009, 07:58 AM
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
03-19-2009, 08:28 AM
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
03-19-2009, 08:39 AM
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
03-19-2009, 09:21 AM
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
03-20-2009, 12:05 PM
I have never worked with such things before do you have some links that would explain it.

Thank you

Bob Phillips
03-20-2009, 01:26 PM
It is something as simple as



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


Obviously, it will need to tailored.

veatraev
03-21-2009, 03:04 PM
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
03-22-2009, 12:54 AM
Not sure if this will help

But

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

veatraev
03-22-2009, 06:50 AM
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