Consulting

Results 1 to 9 of 9

Thread: Solved: Import Spreedsheet from a closed workbook to another workbook

  1. #1
    VBAX Mentor
    Joined
    Aug 2008
    Posts
    323
    Location

    Solved: Import Spreedsheet from a closed workbook to another workbook

    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
    I am a Newbie, soon to be a Guru

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Mentor
    Joined
    Aug 2008
    Posts
    323
    Location
    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
    I am a Newbie, soon to be a Guru

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Mentor
    Joined
    Aug 2008
    Posts
    323
    Location
    I have never worked with such things before do you have some links that would explain it.

    Thank you
    I am a Newbie, soon to be a Guru

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Newbie
    Joined
    Mar 2009
    Posts
    2
    Location
    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

  8. #8
    VBAX Regular
    Joined
    Oct 2004
    Posts
    65
    Location
    We are living in a world today
    where lemonade is made from
    artificial flavoring and furniture polish
    is made from real lemons...
    Alfred E Newman

  9. #9
    VBAX Newbie
    Joined
    Mar 2009
    Posts
    2
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •