Change absolute range reference to named range - VBA - GetData from closed book.

[shajan]

Hi Ninjas !

I am trying to pass a named range to the below code, but it pops an error.

I would like to replace "A179" with a named range.


GetData ThisWorkbook.Path & "asc.xlsm", "stock-out", _

"A179", Sheets("Sheet2").Range("A1"), True, True


----------------------

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _

SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)

' 30-Dec-2007, working in Excel 2000-2007

Dim rsCon As Object

Dim rsData As Object

Dim szConnect As String

Dim szSQL As String

Dim lCount As Long


' Create the connection string.

If Header = False Then

If Val(Application.Version) < 12 Then

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & SourceFile & ";" & _

"Extended Properties=""Excel 8.0;HDR=No"";"

Else

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & SourceFile & ";" & _

"Extended Properties=""Excel 12.0;HDR=No"";"

End If

Else

If Val(Application.Version) < 12 Then

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & SourceFile & ";" & _

"Extended Properties=""Excel 8.0;HDR=Yes"";"

Else

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & SourceFile & ";" & _

"Extended Properties=""Excel 12.0;HDR=Yes"";"

End If

End If


If SourceSheet = "" Then

' workbook level name

szSQL = "SELECT * FROM " & SourceRange$ & ";"

Else

' worksheet level name or range

szSQL = "SELECT * FROM [" & SourceSheet$ & "$ & SourceRange$ & ];"

End If


On Error GoTo SomethingWrong


Set rsCon = CreateObject("ADODB.Connection")

Set rsData = CreateObject("ADODB.Recordset")


rsCon.Open szConnect

rsData.Open szSQL, rsCon, 0, 1, 1


' Check to make sure we received data and copy the data

If Not rsData.EOF Then


If Header = False Then

TargetRange.Cells(1, 1).CopyFromRecordset rsData

Else

'Add the header cell in each column if the last argument is True

If UseHeaderRow Then

For lCount = 0 To rsData.Fields.Count - 1

TargetRange.Cells(1, 1 + lCount).Value = _

rsData.Fields(lCount).Name

Next lCount

TargetRange.Cells(2, 1).CopyFromRecordset rsData

Else

TargetRange.Cells(1, 1).CopyFromRecordset rsData

End If

End If


Else

MsgBox "No records returned from : " & SourceFile, vbCritical

End If

' Clean up our Recordset object.

rsData.Close

Set rsData = Nothing

rsCon.Close

Set rsCon = Nothing

Exit Sub

SomethingWrong:

MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _

vbExclamation, "Error"

On Error GoTo 0


End Sub


Function LastRow(sh As Worksheet)

On Error Resume Next

LastRow = sh.Cells.Find(What:="*", _

After:=sh.Range("A1"), _

Lookat:=xlPart, _

LookIn:=xlFormulas, _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious, _

MatchCase:=False).Row

On Error GoTo 0

End Function


Function Array_Sort(ArrayList As Variant) As Variant

Dim aCnt As Integer, bCnt As Integer

Dim tempStr As String


For aCnt = LBound(ArrayList) To UBound(ArrayList) - 1

For bCnt = aCnt + 1 To UBound(ArrayList)

If ArrayList(aCnt) > ArrayList(bCnt) Then

tempStr = ArrayList(bCnt)

ArrayList(bCnt) = ArrayList(aCnt)

ArrayList(aCnt) = tempStr

End If

Next bCnt

Next aCnt

Array_Sort = ArrayList

End Function


The above is from John Walkenbach's code.

Thanks.

 

[SirJB7]

Hi, shajan!


If the error pops up in line:

szSQL = "SELECT * FROM [" & SourceSheet$ & "$ & SourceRange$ & ];"

shouldn't it be:

szSQL = "SELECT * FROM [" & SourceSheet$ & "! & SourceRange$ & ];"

?


Regards!

 

[shajan]

Hi SirJB

Thanks !


This portion pops up : "MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _

vbExclamation, "Error"


when the sub reads as follows :


Sub GetData_Example1()

GetData ThisWorkbook.Path & "asc.xlsm", "stock-out", _

"stock_out_data", Sheets("Sheet2").Range("A1"), True, True

End Sub

 

[SirJB7]

Hi, shajan!


It's arriving there because of the statement:

On Error GoTo SomethingWrong


Try two things:

a) change MsgBox to:

MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile _

& vbCr & Err.Number & " - " & Err.Description, _

vbExclamation, "Error"

If this does give you a clue to the error, you're done.

b) comment temporarily the On Error statement and run code with F8, step by step

You'll be faced there with the actual error.


Regards!

 

[shajan]

Thanks SirJB !

I replaced the MsgBox, and I could read the error and amended the following line


szSQL = "SELECT * FROM [" & SourceSheet$ & "$ & SourceRange$ & ];"

to

szSQL = "SELECT * FROM [" & "$ & SourceRange$ & ];"


It is working fine now. Thanks for your quick reply and help !

 

[SirJB7]

Hi, shajan!

That was my first guess, but doing so you're invalidating the SourceSheet parameter as blank. Shouldn't you keep the "!" version instead?

However, glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!

 

[shajan]

Hi SirJB,

I tried that ..

szSQL = "SELECT * FROM [" & "! & SourceRange$ & ];"


and it adds ! to the named range viz., "!stock_out_data" and could not get the reference.

Hence, I changed it to $


Regards,

 

[SirJB7]

Hi, shajan!


The "!" was intended to replace the "$" in this original statement:

szSQL = "SELECT * FROM [" & SourceSheet$ & "$ & SourceRange$ & ];"


So as to qualify the range with the worksheet, like this:

WorksheetXXX!A1:B10

because in the original version it would have been:

WorksheetXXX$A1:B10


If you use the worksheet name to qualify the range, you should use "!" and not "$"; if you don't use it, you can't use the "!" and adding a "$" won't do any harm, unless your SourceRange$ contains fixed columns addresses -at least, but beginning with "$"- like "$A1:B10" or "$A$1:$B$10" or anything alike.


That's why it was correct the If construction pointed out in my first comment, just needed to change the "$" by the "!". Unless the SourceSheet$ variable content ends with a "!".


Regards!

 

[shajan]

Hi SirJB,

With that - I am getting an error "Type declaration character does not match declared data type.

Regards.

 

[SirJB7]

Hi, shajan!

Would it be possible for you to upload a sample file?

Regards!

 

[shajan]

Hi SirJB,

Here is the link to the files on SkyDrive

http://sdrv.ms/LXFzA9

http://sdrv.ms/OazXyH

There are 2 files named asc and ado

Regards,

 

[SirJB7]

Hi, shajan!


You're right.


As which is required for objects defined as ranges

Dim rng as Range

where if in other sheets you should use

Set rng = Worksheets("stock-out").Range("stock-out-data")

instead of

Set rng = Range("stock-out-data")

for defining the SQL string you don't need to use worksheet qualification, so the IF is not needed and you can leave this only sentence:

szSQL = "SELECT * FROM " & SourceRange$ & ";"

... no need of "$" prefix.


Hope it helps.


Regards!

 

[shajan]

Thank You SirJB,

The error Msg string was of real help.

Regards,

 

[SirJB7]

Hi, shajan!

Always welcome.

Regards!