Listbox referer to specific excel sheet

[Newbie_Movie]

do you want to select a value from a list box ?

Yes. It goes like this: i select a value from a list box and then click on the button near the list box. The button must open then a specific sheet in a workbook.

 

[labanitup]

I did a code that allow you to select multiple values( 5 in my case) from a list box and
put them in a string with these values . I dont know if it helps you out.

 

[DCrake]

Simple Software Solutions

The first step is to load an instance of Excel

and pass the name of the workbook and the worksheet to the function to tell it which one to open and which sheet to set the focus to:

Function LoadExcelSpreadsheet(xlfilename,xlSheetName)

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim iRow As Integer
Dim iCol As Integer
Dim iLastRow
Dim tDate As String

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(xlfilename)
Set oSheet = oBook.Worksheets(xlSheetName)


...Rest of your code here

Where xlFileName = the name of the Workbook
Where xlSheetName = the name of the Worksheet

To employ this on the OnClick of your button apply

Call LoadExcelSpreadsheet(StrWorkBook,StrWorkSheet)

StrWorkbook and StrWorkSheet are obtained from your controls on the form

You may want to check if the actual workbook is open prior to attempting to open it as you will get errors appearing:

Copy this function into a module.

Function IsXLBookOpen(strName As String) As Boolean
     
     'Function designed to test if a specific Excel
     'workbook is open or not.
     
    Dim i As Long, XLAppFx As Excel.Application, NotOpen As Boolean
     
     'Find/create an Excel instance
    On Error Resume Next
    Set XLAppFx = GetObject(, "Excel.Application")
    If Err.Number = 429 Then
        NotOpen = True
        Set XLAppFx = CreateObject("Excel.Application")
        Err.Clear
    End If
     
     'Loop through all open workbooks in such instance
    For i = XLAppFx.Workbooks.Count To 1 Step -1
        If XLAppFx.Workbooks(i).Name = strName Then Exit For
    Next i
     
     'Set all to False
    IsXLBookOpen = False
     
     'Perform check to see if name was found
    If i <> 0 Then IsXLBookOpen = True
     
     'Close if was closed
    If NotOpen Then XLAppFx.Quit
     
     'Release the instance
    Set XLAppFx = Nothing
     
End Function

Hope we have got to the bottom of this issue.

CodeMaster:

 

[labanitup]

well i did almost the same thing. the only difference is that mine opens a report instead of a sheet.
i thin k u can replace the sheet name by Me.List5.Selected where list5 is ur list box. i dont know if that works

 

[Newbie_Movie]

The first step is to load an instance of Excel

and pass the name of the workbook and the worksheet to the function to tell it which one to open and which sheet to set the focus to:

Function LoadExcelSpreadsheet(xlfilename,xlSheetName)
 
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim iRow As Integer
Dim iCol As Integer
Dim iLastRow
Dim tDate As String
 
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(xlfilename)
Set oSheet = oBook.Worksheets(xlSheetName)
 
 
...Rest of your code here

Where xlFileName = the name of the Workbook
Where xlSheetName = the name of the Worksheet

To employ this on the OnClick of your button apply

Call LoadExcelSpreadsheet(StrWorkBook,StrWorkSheet)

StrWorkbook and StrWorkSheet are obtained from your controls on the form

You may want to check if the actual workbook is open prior to attempting to open it as you will get errors appearing:

Copy this function into a module.

Function IsXLBookOpen(strName As String) As Boolean
 
     'Function designed to test if a specific Excel
     'workbook is open or not.
 
    Dim i As Long, XLAppFx As Excel.Application, NotOpen As Boolean
 
     'Find/create an Excel instance
    On Error Resume Next
    Set XLAppFx = GetObject(, "Excel.Application")
    If Err.Number = 429 Then
        NotOpen = True
        Set XLAppFx = CreateObject("Excel.Application")
        Err.Clear
    End If
 
     'Loop through all open workbooks in such instance
    For i = XLAppFx.Workbooks.Count To 1 Step -1
        If XLAppFx.Workbooks(i).Name = strName Then Exit For
    Next i
 
     'Set all to False
    IsXLBookOpen = False
 
     'Perform check to see if name was found
    If i <> 0 Then IsXLBookOpen = True
 
     'Close if was closed
    If NotOpen Then XLAppFx.Quit
 
     'Release the instance
    Set XLAppFx = Nothing
 
End Function

Hope we have got to the bottom of this issue.

CodeMaster:

Thanks for your help CodeMaster but.. this wasn't where i was looking for. I camed to the point that i can open a specific worksheet in a specific workbook. But the issue is this:

I have 10 citynames in a list box and a workbook with 10 worksheets. Each worksheet has the same name of the cityname, listed in the list box. So i have on my form this list box and i want to make a button which can open workbook with the specific worksheet. And this is my problem It must be something dynamic thing.

Can you please help me?

 

[DCrake]

Simple Software Solutions

You are beginning to confuse me even further

I have 10 citynames in a list box and a workbook with 10 worksheets. Each worksheet has the same name of the cityname

Citynames
Listbox:

Miami
Florida
Kansas
Dallas
Etc


Excel Workbook: What is its name
Worksheet 1: Miami
Worksheet 2: Florida
Worksheet 3: Kansas
Worksheet 4: Dallas
Worksheet 5: Etc

So i have on my form this list box and i want to make a button which can open workbook with the specific worksheet.

So what you are saying is that you do not know the name of the workbook that has the name of the selected city as a worksheet within it?

Very confused:

 

[Newbie_Movie]

I have a workbook name citynames.xls
When i select a city out of the list box and i hit a button, i want to open the worksheet that has the same name as the city.

Example:
I select Stockholm in the list box and i click on the button near it.
The button opens in the workbook the worksheet Stockholm.

The same for Tokio, for Madrid etc.


The worksheets i will normally add in the workbook and a new city i will normally add in the database of Access

I hope it's now more clear.

 

[DCrake]

Simple Software Solutions

The button opens in the workbook the worksheet Stockholm.

Where is this button? is it on your form? or is it on the worksheet in the workbook?

Example:
I select Stockholm in the list box and i click on the button near it.
The button opens in the workbook the worksheet Stockholm.

So I have highlighted Stockholm from my listbox
I then click a button next to the list box
The OnClick event recognsis the name stockholm and opens the workbook citynames.xls

The worksheet names Stockholm is selected

What's missing

 

[Newbie_Movie]

The button is on the form.

So I have highlighted Stockholm from my listbox
I then click a button next to the list box
The OnClick event recognsis the name stockholm and opens the workbook citynames.xls

The worksheet names Stockholm is selected

Exacto mundo. That is what i am trying to do. It opens the workbook citynames.xls and opens the worksheet Stockholm.

 

[DCrake]

Simple Software Solutions

So what is the problem? I thought this was the topic of the original post?

 

[Newbie_Movie]

So what is the problem? I thought this was the topic of the original post?

That my button near the list box on the form be able to do that.

 

[DCrake]

Simple Software Solutions

Earlier Post:

Thanks for your help CodeMaster but.. this wasn't where i was looking for. I camed to the point that i can open a specific worksheet in a specific workbook. But the issue is this:

I have 10 citynames in a list box and a workbook with 10 worksheets. Each worksheet has the same name of the cityname, listed in the list box. So i have on my form this list box and i want to make a button which can open workbook with the specific worksheet. And this is my problem It must be something dynamic thing.

I have supplied the code to do just that earlier. If you cannot explain the root problem how do you expect other people to give you the correct answer.

I Give Up

 

[Newbie_Movie]

Earlier Post:




I have supplied the code to do just that earlier. If you cannot explain the root problem how do you expect other people to give you the correct answer.

I Give Up

I have explained it many times. How should i tell it different?

 

[Newbie_Movie]

Can some please help me with this problem?
I need a button that can open a specific sheet (based on the selection made in a list box on the same form) in a workbook (excel).

 

[twoplustwo]