Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Finding the Name on an Excel WorkSheet.

I am working on a program that is importing data from Excel.  I have the user select a worksheet, and the the import begins, the problem I have is that the worksheet name are a bit different.  When I access the worksheet via:
   Set ExcelApp = New Excel.Application
   ExcelApp.Visible = True
     
   ExcelApp.Workbooks.Open txtNASBeginPath
   Set ExcelBook = ExcelApp.ActiveWorkbook
   
   Set ExcelSheet = ExcelBook.Worksheets(1)

This gives me the last active worksheet, but I get back bad data.

Now if the sheet was named "QTR1 99" then I can set
   Set ExcelSheet = ExcelBook.Worksheets("QTR1 99") and the data comes out fine.  This is my predicament, The name is alway different, can I programatically obtain a collection of sheets from the Excel Object?

I know this seems confusing, but any help is greatly appreciated.

Thank you in Advance.

-John
0
jforbes
Asked:
jforbes
1 Solution
 
hesCommented:
Here is how I do it:

Private Sub Form_Load()
Dim vbexcel As Excel.Application
Dim wkbObj As Workbook
Set vbexcel = CreateObject("Excel.Application")
Set wkbObj = GetObject("YourExcelName.xls")

'Set vbexcel.ActiveWorkbook("F:\vb\testord\Set Order.xls")
'Set mysheets = wkbObj.Worksheets
For Each wks In wkbObj.Worksheets
 Debug.Print wks.Name
Next
End Sub
0
 
danlevansCommented:
jforbes,

For Each ws In Worksheets
      MsgBox ws.Name
Next ws


Have fun

Dan
0
 
LewyCommented:
Try this:

    Dim PN As New Collection, PX
    Dim I As Integer, Row As Long, P As Integer
   
    For I = 1 To Worksheets.Count
        PN.Add (Worksheets(I).Name)
    Next I
   
    For Each PX In PN
        Worksheets(PX).Activate
    Next PX
    'or
    Worksheets("Sheet2").Activate
0
 
jforbesAuthor Commented:
Thanks All, I should have guessed it.
-John
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now