dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Working with Excel from VB

  1. #1
    Join Date
    Nov 2004
    Posts
    293

    Working with Excel from VB

    I have a VB program that opens an Excel spreadsheet then goes to a paticular worksheet, reads a number and then closes the workbook. This all works fine on the first time thru but on the second time thru it will open the workbook fine but when I tell it to go to a the same paticular worksheet it gives me an error. (Sheets(WorkSheetName.Select)

    Variable TheLocation was defined elseware in the program
    Variable WorkSheetName was defined elseware in the program

    My code looks like this:
    Set appEx = CreateObject("Excel.Application") 'creates the excel
    appEx.Visible = True 'show the excel form
    appEx.Workbooks.Open (TheLocation) 'open the workbook
    On Error GoTo ExcelLoadError2
    'now make it non Max size and set its width and height.
    'must put the excel into normal for the width and height adjustment to work
    appEx.ActiveWorkbook.Windows.Application.WindowState = xlNormal
    appEx.ActiveWindow.WindowState = xlNormal
    appEx.ActiveWindow.Width = 100
    appEx.ActiveWindow.Height = 100
    appEx.Application.Width = 100
    appEx.Application.Height = 225
    DoEvents
    Sheets(WorkSheetName).Select 'make sheet named xxx the active sheet

    'do the stuff you need done here

    On Error GoTo ExcelLoadError3
    appEx.Visible = True
    appEx.ActiveWorkbook.Save 'save the workbook
    appEx.Workbooks.Close
    appEx.Quit
    Set appEx = Nothing

  2. #2
    Join Date
    Nov 2004
    Posts
    293

    The error number

    The error number is: 1004 "method "sheets' of object' _Global' failed

  3. #3
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Try fully qualifying the object since you are not in Excels VBA is almost a requirement.

    Code:
    appEx.ActiveWorkbook.Sheets(Worksheetname).Select
    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

  4. #4
    Join Date
    Nov 2004
    Posts
    293
    That fixed it, thanks.

Similar Threads

  1. EXCEL crash with VB DLL running on XP PRO SP2
    By flv58 in forum VB Classic
    Replies: 0
    Last Post: 02-09-2007, 07:24 AM
  2. Replies: 1
    Last Post: 01-02-2007, 09:58 AM
  3. Excel and VB
    By svn in forum VB Classic
    Replies: 6
    Last Post: 05-17-2005, 09:55 AM
  4. VB Automation error with excel file
    By Parita Pai in forum VB Classic
    Replies: 1
    Last Post: 07-19-2002, 08:31 AM
  5. Replies: 1
    Last Post: 07-25-2001, 06:17 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