Correct structure

[Andrew]

Hi,

Can someone assist with the correct structure for the following:

Dim The_Sheet As ??????????????
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = .Range("A1")

Application.Goto Sheets("Data").Range("A1")
&
Sheets("Data").Range("A1").Offset(0, col).Value = MyDatabase.Fields
(col).Name

I want to replace the code 'Sheets("Data").Range("A1")' with the variables.

Andrew 150208

 

[Dave Peterson]

Dim The_Sheet As Worksheet
Dim The_Range As Range

The_Sheet = Sheets("Data")
The_Range = The_Sheet.Range("A1")

Application.Goto The_Range
The_Range.Offset(0, col).Value = MyDatabase.Fields(col).Name


But there's no reason to use the application.goto line.

 

[JMB]

I think you need some set statements:

Set The_Sheet = Sheets("Data")
Set The_Range = The_Sheet.Range("A1")

 

[Dave Peterson]

Yep.

Thanks for the correction. (Copy and paste is bad...very bad!)

I think you need some set statements:

Set The_Sheet = Sheets("Data")
Set The_Range = The_Sheet.Range("A1")

 

[Andrew]

Hi all,

I'm now trying to get the variable from another sheet. I am getting an
'Object required' error on this line. (Cell B8 on the 'Criteria' sheet
contains the word 'Data')

Set The_Sheet = "Sheets(""" & Sheets("Criteria").Range("B8").Text & """)"

 

[JMB]

try
Set The_Sheet = Sheets(Sheets("Criteria").Range("B8").Text)

Hi all,

I'm now trying to get the variable from another sheet. I am getting an
'Object required' error on this line. (Cell B8 on the 'Criteria' sheet
contains the word 'Data')

Set The_Sheet = "Sheets(""" & Sheets("Criteria").Range("B8").Text & """)"

 

[Andrew]

Hi,

Thanks for your last reply. Setting 'The_Sheet' works but setting
'The_Range1' returns 'Subscript out of range' error.

Dim The_Sheet As Worksheet
Dim The_Range1 As Range
Dim The_Range2 As Range

Set The_Sheet = Sheets(Sheets("Criteria").Range("B8").Text)
Set The_Range1 = Sheets(Sheets("Criteria").Range("B9").Text)


Sheets("Criteria").Range("B8").Text contains 'A1' and
Sheets("Criteria").Range("B9").Text contains 'A2'


Can you then help me replace Sheets("Data").Range("A1").Offset(0,
col).Value = ........' with the variables The_Sheet and The_Range1

 

[Dave Peterson]

I don't understand. Shouldn't one of those ranges contain a worksheet name?

Set The_Range1 = sheets("Data").range(sheets("Criteria").range("B8").value)

====
If b8 on criteria is equal to Data and if b9 on criteria is equal to A1, then

set The_Sheet = sheets(sheets("criteria").range("B8").value)
set The_Range1 = the_sheet.range(sheets("criteria").range("B9").value)

The_Sheet will point at the Data worksheet.
The_Range1 will point at A1 of the Data worksheet.

 

[JMB]

to address your last question - assuming The_Sheet points to "Data" and
The_Range1 points to cell A1 on the "Data" worksheet, you could use

The_Range1.Offset(0, col).Value = ???

No need to qualify with The_Sheet for this instruction as your range
variable is set up to point at "Data" cell A1 (I think - I share Dave's
confusion on what data is in cell B8 and B9).

Since B9 appears to contain a range reference (let's say "A2"), your
original code becomes:

Set The_Range1 = Sheets("A2")

I'm sure you do not have a worksheet named "A2", so you get subscript out of
range error.

 

[Andrew]

Firstly thanks to you both.


I will try to clarify what I've been wanting to achieve. I can see I
created some confusion.

I have a workbook with 2 sheets, CRITERIA and DATA.

CRITERIA is what I consider to be a user interface where the user keys into
column B.

Field (Column A) Value (Column B)
The_Path G:\00_CEN\Hierarchy
The_Filename ProductHierarchy 2000.mdb
The_Table Tbl_Co_Hier
The_Field Dept
The_Qualifier =
The_Criteria 940
The_Sheet Data
The_Range1 A1
The_Range2 A2
The_Fields *
Include_Field_Names TRUE
The_Clear_Range TRUE

DATA is simply the sheet name where the result of the query ends up and
comes from the variable 'The_Sheet'.

Here is my code that is now working (with thanks to you guys and Ron
DeBruin). I have more work to do (eg. the use of the field 'The_Range2') but
I should be ok now.

Sub Andrews_Test()
' Modified from example code created by Ron DeBruin.
Dim MyConnection As String
Dim MySQL As String
Dim MyDatabase As Object
Dim col As Integer
Dim The_Sheet As Worksheet
Dim The_Range1 As Range
Dim The_Range2 As Range

The_Path = Sheets("Criteria").Range("B2").Text
The_Filename = Sheets("Criteria").Range("B3").Text
The_Table = Sheets("Criteria").Range("B4").Text
The_Field = Sheets("Criteria").Range("B5").Text
The_Qualifier = Sheets("Criteria").Range("B6").Text
The_Criteria = Sheets("Criteria").Range("B7").Text
Set The_Sheet = Sheets(Sheets("Criteria").Range("B8").Text)
The_Sheet1 = Sheets("Criteria").Range("B8").Text
Set The_Range1 = The_Sheet.Range(Sheets("Criteria").Range("B9").Value)
Set The_Range2 = The_Sheet.Range(Sheets("Criteria").Range("B10").Value)
The_Fields = Sheets("Criteria").Range("B11").Text
Include_Field_Names = Sheets("Criteria").Range("B12").Text
The_Clear_Range = Sheets("Criteria").Range("B13").Text

Sheets(The_Sheet1).Select
'If ClearRange = True it clear all cells on that sheet first
If The_Clear_Range = True Then Range(The_Range1.Address, "IV" &
Rows.Count).ClearContents

'Select the DestSheetRange where you paste the records
Application.Goto The_Range1

'Create connection string
MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
MyConnection = MyConnection & "Data Source=" & The_Path & "\" &
The_Filename & ";"
MySQL = "SELECT " & The_Fields & " FROM " & The_Table & " WHERE [" &
The_Field & "] " & The_Qualifier & The_Criteria

'Open the database and copy the data
'On Error GoTo SomethingWrong
Set MyDatabase = CreateObject("adodb.recordset")
MyDatabase.Open MySQL, MyConnection, 0, 1, 1
' Check to make sure we received data and copy the data
If Not MyDatabase.EOF Then
'If FieldNames = True copy the field names and records
'If = False copy only records
If Include_Field_Names = True Then
For col = 0 To MyDatabase.Fields.Count - 1
The_Range1.Offset(0, col).Value = MyDatabase.Fields(col).Name
Next
The_Range1.Offset(1, 0).CopyFromRecordset MyDatabase
Else
The_Range2.CopyFromRecordset MyDatabase
End If
Else
MsgBox "No records returned from : " & MyDatabaseFilePathAndName,
vbCritical
End If
MsgBox ("Finished...")
MyDatabase.Close
Set MyDatabase = Nothing
Exit Sub

SomethingWrong:
On Error GoTo 0
Set MyDatabase = Nothing
MsgBox "Error copying data", vbCritical, "Test Access data to Excel"
End Sub