Where's the mistake?

[Ramy27]

I'm doing something stupid, but i can't figure out where i'm going wrong...

Dim RangeA as Excel.Range

Set RangeA = Range(ExcelRange)
(I’m getting an error here- “method range of object global failed”)

ExcelRange here is a String in the form of
“’Sheet1’!A24”

 

[HarleyQuinn]

So when you used the code I provided in my last post did it not work?? Have you stepped through it and checked the values of the variables??

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[Ramy27]

Okay, let me re-state the problem for those who just joined

THE ERROR IS IN THE WAY WE DEFINE:

Set RangeA = Worksheets(ExcelSheet).Range(ExcelRange)

ExcelSheet="Sheet1"
ExcelRaneg="B1214"

 

[HarleyQuinn]

Hi again,

seems that my earlier solution appears not to have worked how about forgetting the

Set RangeA = Worksheets(ExcelSheet).Range(ExcelRange)

and using your original code with a slight modification.

Private Sub CommandButton1_Click()

Dim DBAddress As String
Dim ExcelAddress As String
Dim a As String
Dim ExcelRange As String
Dim SheetName As String
Dim NumOfRows As Double

Sheets("Sheet1").Activate
DBAddress = ActiveSheet.Range("C5")
ExcelAddress = ActiveSheet.Range("C2")
SheetName = ActiveSheet.Range("C3")
NumOfRows = CDbl(ActiveSheet.Range("F3"))

[red]ExcelRange = ActiveSheet.Range("C3") & "!" & Split(ActiveSheet.Range("C4"), ":", -1)(0) & ":" & ActiveSheet.Range("C3") & "!" & Split(ActiveSheet.Range("C4"), ":", -1)(1)[/red]

'MsgBox ExcelRange

a = Upload_To_Database(ExcelAddress, DBAddress, ExcelRange, SheetName, NumOfRows)
        
End Sub


__________________

Public Function Upload_To_Database(ExcelAddress As String, DBAddress As String, ExcelRange As String, SheetName As String, NumberOfRows As Double)

'On Error GoTo Errorhandler
Dim WorksheetNumber As Integer
Dim wb As Object 'Excel.Workbook
'Dim datecomp As DataTypeEnum
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim xSQLbuilder1 As SQL_INSERT_StringBuilder
Dim xSQLbuilder2 As SQL_INSERT_StringBuilder
Dim xSQLbuilder3 As SQL_INSERT_StringBuilder
Dim xSQLbuilder4 As SQL_INSERT_StringBuilder
Dim xSQLbuilder5 As SQL_INSERT_StringBuilder
Dim TimeRange As Excel.Range
Dim JobcodeRange As Excel.Range
Dim POrderRange As Excel.Range
Dim i As Long
Dim j, k As Long
Dim sqlstr
Dim wbstring As String
Dim LoginString As String
Dim workbookNeedsToBeClosed As Boolean
Dim wbtest As Excel.Workbook
Dim DRangeJob As Excel.Range
Dim ARangePO As Excel.Range
Dim DeleteSQL1 As String

Dim isopen As Boolean
Dim ExRange As Excel.Range
Dim RangeA As Excel.Range
Dim TRangeCC As Excel.Range
Dim ColumnName As String
ReDim MonthA(1 To 12) As String

'DBAddress=
'"C:\Documents and Settings\Wes\My Documents\Wesley's Documents\Tracs work\Time Sheets- Harry\Tracs Timesheets.mdb"

wbstring = ExcelAddress

Set conn = New ADODB.Connection

For Each wbtest In Excel.Workbooks
    If wbtest.FullName = wbstring Then
        Set wb = wbtest
        isopen = True
        Exit For
    End If
Next
  
If isopen = False Then
    Set wb = Excel.Workbooks.Open(wbstring)
    workbookNeedsToBeClosed = True
End If
wb.Activate

LoginString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " _
          & DBAddress & ";Jet OLEDB:System database=" _
          & ";User Id=" & "admin" _
          & "; Password=" & ""

conn.ConnectionString = LoginString
conn.Open



    
    
    'wb.Sheets(MonthA(k)).Names.Add Name:="TimeRangeC", RefersToR1C1:= _
        "=" & MonthA(k) & "!R8C2:R69C9"
    
    'Debug.Print
    'ActiveWorkbook.Sheets(SheetName).Select
    Set RangeA = Range(ExcelRange)
..................................
..................................
..................................

So now when you step through and mouse over the variable ExcelRange you should see something like:

"Sheet1!A1:Sheet1!B6"

How does that work for you?

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[Ramy27]

Thanks very much, I'm going to try that and will let you know. Much appreciated.

 

[HarleyQuinn]

Fingers crossed we can get this sorted for you

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[Rofeu]

Hi,

with ExcelSheet = "Sheet1" and ExcelRange = "A1:B6", Set RangeA = Sheets(ExcelSheet).Range(ExcelRange) should not result in an error. Maybe the error doesn´t come from the code. You might want to check some things:

1. do you have your excel in RC reference style? (this code won´t work if you do)

2. is Sheet1 the actual sheet name or the codename of the sheet?

Cheers,

Roel

 

[SkipVought]

Hi,

Looks like you're making it much too hard...

Dim RangeA As Excel.Range, ExcelRange As String

ExcelRange = "’Sheet1’!A2:D4"

Set RangeA = Sheets(Split(Replace(ExcelRange, "’", ""), "!")(0)).Range(Split(ExcelRange, "!")(1))

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]