How to use string variable as a variable?

[John]

How can I set a variable (Table_Select) equal to another string variable (Table_1)?

Example:
--------------------------------
Table_1 = "Winner"

For i = 1 to 3
Table_Select = "Table_" & i
--------------------------------

Table_Select should equal "Winner", not "Table_1"

[tombsy]

I think there is the VAL("Table_" & i).Name

or something like that

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

How can I set a variable (Table_Select) equal to another string variable
(Table_1)?

Example:
--------------------------------
Table_1 = "Winner"

For i = 1 to 3
Table_Select = "Table_" & i
--------------------------------

Table_Select should equal "Winner", not "Table_1"

[Trevor Best]

John wrote:

How can I set a variable (Table_Select) equal to another string variable (Table_1)?

Example:
--------------------------------
Table_1 = "Winner"

For i = 1 to 3
Table_Select = "Table_" & i
--------------------------------

Table_Select should equal "Winner", not "Table_1"

Looks like you should consider using an array, otherwise I have no idea
what you're trying to accomplish.

Dim strTable(1 To 3) As String
Dim strTable_Select As String
Dim i As Long

strTable(1) = "Winner"
strTable(2) = "Runner Up"
strTable(3) = "Loser"

For i = 1 To 3
strTable_Select = strTable(i)
Debug.Print strTable_Select
Next
--
This sig left intentionally blank

[Bas Cost Budde]

tombsy wrote:

I think there is the VAL("Table_" & i).Name

or something like that

No, Eval("Table_" & i) will do.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[John]

> No, Eval("Table_" & i) will do.

I'm not able to get this to work for me. Here's my code...at the
bottom is the logic I'm having trouble with:

'************************************************* *******************
Function UploadModel(Model_No As Integer)
Dim ModelPath As String
Dim TableName As String
Dim FileName As String
Dim RangeName As String

ModelPath = "P:\Forecast\"

'************************************************* *******************
'Model Definitions
'************************************************* *******************
'ProductA
Model_1 = "ProductA.xls"
Model_1_Range = "db_ProductA"
Table_1 = "xl_ProductA"
'ProductB
Model_2 = "ProductB.xls"
Model_2_Range = "db_ProductB"
Table_2 = "xl_ProductB"

'************************************************* *******************
'Upload Model
'************************************************* *******************
TableName = "Table_" & Model_No
FileName = model_path & "Model_" & Model_No
RangeName = "Model_" & Model_No & "_Range"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, & _
Eval("Table_" & Model_No), Eval("FileName"), True, Eval("RangeName")

End Function

[Bas Cost Budde]

John wrote:

No, Eval("Table_" & i) will do.
I'm not able to get this to work for me. Here's my code...at the
bottom is the logic I'm having trouble with:

I was clearly mislead by your question.
TableName = "Table_" & Model_No
FileName = model_path & "Model_" & Model_No
RangeName = "Model_" & Model_No & "_Range"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, & _
Eval("Table_" & Model_No), Eval("FileName"), True, Eval("RangeName")
The DoCmd.TransferSpreadsheet needs *string* parameters. So instead of
having the Eval() expressions, you could use the variables assigned above:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, & _
TableName, FileName, True, RangeName

should do.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[John]

Bas, thanks for the response. However, your suggestion of just using
the variables for the transferspreadsheet parameters is exactly my
problem. The variable would be a string...I need the "string" to be a
variable.

In other words...
"table_16" string needs to be table_16 variable, which is equal to a
string ("Some_table").

I mistakingly left out a line in the code before, but this is an
example:

'************************************************* **
TableName = "Table_" & Model_No
FileName = model_path & "Model_" & Model_No
RangeName = "Model_" & Model_No & "_Range"

Table_16 = "Some_Table"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, & _
Eval("Table_" & Model_No), Eval("FileName"), True, Eval("RangeName")
'************************************************* **

[Darryl Kerkeslager]

I think you're trying to use a variable name as something meaningful in and
of itself, which is not accepted practice.

In your first line, you set the value of TableName - presumably to
"Table_16" when you want that table. If that's what happens, Bas's code
does *exactly* what you want. If you're trying to do something other than
that, your really twisting the way variables work. There should be no need
to use the Eval function here, and no need to use the line Table_16 =
"Table_16".
Darryl Kerkeslager

"John" <so*********@hotmail.com> wrote:

Bas, thanks for the response. However, your suggestion of just using
the variables for the transferspreadsheet parameters is exactly my
problem. The variable would be a string...I need the "string" to be a
variable.

In other words...
"table_16" string needs to be table_16 variable, which is equal to a
string ("Some_table").

I mistakingly left out a line in the code before, but this is an
example:

'************************************************* **
TableName = "Table_" & Model_No
FileName = model_path & "Model_" & Model_No
RangeName = "Model_" & Model_No & "_Range"

Table_16 = "Some_Table"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, & _
Eval("Table_" & Model_No), Eval("FileName"), True, Eval("RangeName")
'************************************************* **

[Bas Cost Budde]

John wrote:

Bas, thanks for the response. However, your suggestion of just using
the variables for the transferspreadsheet parameters is exactly my
problem. The variable would be a string...I need the "string" to be a
variable.

Zah. That looks like a double pointer to me, and VBA is not well suited
for this.

What process is this question part of? If I can understand what goal
you're after, I may come with "an elegant solution" (the ph on my
keyboard doesn't work, I always have to type the g instead ;-) )

I see you are importing a spreadsheet, probably many of them.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea