Select Correct sheet using VB Code

D

Dunmarie

I have a Excel Workbook with a couple of sheets. I would like to call a sheet
depending on a variable selected by the user.
My current code is as follows:
Dim xlw As Excel.Workbook
Dim xls As Excel.Worksheet
Dim xla As Excel.Application

Set xlw = GetObject("G:\Stock Take\Stock Month-End.xls")
Set xls = xlw.ActiveSheet
Set xla = xlw.Parent

[Text44] = xls.Cells(10, 2)
[Text54] = xls.Cells(10, 3)
[Text63] = xls.Cells(10, 4)
[Text201] = xls.Cells(10, 5)
[Text212] = xls.Cells(10, 7)
[Text90] = xls.Cells(10, 8)

I did not include the code for selecting the sheet depended on the var.
Any help will be appreciated.
Thanks.
 
G

Guest

if you are calling a sheet depending on a variable, sounds
like a select case or if then else. not knowing what the
variables are or how many, there is very little we can do
to help.
by the way....you do know that this is the access news
group. you may get more help in the xl news group.
good luck.
 
D

Dunmarie

Yes, I know it is a Acces news Group. I am calling the sheet from a Access
Database using VB code.
The variable is the sheet name. The user select it using a combo box which
then creates the variable. A simple example will be:
Sheetname = Jan
User Select "January" which then creates a variable called varMonth with
"Jan" as var.
I would like to use "varMonth" which contain "Jan" to call sheet "Jan" in
the workbook.

[email protected] said:
if you are calling a sheet depending on a variable, sounds
like a select case or if then else. not knowing what the
variables are or how many, there is very little we can do
to help.
by the way....you do know that this is the access news
group. you may get more help in the xl news group.
good luck.
-----Original Message-----
I have a Excel Workbook with a couple of sheets. I would like to call a sheet
depending on a variable selected by the user.
My current code is as follows:
Dim xlw As Excel.Workbook
Dim xls As Excel.Worksheet
Dim xla As Excel.Application

Set xlw = GetObject("G:\Stock Take\Stock Month- End.xls")
Set xls = xlw.ActiveSheet
Set xla = xlw.Parent

[Text44] = xls.Cells(10, 2)
[Text54] = xls.Cells(10, 3)
[Text63] = xls.Cells(10, 4)
[Text201] = xls.Cells(10, 5)
[Text212] = xls.Cells(10, 7)
[Text90] = xls.Cells(10, 8)

I did not include the code for selecting the sheet depended on the var.
Any help will be appreciated.
Thanks.
.
Click to expand...
Click to expand...
 
B

Brendan Reynolds

You're still more likely to get a reply in an Excel newsgroup. I understand
your point that you are using the code in Access, but the code is
manipulating the Excel object model, and Excel developers are likely to be
more familiar with the Excel object model than Access developers.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dunmarie said:
Yes, I know it is a Acces news Group. I am calling the sheet from a Access
Database using VB code.
The variable is the sheet name. The user select it using a combo box which
then creates the variable. A simple example will be:
Sheetname = Jan
User Select "January" which then creates a variable called varMonth with
"Jan" as var.
I would like to use "varMonth" which contain "Jan" to call sheet "Jan" in
the workbook.

[email protected] said:
if you are calling a sheet depending on a variable, sounds
like a select case or if then else. not knowing what the
variables are or how many, there is very little we can do
to help.
by the way....you do know that this is the access news
group. you may get more help in the xl news group.
good luck.
-----Original Message-----
I have a Excel Workbook with a couple of sheets. I would like to call a sheet
depending on a variable selected by the user.
My current code is as follows:
Dim xlw As Excel.Workbook
Dim xls As Excel.Worksheet
Dim xla As Excel.Application

Set xlw = GetObject("G:\Stock Take\Stock Month- End.xls")
Set xls = xlw.ActiveSheet
Set xla = xlw.Parent

[Text44] = xls.Cells(10, 2)
[Text54] = xls.Cells(10, 3)
[Text63] = xls.Cells(10, 4)
[Text201] = xls.Cells(10, 5)
[Text212] = xls.Cells(10, 7)
[Text90] = xls.Cells(10, 8)

I did not include the code for selecting the sheet depended on the var.
Any help will be appreciated.
Thanks.
.
Click to expand...
Click to expand...
Click to expand...
 
Top