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
--
Andrew
"JMB" wrote:
> 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" wrote:
>
> > 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
> >
> >
> > --
> > Andrew
> >
> >
> > "JMB" wrote:
> >
> > > try
> > > Set The_Sheet = Sheets(Sheets("Criteria").Range("B8").Text)
> > >
> > > "Andrew" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Yep.
> > > > >
> > > > > Thanks for the correction. (Copy and paste is bad...very bad!)
> > > > >
> > > > > JMB wrote:
> > > > > >
> > > > > > I think you need some set statements:
> > > > > >
> > > > > > Set The_Sheet = Sheets("Data")
> > > > > > Set The_Range = The_Sheet.Range("A1")
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > 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.
> > > > > > >
> > > > > > > Andrew wrote:
> > > > > > > >
> > > > > > > > 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
> > > > > > > >
> > > > > > > > --
> > > > > > > > Andrew
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
|
Similar Threads
