Can't Open an Excel File-VBForums
Results 1 to 9 of 9

Thread: Can't Open an Excel File

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2010
    Posts
    9

    Can't Open an Excel File

    Hi all,

    I'm pulling my hair out trying to open an Excel file with VB6. The code is as follows

    Code:
    Dim AppExcel As Excel.Application
    Dim wSheet As Worksheet
    Dim wBook As Workbook
    
    Private Sub Form_Load()
    
       Set AppExcel = GetObject("c:\MyFile.xls")
    
    End Sub
    The GetObject line will always give me an error "Run-time error '13': Type mismatch". However, if I use the CreateObject command instead of the GetObject everything goes well. Yes, the file that I'm trying to open is in the right location, but I have no idea what's wrong. Any help will be appreciated. Thank you.

    Robert

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    36,801

    Re: Can't Open an Excel File

    The problem is that GetObject("c:\MyFile.xls") does not return an Application object.

    (hint: you already have an apt variable)

  3. #3
    Lively Member
    Join Date
    Jan 2009
    Posts
    84

    Re: Can't Open an Excel File

    Try this instead:

    Set wBook = AppExcel.Workbooks.Open("c:\MyFile.xls") 'Open an existing workbook

    AppExcel.Visible = True 'set to False to hide from user

  4. #4

    Thread Starter
    New Member
    Join Date
    Jun 2010
    Posts
    9

    Re: Can't Open an Excel File

    Quote Originally Posted by si_the_geek View Post
    The problem is that GetObject("c:\MyFile.xls") does not return an Application object.

    (hint: you already have an apt variable)
    Hmmm, your hint doesn't ring any bells .

    todd.kauffman,

    When I tried the line that you mentioned I get a new error pointing to the line you suggested

    "Run-time error '91': Object variable or With block variable not set"

    Any suggestions on how to open a file in excel? Thank you...

    Robert

  5. #5
    Web developer Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    11,437

    Re: Can't Open an Excel File

    This works!

    vb Code:
    1. Dim AppExcel As Excel.Application
    2. Dim wSheet As Worksheets
    3. Dim wBook As Workbook
    4.  
    5. Private Sub Form_Load()
    6. Set AppExcel = New Excel.Application
    7. Set wBook = AppExcel.Workbooks.Open("c:\MyFile.xls") 'Open an existing workbook
    8. wBook.Application.Visible = True  'set to False to hide from user
    9. End Sub
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    Please consider giving me some rep points if I help you a lot.
    DON'T BUMP YOUR POSTS!!! Links to my code examples can now be found on my website: My websites
    Please rate my post if you find it helpful!
    Technology is a dangerous thing in the hands of an idiot! I am that idiot.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,591

    Re: Can't Open an Excel File

    Hmmm, your hint doesn't ring any bells
    getobject(workbook) returns a workbook object, type mismatch to application object (appexcel)

    would work fine to wbook, which is a workbook object variable

    if you want to work with a workbook that may be already open, getobject(workbook) is good, it will find it in any instance of excel if it is already open, or open it if it is not
    to make visible if required
    vb Code:
    1. wbook.application.visible = true
    2. wbook.windows(1).visible = true
    either or both may be required
    Last edited by westconn1; Apr 14th, 2011 at 05:47 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Jun 2010
    Posts
    9

    Re: Can't Open an Excel File

    Quote Originally Posted by Nightwalker83 View Post
    This works!

    vb Code:
    1. Dim AppExcel As Excel.Application
    2. Dim wSheet As Worksheets
    3. Dim wBook As Workbook
    4.  
    5. Private Sub Form_Load()
    6. Set AppExcel = New Excel.Application
    7. Set wBook = AppExcel.Workbooks.Open("c:\MyFile.xls") 'Open an existing workbook
    8. wBook.Application.Visible = True  'set to False to hide from user
    9. End Sub
    Nightwalker83,

    Your code works fine.

    westconn1,

    It makes sence what you are saying. I still don't have a clear understanding of all of this VB6 concepts.

    Thank you all for your help. Now, I have a final question. The code from Nightwalker83 would only work if I get an Excel code example that came in a CD with a book and copy paste Nightwalker83's code in there. If I create a blank new project and paste that code it won't even recognize the first line of code "Dim AppExcel As Excel.Application". I get a "Compile error: User-defined type not defined".

    Is there any settings that I need to make in order for these Excel applications to works? I think I read about this somewhere, but I can't remember how to set it.

    Thanks again for your help.

    Robert

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    36,801

    Re: Can't Open an Excel File

    I recommend taking a look at my Excel tutorial (link in my signature), as it covers that issue - and others you are likely to come across.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,591

    Re: Can't Open an Excel File

    using getobject or createobject to set variables of type object, require no reference to excel, and will work on any computer with any version of excel installed

    using variables of type excel.application or excel.workbook require a reference (to excel) to be added to the project, this will then only work with the specific version of excel that the reference is for, and fail on computers using any other version
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.