Listbox referer to specific excel sheet

[Newbie_Movie]

Hello Everyone,

I want to try to make a button in Access which can open a specific sheet in a Excel-workbook.

The specific sheet must referer to the selection in a listbox. So the meaning is when i select an item in a listbox, the button must open the specific sheet of the item for an example a city.

The code that i have is:

Private Sub Button69_Click()
On Error GoTo Err_Button_Click
Dim xlWB As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("C:\worldcitys.xls")
Exit_Button69_Click:
Exit Sub
Err_Button69_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub


Can someone help me?

 

[JuriSim]

you shoud make a tempory table.
in form_open event you can delete contents of table
after this you shoud run a function which fills the table making use of function DIR
example:


Dim xPath As String
Dim MyFile As String
Dim tmpset As DAO.Recordset

CurrentDb.Execute "Delete from TempTable"

Set tmpset = currentdb.OpenRecordset("TempTable", dbOpenDynaset)


xPath = "C:\"
MyFile = Dir(xPath & "*.xls")
Do While MyFile <> ""
tmpset.AddNew
tmpset!FileName = MyFile
tmpset.Update
End If
MyFile = Dir()
Loop

 

[Newbie_Movie]

Hello Jurisim,

My problem is not populating the selections in a listbox but opening a specific sheet by the selection made in the list box. Can you or someone help me by this?

Greetings,

Newbie_Movie

 

[twoplustwo]

Can you not activate the worksheet name based on the variable that you'fe filtering on? I'll suffix this with the fact I've not used Access/Excel in this manner.

 

[JuriSim]

Hello,
After opening your workbook you can always use the following:

xlapp.sheets(1)

Tip: use the macro editor in Xls to see what code is generated...

 

[Newbie_Movie]

Hello,
After opening your workbook you can always use the following:

xlapp.sheets(1)

Tip: use the macro editor in Xls to see what code is generated...

I have used this code in the past but with this i can only select one particular sheet.. that works fine. The question how to auto-select the worksheet based on the selection made in the listbox.

For an example want the meaning is:
When i select city Tokio in the listbox and i click the button near, then i want to open a workbook and auto-browse to the sheet with the name Tokio.

 

[JuriSim]

xlapp.sheets("Tokio") should work as well

 

[Newbie_Movie]

xlapp.sheets("Tokio") should work as well

I know but i will open only sheet Tokio then.. when i select Madrid, then sheet Madrid must be opened and when i select Bern then the sheet Bern must be opened. How can i do this?

 

[JuriSim]

Then you shoud open the whole excel doc; afterwards just select the sheet

xlapp.Sheets("Madrid").Select

 

[Newbie_Movie]

Then you shoud open the whole excel doc; afterwards just select the sheet

xlapp.Sheets("Madrid").Select

I know that but the name of the city changes on the selection made in the list box. This must be dynamic. Anyone help please, without this i can not complete my application.

 

[Newbie_Movie]

Can anyone help me with this
I can not go furhter with this

 

[chergh]

Create a sub to select a sheet something like:

sub selectSheet(strCity as string)

xlapp.Sheets(strCity).Select

end sub

And then call the sub from the click event of your button and pass the value of the selected city in the list box to the called sub e.g.

Call selectSheet(me!LstBoxName)

 

[Newbie_Movie]

Create a sub to select a sheet something like:

sub selectSheet(strCity as string)
 
xlapp.Sheets(strCity).Select
 
end sub

And then call the sub from the click event of your button and pass the value of the selected city in the list box to the called sub e.g.

Call selectSheet(me!LstBoxName)

Hello Chergh,

Could you be so kind a put that in the code that i posted at the beginning? I would very appreciate that because i am a newbie to vba

 

[chergh]

Theres not a lot of point in me doing that to be honest. Your code is going to try to open the same workbook everytime you hit the button so if the book is already open then your going to run into difficulties.

You want to approach your problem something like this.

Create a sub in a module that opens your excel workbook is the first step.

Then add an if statement so it checks that xlwb is equal to nothing before trying to open the work book.

Then call this sub from the code sitting behind the form

You can then add a parameter to the sub so the name of the sheet to select is can be passed from your form

You should be able to figure out the first two steps easily enough, if you don't know how to do them use the forum search and google as the answers to them can be found in many places.

 

[Newbie_Movie]

It didn't succeeded to put this code in my code. I tryed many ways to figure this out but with negative results. Can you or someone help me with this please

 

[Newbie_Movie]

Can someone help me with this please

 

[Newbie_Movie]

Theres not a lot of point in me doing that to be honest. Your code is going to try to open the same workbook everytime you hit the button so if the book is already open then your going to run into difficulties.

You want to approach your problem something like this.

Create a sub in a module that opens your excel workbook is the first step.

Then add an if statement so it checks that xlwb is equal to nothing before trying to open the work book.

Then call this sub from the code sitting behind the form

You can then add a parameter to the sub so the name of the sheet to select is can be passed from your form

You should be able to figure out the first two steps easily enough, if you don't know how to do them use the forum search and google as the answers to them can be found in many places.

Can anyone help me with this please? I can not go one with my application. Or can someone explain this to me with the first message i posted on this topic (there you can find the code i posted)

Please help me.

 

[DCrake]

Simple Software Solutions

Having followed this thread closely I am still bemused as to why you need to open a specific worksheet having opened the specific workbook.

How does Access know what worksheet names appear in the workbook?
What happens if the user select Burma and it can't find a worksheet call Burma. Yes you can can get it to itterate throught the known worksheet names and call up a message box to state that Burna is an invalid name of a worksheet. But a good programmer would not let the user get that far in the first place.

My solution would be to identify the workbook that the user wants to use.
Get Access to open it in the background and use all the worksheet names within the workbook as items in a further listbox/combobox.

Then the user can only choose a valid worksheet name.

Also what are you doing with this worksheet info? are you reading/writing back to the workbook, what?

If you are simply reading the info then I suggest you select the desired worksheet name using the logic described earlier and copy this into a new workbook, thus preserving the original workbook and open the new workbook.

For a newbie as you term yourself you are attempting to perform a task that is somewhat more advanced that your skills permit. Before entering a marathon runners usually train running 5000k - 10000k first.

CodeMaster:

 

[Newbie_Movie]

Having followed this thread closely I am still bemused as to why you need to open a specific worksheet having opened the specific workbook.

How does Access know what worksheet names appear in the workbook?
By the name of the city in the listbox. When i add a new city in the listbox, i will add a new worksheet with the name of the city. When i select the city in the listbox, the specific worksheet must open when i hit a button. That is my mention.
What happens if the user select Burma and it can't find a worksheet call Burma.
I can make a error-message then.
Yes you can can get it to itterate throught the known worksheet names and call up a message box to state that Burna is an invalid name of a worksheet. But a good programmer would not let the user get that far in the first place.

My solution would be to identify the workbook that the user wants to use.
Get Access to open it in the background and use all the worksheet names within the workbook as items in a further listbox/combobox.

Then the user can only choose a valid worksheet name.

Also what are you doing with this worksheet info? are you reading/writing back to the workbook, what?
This workbook i will store additional information about the city.

If you are simply reading the info then I suggest you select the desired worksheet name using the logic described earlier and copy this into a new workbook, thus preserving the original workbook and open the new workbook.

For a newbie as you term yourself you are attempting to perform a task that is somewhat more advanced that your skills permit. Before entering a marathon runners usually train running 5000k - 10000k first.
Your right. But this job camed without notification. Therefor i had no time to excercise and play wothout Access and VBA. My application is finished. Only this point i am stuck to it.

 

[labanitup]

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