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]

Try setting ExcelRange as:

"Sheet1!A2:D4"

i.e. Without the single quotes.

Hope this helps

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

 

[HarleyQuinn]

Saying that, it still works fine on my machine with them in.

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

 

[Ramy27]

Thanks, but it still gives the same error message

 

[HarleyQuinn]

Could you post some more of your code to help us see what it might be that's going wrong please?

Cheers

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

 

[Ramy27]

i suspect the error is somewhere here

.....= Range(ExcelRange)

 

[Ramy27]

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"))

ExcelRange = ActiveSheet.Range("C3") & "!" & ActiveSheet.Range("C4")

'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)
..................................
..................................
..................................

 

[HarleyQuinn]

I don't think this is the problem but it does seem to be an error in your code:

ExcelRange = [red]"" & [/red]ActiveSheet.Range("C3") & "[red]:[/red]" & ActiveSheet.Range("C4")[red] & ""[/red]

Does that make a difference?

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

 

[HarleyQuinn]

In fact, I do think that's the problem.

Hope this helps

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

 

[Ramy27]

no, that's not the problem

ActiveSheet.Range("C3")= "Sheet1"
ActiveSheet.Range("C4") ="C212"

 

[HarleyQuinn]

OK. How about:

Dim DBAddress As String
Dim ExcelAddress As String
Dim a As String
Dim ExcelRange As String
[red]Dim ExcelSheet As String
[/red]
Dim SheetName As String
Dim NumOfRows As Double
Dim RangeA As Excel.Range

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

[red]ExcelSheet = ActiveSheet.Range("C3")
[/red]
[red]ExcelRange = ActiveSheet.Range("C4")
[/red]
'ExcelRange = "'Sheet1'!A2:D4"
'MsgBox ExcelRange
Set RangeA = [red]Worksheets(ExcelSheet)[/red].Range(ExcelRange)

Hope this helps

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

 

[Ramy27]

no, it does not work...i'm totally messed up

 

[Ramy27]

the error is still in

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

Help!!!!!!

 

[HarleyQuinn]

Are you passing ExcelShhet into the Function?

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

 

[Ramy27]

Should that code you posted go in the Function or Coomandbutton_click?

 

[Ramy27]

THE ERROR IS IN THE WAY WE DEFINE:

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

 

[HarleyQuinn]

Sorry to be unspecific there.

Here is you code (slightly modified to exclude the DB connections etc.) that I have working on my machine.

Private Sub CommandButton1_Click()


Dim DBAddress As String
Dim ExcelAddress As String
Dim a As String
Dim ExcelRange As String
Dim ExcelSheet As String
Dim SheetName As String
Dim NumOfRows As Double
Dim RangeA As Excel.Range

Sheets("Sheet1").Activate
DBAddress = ActiveSheet.Range("C5")
ExcelAddress = ActiveSheet.Range("C2")
SheetName = ActiveSheet.Range("C3")
NumOfRows = CDbl(ActiveSheet.Range("F3"))
ExcelSheet = ActiveSheet.Range("C3")
ExcelRange = ActiveSheet.Range("C4")
'ExcelRange = "'Sheet1'!A2:D4"
'MsgBox ExcelRange
'Set RangeA = Worksheets(ExcelSheet).Range(ExcelRange)

'Debug.Print RangeA

'RangeA.Value = "A"

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

Public Function Upload_To_Database(ExcelAddress As String, DBAddress As String, ExcelRange As String, SheetName As String, NumberOfRows As Double, ExcelSheet As String)
Dim RangeA As Excel.Range
    'wb.Sheets(MonthA(k)).Names.Add Name:="TimeRangeC", RefersToR1C1:= _
        "=" & MonthA(k) & "!R8C2:R69C9"
    
    'Debug.Print
    'ActiveWorkbook.Sheets(SheetName).Select
    Set RangeA = Worksheets(ExcelSheet).Range(ExcelRange)
    RangeA.Value = "A"
End Function

Could you try that at your end and see if it works?

Hope this helps

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

 

[HarleyQuinn]

THE ERROR IS IN THE WAY WE DEFINE:

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

In Excel 2003 which I am using there is no problem with that at all.

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

 

[RoyVidar]

I guess that's the question - is this automation or within Excel?

Roy-Vidar

 

[Ramy27]

I'm using Excel 2002 SP2- perhaps that's where the problem is.