Solved: Trouble Storing Worksheet in a Variable

[LeoLee]

Greating People,

I have a Excel VBA code that will import data from Access database to Excel sheet.

For example, It will import Customer_ID to Excel Range("D4").

There is nothing wrong with this code, take a look:
Option Explicit
Sub Access_Data(Query As String)
'Requires reference to Microsoft ActiveX Data Objects xx Library

Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim MyConn, sSQL As String

Dim Rw As Long, Col As Long, C As Long
Dim MyField, Location As Range
Dim A As Long

'Set destination
Set Location = Worksheets("BackEnd").Range("D4")

MsgBox (Location)
'Set source
MyConn = "C:\Users\Desktop\Backup\Working\Bookings.mdb"
'Create query
sSQL = Query

'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
Set Rs = .Execute(sSQL)
End With

'Write RecordSet to results area
Rw = Location.Row
Col = Location.Column
C = Col
Do Until Rs.EOF
For Each MyField In Rs.Fields
Cells(Rw, C) = MyField
C = C + 1
Next MyField
Rs.MoveNext
Rw = Rw + 1
C = Col
Loop
Set Location = Nothing
Set Cn = Nothing
End Sub



However, I am facing a problem as this code will Import data to the active sheet Range("D4") instead of BackEnd sheet range("D4").

I am confident that it is because of the bottm code which only write to column and row but never specific the sheet.

'Write RecordSet to results area
Rw = Location.Row
Col = Location.Column
C = Col
Do Until Rs.EOF
For Each MyField In Rs.Fields
Cells(Rw, C) = MyField
C = C + 1
Next MyField
Rs.MoveNext
Rw = Rw + 1
C = Col
Loop
Set Location = Nothing
Set Cn = Nothing

I would really appreciate if someone could help me to define the sheet to import the data. Thanks!

:bow:

[Bob Phillips]

If you don't specify the sheet, it assumes the active sheet, so that should work.

[Kenneth Hobs]

When you don't use workbook or worksheet object as prefix to Cells(), it assumes activeworkbook and activesheet.

This puts it into the activeworkbook with a sheet's tab named BackEnd.
WorkSheets("BackEnd").Cells(Rw, C) = MyField

If you are getting all of the field values from the recordset, why not use CopyFromRecordSet()?

'http://www.excelkb.com/article.aspx?id=10091&cNode=1I7C3V
Sub Execute_Parameterized_SQL_Queries()
Dim cnt As adodb.Connection
Dim cmd As adodb.Command
Dim prm As adodb.Parameter
Dim rst As adodb.RecordSet
Dim stDB As String, stCon As String, stParamSQL As String
Dim vaNumber As Variant
Dim xlCalc As XlCalculation

'Change settings in order to increase the performance.
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

'Path to the database.
stDB = "E:\Arbetsmaterial\XLData1.mdb"

'Create the connection string.
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";" & _
"Jet OLEDB:Database Password=dennis;" & _
"Persist Security Info=False"

'Create the parameter string.
stParamSQL = "SELECT * FROM tblData WHERE [Number]=?"

'Instantiate the ADO COM's objects.
Set cnt = New adodb.Connection
Set cmd = New adodb.Command

'Open the connection.
cnt.Open stCon

'Set main properties of the command object.
With cmd
.ActiveConnection = cnt
.CommandText = stParamSQL
.CommandType = adCmdText
End With

'Retrieve the value to be used.
vaNumber = Trim(Application.InputBox(Prompt:="Please enter an article number:", _
Title:="Parameterized Queries", Type:=1))

'In the user cancel the operation.
If vaNumber = "False" Then GoTo Exithere

'Create and define the parameter.
Set prm = cmd.CreateParameter("Number", adInteger, adParamInput, Len(vaNumber), vaNumber)

cmd.Parameters.Append prm

'Instantiate the Recordset object and execute the command.
Set rst = cmd.Execute

'Check if the Recordset have any records.
If rst.EOF = True Or rst.BOF = True Then
MsgBox "No records match the number!", vbInformation
GoTo Exithere
Else
'Dump the records into the active worksheet.
With ActiveSheet.Cells(2, 1)
.CurrentRegion.ClearContents
.CopyFromRecordset rst
End With
End If

'Close the recordset and the connection.
rst.Close
cnt.Close
Exithere:

'Release objects from memory.
Set rst = Nothing
Set prm = Nothing
Set cmd = Nothing
Set cnt = Nothing

'Restore the settings.
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

[LeoLee]

H

[LeoLee]

Hi xld,

To answer your question, the reason why I need to specify another sheet is because this particular code is working in the backend which is hidden and also known as "back end processing" where it is not seen by the user. Hence, when this code is excuted, it is not at the sheet it intended to be.

Thanks!

Hi Kenneth,

Thank you for your reply! Really appreciate it alot. And your code help me to solve all the problems! Thanks a million! Just a quick note, what is the differences between the Access Import functions provided by you compared to mine? Is it in terms of the efficiency? Thanks a lot in educating me! So sorry to trouble you as the code I provided is also from the net.

P.S.: I am sorry as I forgot to credit the source of the Access_Data function that I got it from the internet. I got it from http://www.vbforums.com/.

So sorry, I have another question to ask which is similar to the problem in this thread.

There is nothing wrong with this code if it is executed in the active sheet, however, some error will occur when I am at the other sheet.
Take a look at this code:
NameRange is a Dynamic Name Range by using Offset functions.

Sub ColumnFormatting(ByVal NameRange As Range)
'Formatting column imported from DB

NameRange.Select 'specify the range which suits your purpose
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With

End Sub

I have also tried to make some amendment but there is still error in this particular code. I would appreciate if you could help me out with this line.

Thanks a million for your time!
Worksheets("RawBackEndProcess").Range("NameRange").Select 'specify the range which suits your purpose

[Bob Phillips]

Worksheets("RawBackEndProcess").Activate
Range("NameRange").Select

[LeoLee]

Hi xld,

Thanks you for your reply! However, I couldnt use this particular code as RawBackEndProcess sheet is in hidden mode. Hence, it cannot be activated. Is there any other way where that the code is still along this line however, doesnt require the sheet to be activated? For example, a referance that refer the sheets "RawBackEndProcess" and able to still call the name range in that sheet?

Worksheets("RawBackEndProcess").Activate

[mdmackillop]

You can refer to the named range directly, read from it or change values as required. e.g.
Sub Test()
Dim rng As Range, cel as range
Set rng = Sheets("RawBackEndProcess").Range("Data")
For Each cel In rng
MsgBox cel
Next
End Sub

[LeoLee]

Hi,

Thank you for your reply.

However, this error occurred:

"Application-defined or object-defined error".

I believe that the code will only work if I am on the location of the name range. If the page is hidden or is not activate, I couldnt call the name range.

I would appreciate if anyone could help be of help in this. thanks

[mdmackillop]

Did you change the named range "Data" in the sample to your own value?

[Kenneth Hobs]

Obviously, your worksheet name and range name must exist. If you have password protection or sheets hidden, that needs to be addressed as well. I see no reason for Selection or Activate. The sheet does need to be visible to change data.

I added a debug to show what your named range and address are. I also added a password for the worksheet. Since it is optional, you can use the commented line to call the Sub if no password was set.

Sub Test_ColumnFormatting()
ColumnFormatting Range("Data"), "ken"
'ColumnFormatting Range("Data")
End Sub
Sub ColumnFormatting(ByVal NameRange As Range, Optional pw As String = "")
'Formatting column imported from DB
Debug.Print NameRange.Name
Worksheets(NameRange.Worksheet.Name).Unprotect pw
With NameRange
.NumberFormat = "General"
.Value = .Value
End With
Worksheets(NameRange.Worksheet.Name).Protect pw
End Sub

[LeoLee]

Hi Kenneth,

Thanks so much for your help! It works like wonder!

Just a quick note, what is the different between the Import data from Access functions provided by you compared to mine?

Once again, thanks a million for your help!

[Kenneth Hobs]

The main difference is CopyFromRecordset which gets all of the field values for each record without iteration loops.

Your code iterates each field for each record. That is fine if you need that for discontinuous data ranges for example.

If you need the fieldnames, at least one iteration for a record is needed for either method.