DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 4 123 ... LastLast
Results 1 to 15 of 52

Thread: Several Excel VB Questions.

  1. #1
    Join Date
    Jun 2007
    Posts
    39

    Several Excel VB Questions.

    1) I need to Query the user for a File... Would like to use File Browser to get file location and name.

    2) then I need to open the file and copy data from opened sheet to new sheet.


    I'm doing this to provide users a button to press to update from one version to the next. If there's a better way to do this I'm open to suggestions..

    Red.

  2. #2
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Welcome to the Forums.

    Converting from one version of Excel to another? What versions? Is this all to be done in Excels VBA?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  3. #3
    Join Date
    Jun 2007
    Posts
    39
    I'm creating something to work with a website I frequent..

    The Version change is: versions of this excel sheet..

    User places information in specified columns on one of the sheets..

    If I change the over all sheet/macros/layout.. blah blah..

    I'd like the user to have a button that asks them to "Browse to Old Database" then copies the data the user put into the earlier version and places it into the new version...

    Does that make sense?

  4. #4
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Oh different "versions" of the Excel workbook file andnot the Excel app version like 2002, 2003, 2007 etc.

    Is this "Old database" an Access database or Excel workbook?

    You can create a file selector type dialog with ...

    Application.GetOpenFileDialog


    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  5. #5
    Join Date
    Jun 2007
    Posts
    39
    Old one is also Excel..

    What would the return be? Like

    File = Application.GetOpenFileDialog

    would that make File the location and file name of the selected file?

  6. #6
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Just take a look in the help file or Object Browser of Excel.


    Code:
    Dim sFile As String
    sFile = Application.GetOpenFilename("Excel Workbook Files Only (*.xls),*.xls", 1, "Select Old Excel File")
    MsgBox sFile
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  7. #7
    Join Date
    Jun 2007
    Posts
    39
    When looking for proper code I always check the help button.. Then Google.. Then where ever else I can find it lol

    This so far is the only thing has me stumped.. for days...

    Now I have to figure out how to open the file and copy the data.. I'm trying to do all of it in a Macro that will be attached to a button.. I'll work on what you sent me and come back if I get stumped again..

    Thank You so much!!

  8. #8
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    You're wwelcome

    If you need more help on the transferring of data you can always record a macro to see how Excel does it. Then you can tweak the code as needed for repeated or general use.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  9. #9
    Join Date
    Jun 2007
    Posts
    39
    yeah I know that part.. hehe See I've done most of it by making macros and seeing what it looks like..

    New question though.. I used:

    Dim sFile As String
    sFile = Application.GetOpenFilename("Excel Workbook Files Only (*.xls),*.xls", 1, "Select Old Excel File")
    ' MsgBox sFile
    Open sFile For Random Access Read As #2


    When it opens the file as #2 (I know it's open cause when I try to run this a second time it says it's open lol) But I don't see it open so I'm guessing it's open in memory.. So how to I use it? is there a way to say "Copy this from file #2" ? I mean i know the copy commands.. and paste blah blah.. but how to tell it I'm working with the file that I just opened? lol

  10. #10
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Correct, that opens it in memory and not sdisplayed to the user. To open a xls file visible to the user then...

    Application.Workbooks.Add sFile

    Then if you want to manipulate it that is possible too.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  11. #11
    Join Date
    Jun 2007
    Posts
    39
    I need to open the file so I can manipulate it in the macro.. so this will allow me... Also.. I need to be able to close it.. which I think I can figure out.. can all this be done with Application.Screenupdating = False So that the user doesn't see the file open or close?

    I need to open the file.. Copy a section of it and paste it into the new file.. then close the old file... Make sense?

    Ohh Found another thing.. How do I get the name of all open Workbooks? What if they change the name of the workbook? Then this macro might break
    Last edited by RedSatiin; 07-01-2007 at 04:05 PM.

  12. #12
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    You can use the Workbooks collection to iterate through to find all open workbooks inside that same instance of Excel. If you have separate instances open then it will not transverse them.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  13. #13
    Join Date
    Jun 2007
    Posts
    39
    ok how bout this.. Since I see that I'm going to be asking so many simple questions lol..

    Is there a book that gives detailed information about Excel's VB? Or will any VB Book work? I think I just need a book that can give me a really good point of reference..

  14. #14
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    It would have to be a VBA book and to be honest I havent had a new reference book since 1998.

    Excels Object Browser is a great resource and also its help file.

    Ps, we dont mind answering a bunch of questions
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  15. #15
    Join Date
    Jun 2007
    Posts
    39
    ok.. then let me place the questions in order And all in one question lol

    1. Once I open the workbook How to I bring that workbook to focus.. I know I can select individual Sheets with: Sheets(2).select How would I select the individual Workbook that they selected as the File.

    2. If they've changed the name of the Sheet that i need to gather information from what's the best way to check? Is there a way to have a hidden Tag on the sheet that the user can't remove, that I can use to identify it?

    3. What's the best way to find the "Empty Cell" in a column? Users have lists of items in Column A.. I need to be able to find the last cell of their list so that my macro can select all the items in their list down to the last.

    Hmm Think that's it for right now

Similar Threads

  1. VB to excel export
    By Ananthakrishnan in forum VB Classic
    Replies: 3
    Last Post: 05-20-2005, 11:01 AM
  2. Excel and VB
    By svn in forum VB Classic
    Replies: 6
    Last Post: 05-17-2005, 09:55 AM
  3. They created J#, why couldn't they do VB#?
    By Thomas Eyde in forum .NET
    Replies: 290
    Last Post: 12-22-2001, 03:13 PM
  4. Replies: 84
    Last Post: 01-29-2001, 02:12 PM
  5. Re: VB or Powerbuilder?
    By Jean-Yves in forum Enterprise
    Replies: 3
    Last Post: 06-01-2000, 12:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center