Method Range Of Object &"_Global&" Failed

'Method &"Range&" Of Object &"_Global&" Failed.' ???
Hi!

I'm trying to create a VB app that interfaces with an Excel file. I can access the file okay. Part of the code is finding the next empty row in a range. I keep getting an error in the set range part of the code.


Code:
Dim rangeSearch As Range

xlSheet.Columns("A").Select
Set rangeSearch = Selection.Find(what:="*", After:=Range("A1"), lookin:=xlFormulas, _
lookat:=xlWhole, searchorder:=xlByRows, SearchDirection:=xlNext)
If Not rangeSearch Is Nothing Then
FindLastRow = rangeSearch.Row
MsgBox rangeSearch.Row
End If

The error is 'Method "Range" of object "_Global" failed.'

Anyone have any idea what this means?

Thanks!!!

---

VB Error: 1004 Method Range Of Object &"_Global&" Failed
Hi All,
I am using MS Visual Basic for Applications (VBA) for Excel and I am very new to this programming. In fact, I merely copied a program from a book. I apologise for the length of it, but basically I am getting error "Run-time error '1004' Method "Range" of object "_Global" failed. It is happening at the following lines:

Range("Accuracy").Select
Range("Payoff").Select
Range("Money_Mgt_Approach").Select
etc etc

Would someone be kind enough to assist please? Any help in layman terms would be appreciated.

Cheers, Phil.

The program is as follows:

Code:
Sub Simulate_Risk_of_Ruin()

'
'Define variables
'
Const NoRecords = 10001
Dim TradeResult(NoRecords) As Long
Dim EquityCurve(NoRecords) As Long
Dim Accuracy As Variant
Dim PayOff_Ratio As Variant
Dim Money_Mgt_Approach As String
Dim Fixed_Percent_Risked As Variant
Dim Ruin_Point_DrawDown As Variant
Dim Account_Start As Variant
Dim Account_Balance As Variant
Dim Account_New_High As Variant
Dim Account_DrawDown As Variant
Dim Account_DrawDown_Percent As Variant
Dim Win_or_Loss As Variant
Dim Probability_Of_Ruin As Variant
Dim RowNumber As Variant
Dim Unit_Of_Money As Integer
Dim Fixed_Dollar_Risk As Variant
Dim Number_Of_Trades As Long
Dim Number_of_Losses_Before_Ruin As Long
Dim Number_of_Trades_Since_Account_High As Long
Dim i As Long
Dim j As Long
Dim x As Long

'
'Freeze Screen
'
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'
'Load Variables from Spreadsheet
'
'Load Accuracy Rate
Sheets("RiskOfRuin").Select
[b]Range("Accuracy").Select[/b]
Accuracy = Selection

'Load the Average Win to Average Loss Payoff Ration
[b]Range("Payoff").Select[/b]
PayOff_Ratio = Selection

'Load Money Management Approach
[b]Range("Money_Mgt_Approach").Select[/b]
If ActiveCell = 1 Then
Money_Mgt_Approach = "Fixed Percentage Risk Money Mgt"
Else
Money_Mgt_Approach = "Fixed Dollar Risk Money Mgt"
End If

'Load Starting Account Size
[b]Range("Start_Capital").Select[/b]
Account_Start = Selection

'Load Fixed Percentage Rate of account balance risked on each trade
[b]Range("FixedPercentage").Select[/b]
Fixed_Percent_Risked = Selection

'Load the Percentage Draw Down rate we define ruin as
Range("Ruin").Select
Ruin_Point_DrawDown = Selection

'Load the number of units of money we have in our account
Range("Unit_Of_Money").Select
Unit_Of_Money = Selection

'
'Clear the Arrays
'
For i = 1 To NoRecords
TradeResult(i) = Empty
EquityCurve(i) = 0
Next i
'
'Begin Simulating Probability of Ruin
'
Number_Of_Trades = 1
Account_Balance = Account_Start
Account_New_High = Account_Start
Account_DrawDown_Percent = 0
Number_of_Losses_Before_Ruin = 0
Fixed_Dollar_Risk = Account_Start / Unit_Of_Money

i = 1
j = 1
x = 0

Do Until Account_DrawDown_Percent >= Ruin_Point_DrawDown Or EquityCurve(i - 1) > 200000000 Or x >= 10000

'Check For New Equity High and reset number of losing trades to zero
If Account_Balance > Account_New_High Then
Account_New_High = Account_Balance
Number_of_Losses_Before_Ruin = 0
Number_of_Trades_Since_Account_High = 0
End If

'Generate random number to see whether a trade wins or loses
Win_or_Loss = Rnd

'Check for a Win
If Win_or_Loss >= (1 - Accuracy) Then
'We have a WIN!
'Calculate the profit
If Money_Mgt_Approach = "Fixed Percentage Risk Money Mgt" Then
TradeResult(j) = ((Fixed_Percent_Risked * Account_Balance) * PayOff_Ratio)
End If

If Money_Mgt_Approach = "Fixed Dollar Risk Money Mgt" Then
TradeResult(j) = Fixed_Dollar_Risk * PayOff_Ratio
End If

'Add to the equity curve
If i = 1 Then
EquityCurve(i) = Account_Start
i = i + 1
EquityCurve(i) = EquityCurve(i - 1) + TradeResult(j)
Else
EquityCurve(i) = EquityCurve(i - 1) + TradeResult(j)
End If

'We have a LOSS!
'Calculate the loss
If Money_Mgt_Approach = "Fixed Percentage Risk Money Mgt" Then
TradeResult(j) = -(Fixed_Percent_Risked * Account_Balance)
End If

If Money_Mgt_Approach = "Fixed Dollar Risk Money Mgt" Then
TradeResult(j) = -Fixed_Dollar_Risk
End If

'Add to the equity curve
If i = 1 Then
EquityCurve(i) = Account_Start
i = i + 1
EquityCurve(i) = EquityCurve(i - 1) + TradeResult(j)
Else
EquityCurve(i) = EquityCurve(i - 1) + TradeResult(j)
End If

'Add to our account balance
Account_Balance = Account_Balance + TradeResult(j)

'Calculate current drawdown and percentage drawdown
Account_DrawDown = Account_New_High - Account_Balance
Account_DrawDown_Percent = Account_DrawDown / Account_New_High

'Calculate the number of losses before ruin
Number_of_Losses_Before_Ruin = Number_of_Losses_Before_Ruin + 1

End If

'Calculate number of trades
Number_Of_Trades = Number_Of_Trades + 1
Number_of_Trades_Since_Account_High = Number_of_Trades_Since_Account_High + 1

'Increase counters
x = x + 1
j = j + 1
i = i + 1
Loop


'Calculate Probability of Ruin
Probability_Of_Ruin = Number_of_Losses_Before_Ruin / Number_of_Trades_Since_Account_High

'If the Equity Curve is above $200m or we have simulated 10,000 trades
'then we will assume ruin has been avoided.
If EquityCurve(i - 1) > 200000000 Or x >= 10000 Then
Probability_Of_Ruin = 0
End If

'Enter Probability of Ruin in Spreadsheet
Sheets("RiskOfRuin").Select
Range("Probability").Select
ActiveCell = Probability_Of_Ruin
Selection.Style = "Percent"

'
'Print Equity Curve
'
'Clear Previous Equity Curve
Columns("AA:AA").Select
Selection.Clear

'Print Equity Curve in Spreadsheet - Column AA
i = 1
Do Until i >= Number_Of_Trades + 1
Sheets(1).Cells(i, 27).Value = EquityCurve(i)
i = i + 1
Loop

'Change Chart Range
Range("AA1").Select
Selection.End(xlDown).Select
RowNumber = ActiveCell.Row

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=RiskOfRuin!R1C27:R" & RowNumber & "C27"
ActiveWindow.Visible = False
Windows("0_Risk_of_Ruin_Simulator.xls").Activate

'Move cursor to the Probability of Ruin calculation
Range("B22").Select

'
'Refresh Screen
'
Application.DisplayAlerts = True
Application.ScreenUpdating = True

'End of Simulator


End Sub

---

Method &"Range&" Of Object &"_Worksheet&" Failed....???
What ever I do on this line:

Sheet1.Range(strColumn & 3).Value = strValue

I get an error:

Method "Range" of object "_Worksheet" failed.


I tried at least 10 different ways! What's wrong?? THIS line works fine:

LColorCells = "A" & Lrow & ":" & "J" & Lrow
    Sheet1.Range(LColorCells).Font.Color = vbwhite

So what's wrong with the above line??

I've tried for example:

Sheet1.Range("" &strColumn & str(3)).Value = strValue
Sheet1.Range("'" &strColumn & 3 &"'").Value = strValue

and more...

What's strange is that THIS WORKS:

Sheet1.Range("D" & 3).Value = strValue

But of course, he would only use Column "D" and I need to loop it cause strColumn is changing.

Can someone tell me what's wrong?
Thanks!




Edited by - Zvi on 11/8/2006 10:53:20 PM

---

&"Method 'Range' Of Object '_Globa' Failed&" ??
Hi!

Im trying to make an app that interacts with an Excel file. I can access the file okay. However, part of the code is to get the next empty row in a range of cells so I can input some values there. I keep getting the error in my subject on the Set rangeSearch line...


Code:
Dim rangeSearch As Range

xlSheet.Columns("A").Select
Set rangeSearch = Selection.Find(what:="*", After:=Range("A1"), lookin:=xlFormulas, _
lookat:=xlWhole, searchorder:=xlByRows, SearchDirection:=xlNext)
If Not rangeSearch Is Nothing Then
FindLastRow = rangeSearch.Row
MsgBox rangeSearch.Row
End If



Any help would be appreciated.

Thanks!!!

---

Method 'Range' Of Object '_Global' Failed
Ok, I've searched and found a ton of threads on this error, but none have helped me fix my problem (which is basically a lack of understanding). I've been working with excel automation for only a short time, so I've got a lot to learn about it. I'm trying to call an Excel macro from my VB6 application. It gives me the error "Method 'Range' of object '_Global' failed". When I run the macro from inside Excel it runs flawlessly, but when I try and call the macro from VB it gives me the error. I assume it's a declarations thing or something. Please help! Here is how I am calling my macro named "test":
Code:
Dim objExcel As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRng As Excel.Range
Set objExcel = New Excel.Application
Set objWB = GetObject("C:faxingreport.xls")
Set objWS = objWB.Worksheets("Sheet1")
objWB.Application.Run "test"
Here is the line in the macro that it errors out on:
Code:
Range("A" & varRow).Select
ActiveCell.FormulaR1C1 = "Name"
Also, is there a faster method than using .select & activecell? Thanks!

---

Method 'Range' Of Object '_global' Failed
i am new to vb and i am writing a small code to get records from access database based on a selection criteria and then create an excel sheet using that data.

the sequence is as follows:
-Starting the VB application goes to main selection screen
-Select a particular record and create excel sheet.
-creates an excel sheet , saves it and control comes back to main selection screen.
now if i select another record and try to create an excel sheet i get an error " error 1004 method 'Range' of object '_global' failed'

but if i go out of the vb application and select another recoed i do not get this error . can some tell me how to code so as not ot get the error.
thank you in advance

---

VB Get Error Method 'Range' Of Object '_Global' Failed
Hi All, I have create a Excel VB Application that means when i load the excel file data to database the first time can load in the database but after go back to load again the file data it have error as

Method 'Range' of Object '_Global' failed

Bellow is the code that i have write


----

Quote:





Private Sub cmdLoad_Click()

On Error GoTo errHandler:
Dim xlsApp As Object
Dim xlsWB1 As Object
Dim xlsWS1 As Object
Dim strPath As String
ProgressBar1.Value = 1
dataFormat = "New"
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Visible = False
strPath = File1.Path & "" & File1.FileName
Set xlsWB1 = xlsApp.Workbooks.Open(strPath)
Set xlsWS1 = xlsWB1.Worksheets("Scrap IC")
Call WriteToLogFile1(strPath)
Dim row As Integer
Dim maxrow1 As Integer
Dim strSQL As String
Dim lngRecsAff As Long
Dim PBarCounter As Integer

maxrow1 = Range("A65536").End(xlUp).row
strSQL1 = "Insert Into tblscrap_Archive Select * From tblscrap "
adocn.Execute strSQL1, lngRecsAff, adExecuteNoRecords

strSQL2 = "Delete tblscrap "
adocn.Execute strSQL2, lngRecsAff, adExecuteNoRecords

PBarCounter = 0

For row = 2 To maxrow1
ProgressBar1.Refresh
ProgressBar1.Max = maxrow1 + 3 + PBarCounter
ProgressBar1.Value = PBarCounter

strSQL3 = "Insert Into tblscrap(KTC_PART_No, IC_PN, KTC_WO, COO, Sheet_No, NANYA_WO, Qty, Scrap_IC_ID)Values('" & xlsWS1.Cells(row, 1).Value & "','" & xlsWS1.Cells(row, 2).Value & "','" & xlsWS1.Cells(row, 3).Value & "','" & xlsWS1.Cells(row, 4).Value & "','" & xlsWS1.Cells(row, 5).Value & "','" & xlsWS1.Cells(row, 6).Value & "','" & xlsWS1.Cells(row, 7).Value & "','" & xlsWS1.Cells(row, 8).Value & "')"
adocn.Execute strSQL3, lngRecsAff, adExecuteNoRecords
PBarCounter = PBarCounter + 5
Next

strSQL4 = "Update tblscrap set Date_Load = '" & Date + Time & "', File_Name ='" & strPath & "' "
adocn.Execute strSQL4, lngRecsAff, adExecuteNoRecords
'odocn.Close
'Set cn1 = Nothing

xlsWB1.Close
xlsApp.Quit
Set xlsApp = Nothing
Set xlsWB1 = Nothing
Set xlsWS1 = Nothing
Me.Hide
ScrapIC.Show
Exit Sub
errHandler:
'MsgBox "An unknown error occurred while Passing the Excel. Sorry about that!!", vbCritical, "Error"
MsgBox Err.Description, vbCritical, "Error"
'xlsWB1.Close
xlsApp.Quit
Set xlsApp = Nothing
'Set xlsWB1 = Nothing
Set xlsWS1 = Nothing
End Sub





----
Hope can help me solve this problem
thanks

---

Method 'Range' Of Object '_Global' Failed Error
I am trying to write a simple VB macro for an excel sheet that takes values from certain cells and pastes them into one column. I keep getting the following error:

Method 'Range' of object '_Global' failed

Any help would be greatly appreciated. Thanks.

Below is my code:

Sub Test()

    
    Dim cellNumber As Integer
    Dim pasteNumber As Integer
    cellNumber = 1
    pasteNumber = 0
    counter = 0
    Dim rangeString As String
    Dim pasteString As String
    
    
    Do
    cellNumber = cellNumber + 8
    pasteNumber = pasteNumber + 1
    
    rangeString = E & cellNumber
    pasteString = Y & pasteNumber
    
    Range("rangeString").Select <---This is where I am getting my error
    Selection.Cut
    Range("pasteString").Select <----Here too
    ActiveSheet.Paste
    
        If cellNumber > 1000 Then counter = 1
        
        
        
    Loop Until counter = 1
    
    
End Sub







Edited by - giggs22 on 7/14/2005 12:39:01 PM

---

Error 1004 Method 'Range' Of Object '_Global' Failed
I have an Excel spreadsheet that gives me an Error 1004 Method 'Range' of object '_Global' failed at the following line.

startCell = Worksheets(worksheetname).Cells(Range(startCell).Row + 2, 1).Address

Can anyone help?

Thanks,
Bernie

---

I Get Run-time Error '1004' Method 'Range' Of Object '_Global' Failed
I have a program that should fetch the cell with the desired value.
It should also give more than one result.


Code:
Public Sub FindCell()
Dim objExcel As ExCel.Application
Dim objXLS As ExCel.Sheets
Dim AR(1000) As Variant '1 To 1000, 1 To 2) As Long
Dim i As Long
Dim LastRow As Variant
Dim Rng As Range
Dim rgLast As Range
Dim LastRange As Range
Dim FirstAddress As Variant

Set objXLS = Nothing
Set objExcel = Nothing
Set objExcel = New ExCel.Application


SetAttr "c:excelfileexcel.xls", vbReadOnly

objExcel.Workbooks.Open FileName:="c:excelfileexcel.xls"
Set rgLast = Range("A1").SpecialCells(xlCellTypeLastCell)
If rgLast.Row = 65536 Then
LastRow = rgLast.Row + 1
[b] Set LastRange = Range(LastRow + 1, 1)[/b]
Else
Set LastRange = rgLast
LastRow = rgLast.Row
End If
Set Rng = LastRange.Cells(LastRow, 1)
i = 1
With Worksheets(1).Range("a1")
Set Rng = Range("A1", LastRange.Cells(LastRow, 1)).fIND(What:=Form1.Text1.Text, _
After:=LastRange.Cells(LastRow, 1), lookin:=xlValues, LookAt:=xlPrevious)
AR(i) = Rng.Row
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
i = i + 1
AR(i) = Rng.Row
Set Rng = Range("A1", LastRange.Cells(LastRow, 1)).FindNext(After:=Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
End With

MsgBox ("Search Ended. " & i - 1 & " Certificates Found")
SetAttr "c:excelfileexcel.xls", vbNormal

End Sub

Recently this code started to malfunction and I don't know why.
I get this error:

Run-time error '1004' Method 'Range' of object '_Global' failed

It returns this error at the bold line in my code above.

It used to work -so I am at a loss

I'm guessing it has to do with the range I use - I basically state something like get last cell, then search column one -which ranges from 1 to lastcell including YOURself.
Methinks it has to do with the magical numer of 65536, which is, coincidentally, the total numer of rows excel can handle -it is also- in the same order of serendipity 512 times 128

But still - I don't know why this happens, I don't know exactly what "Run-time error '1004' Method 'Range' of object '_Global' failed"

While debugging I noticed it always thinks the last cell is 65536, which is in a sense untrue -this is the last empty cell; it is supposed to find the last cell that has a value so I emptied the apparently unfilled cells, to no avail. can anybody help me?

QC

---

Run-Time Error '1004' Method 'Range' Of Object '_Global' Failed
I have the following code in a macro in an Excel worksheet and I get the Run-Time Error '1004' Method 'Range' of object '_Global' failed message box.

Union(Range("A5,A12,A19,A26,A33,A40,A47,A54,A61,A68,A75,A82,A89,A96,A1 03,A110,A117,A124,A131,A138,A145,A152,A159,A166,A173,A180,A187,A194,A2 01,A208,A215,A222,A229,A236,A243,A250,A257,A264,A271,A278,A285,A292,A2 99,A306,A313,A320,A327,A334,A341,A348,A355,A362,A369,A376,A383,A390,A3 97,A404,A411,A418,A425,A432,A439,A446,A453,A460,A467,A474,A481,A488,A4 95,A502,A509,A516,A523,A530,A537,A544,A551,A558,A565,A572,A579,A586,A5 93,A600,A607,A614,A621,A628,A635,A642,A649,A656,A663,A670,A677,A684,A6 91,A698,A705,A712,A719,A726,A733,A740,A747,A754,A761,A768,A775,A782,A7 89,A796,A803"), _
Range("A810,A817,A824,A831,A838,A845,A852,A859,A866,A873,A880,A887,A89 4,A901,A908,A915,A922,A929,A936,A943,A950,A957,A964,A971,A978,A985,A99 2,A999,A1006,A1013,A1020,A1027,A1034,A1041,A1048,A1055,A1062,A1069,A10 76,A1083,A1090,A1097,A1104,A1111,A1118,A1125,A1132,A1139,A1146,A1153,A 1160,A1167,A1174,A1181,A1188,A1195,A1202,A1209,A1216,A1223,A1230,A1237 ,A1244,A1251,A1258,A1265,A1272,A1279,A1286,A1293,A1300,A1307,A1314,A13 21,A1328,A1335,A1342,A1349,A1356,A1363,A1370,A1377,A1384,A1391,A1398,A 1405,A1412,A1419,A1426,A1433,A1440,A1447,A1454,A1461,A1468,A1475,A1482 ,A1489,A1496,A1503,A1510,A1517,A1524,A1531,A1538,A1545,A1552,A1559,A15 66,A1573,A1580,A1587,A1594,A1601"), _
Range("A1608,A1615,A1622,A1629,A1636,A1643,A1650,A1657,A1664,A1671,A16 78,A1685,A1692,A1699,A1706,A1713,A1720,A1727,A1734,A1741,A1748,A1755,A 1762,A1769,A1776,A1783,A1790,A1797,A1804,A1811,A1818,A1825,A1832,A1839 ,A1846,A1853,A1860,A1867,A1874,A1881,A1888,A1895,A1902,A1909,A1916,A19 23,A1930,A1937,A1944,A1951,A1958,A1965,A1972,A1979,A1986,A1993,A2000,A 2007,A2014,A2021,A2028,A2035,A2042,A2049,A2056,A2063,A2070,A2077,A2084 ,A2091,A2098,A2105,A2112,A2119,A2126,A2133,A2140,A2147,A2154,A2161,A21 68,A2175,A2182,A2189,A2196,A2203,A2210,A2217,A2224,A2231,A2238,A2245,A 2252,A2259,A2266,A2273,A2280,A2287,A2294,A2301,A2308,A2315,A2322,A2329 ,A2336,A2343,A2350,A2357,A2364,A2371,A2378,A2385,A2392,A2399,A2406"), _
Range("A2413,A2420,A2427,A2434,A2441,A2448,A2455,A2462,A2469,A2476,A24 83,A2490,A2497,A2504,A2511,A2518,A2525,A2532,A2539,A2546,A2553,A2560,A 2567,A2574,A2581,A2588,A2595,A2602,A2609,A2616,A2623,A2630,A2637,A2644 ,A2651,A2658,A2665,A2672,A2679,A2686,A2693,A2700,A2707,A2714,A2721,A27 28,A2735,A2742,A2749,A2756,A2763,A2770,A2777,A2784,A2791,A2798,A2805,A 2812,A2819,A2826,A2833,A2840,A2847,A2854,A2861,A2868,A2875,A2882,A2889 ,A2896,A2903,A2910,A2917,A2924,A2931,A2938,A2945,A2952,A2959,A2966,A29 73,A2980,A2987,A2994,A3001,A3008,A3015,A3022,A3029,A3036,A3043,A3050,A 3057,A3064,A3071,A3078,A3085,A3092,A3099,A3106,A3113,A3120,A3127,A3134 ,A3141,A3148,A3155,A3162,A3169,A3176,A3183,A3190,A3197,A3204"), _
Range("A3211,A3218,A3225,A3232,A3239,A3246,A3253,A3260,A3267,A3274,A32 81,A3288,A3295,A3302,A3309,A3316,A3323,A3330,A3337,A3344,A3351,A3358,A 3365,A3372,A3379,A3386,A3393,A3400,A3407,A3414,A3421,A3428,A3435,A3442 ,A3449,A3456,A3463,A3470,A3477,A3484,A3491,A3498,A3505,A3512,A3519,A35 26,A3533,A3540,A3547,A3554,A3561,A3568,A3575,A3582,A3589,A3596,A3603,A 3610,A3617,A3624,A3631,A3638,A3645,A3652,A3659,A3666,A3673,A3680,A3687 ,A3694,A3701,A3708,A3715,A3722,A3729,A3736,A3743,A3750,A3757,A3764,A37 71,A3778,A3785,A3792,A3799,A3806,A3813,A3820,A3827,A3834,A3841,A3848,A 3855,A3862,A3869,A3876,A3883,A3890,A3897,A3904,A3911,A3918,A3925,A3932 ,A3939,A3946,A3953,A3960,A3967,A3974,A3981,A3988,A3995,A4002"), _
Range("A4009,A4016,A4023,A4030,A4037,A4044,A4051,A4058,A4065,A4072,A40 79,A4086,A4093,A4100,A4107,A4114,A4121,A4128,A4135,A4142,A4149,A4156,A 4163,A4170,A4177,A4184,A4191,A4198,A4205,A4212,A4219,A4226,A4233,A4240 ,A4247,A4254,A4261,A4268,A4275,A4282,A4289,A4296,A4303,A4310,A4317,A43 24,A4331,A4338,A4345,A4352,A4359,A4366,A4373,A4380,A4387,A4394,A4401,A 4408,A4415,A4422,A4429,A4436,A4443,A4450,A4457,A4464,A4471,A4478,A4485 ,A4492,A4499,A4506,A4513,A4520,A4527,A4534,A4541,A4548,A4555,A4562,A45 69,A4576,A4583,A4590,A4597,A4604,A4611,A4618,A4625,A4632,A4639,A4646,A 4653,A4660,A4667,A4674,A4681,A4688,A4695,A4702,A4709,A4716,A4723,A4730 ,A4737,A4744,A4751,A4758,A4765,A4772,A4779,A4786,A4793,A4800,A4807"), _
Range("A4814,A4821,A4828,A4835,A4842,A4849,A4856,A4863,A4870,A4877,A48 84,A4891,A4898,A4905,A4912,A4919,A4926,A4933,A4940,A4947,A4954,A4961,A 4968,A4975,A4982,A4989,A4996,A5003,A5010,A5017,A5024,A5031,A5038,A5045 ,A5052,A5059,A5066,A5073,A5080,A5087,A5094,A5101,A5108,A5115,A5122,A51 29,A5136,A5143,A5150,A5157,A5164,A5171,A5178,A5185,A5192,A5199,A5206,A 5213,A5220,A5227,A5234,A5241,A5248,A5255,A5262,A5269,A5276,A5283,A5290 ,A5297,A5304,A5311,A5318,A5325,A5332,A5339,A5346,A5353,A5360,A5367,A53 74,A5381,A5388,A5395,A5402,A5409,A5416,A5423,A5430,A5437,A5444,A5451,A 5458,A5465,A5472,A5479,A5486,A5493,A5500,A5507,A5514,A5521,A5528,A5535 ,A5542,A5549,A5556,A5563,A5570,A5577,A5584,A5591,A5598,A5605"), _
Range("A5612,A5619,A5626,A5633,A5640,A5647,A5654,A5661,A5668,A5675,A56 82,A5689,A5696,A5703,A5710,A5717,A5724,A5731,A5738,A5745,A5752,A5759,A 5766,A5773,A5780,A5787,A5794,A5801,A5808,A5815,A5822,A5829,A5836,A5843 ,A5850,A5857,A5864,A5871,A5878,A5885,A5892,A5899,A5906,A5913,A5920,A59 27,A5934,A5941,A5948,A5955,A5962,A5969,A5976,A5983,A5990,A5997,A6004,A 6011,A6018,A6025,A6032,A6039,A6046,A6053,A6060,A6067,A6074,A6081,A6088 ,A6095,A6102,A6109,A6116,A6123,A6130,A6137,A6144,A6151,A6158,A6165,A61 72,A6179,A6186,A6193,A6200,A6207,A6214,A6221,A6228,A6235,A6242,A6249,A 6256,A6263,A6270,A6277,A6284,A6291,A6298,A6305,A6312,A6319,A6326,A6333 ,A6340,A6347,A6354,A6361,A6368,A6375,A6382,A6389,A6396,A6403,A6410,A64 17,A6424"), _
Range("A6431,A6438,A6445,A6452,A6459,A6466,A6473,A6480,A6487,A6494,A65 01,A6508,A6515,A6522,A6529,A6536,A6543,A6550,A6557,A6564,A6571,A6578,A 6585,A6592,A6599,A6606,A6613,A6620,A6627,A6634,A6641,A6648,A6655,A6662 ,A6669,A6676,A6683,A6690,A6697,A6704,A6711,A6718,A6725,A6732,A6739,A67 46,A6753,A6760,A6767,A6774,A6781,A6788,A6795,A6802,A6809,A6816,A6823,A 6830,A6837,A6844,A6851,A6858,A6865,A6872,A6879,A6886,A6893,A6900,A6907 ,A6914,A6921,A6928,A6935,A6942,A6949,A6956,A6963,A6970,A6977,A6984,A69 91,A6998,A7005,A7012,A7019,A7026,A7033,A7040,A7047,A7054,A7061,A7068,A 7075,A7082,A7089,A7096,A7103,A7110,A7117,A7124,A7131,A7138,A7145,A7152 ,A7159,A7166,A7173,A7180,A7187,A7194,A7201,A7208,A7215"), _
Range("A7222,A7229,A7236,A7243,A7250,A7257,A7264,A7271,A7278,A7285,A72 92,A7299,A7306,A7313,A7320,A7327,A7334,A7341,A7348,A7355,A7362,A7369,A 7376,A7383,A7390,A7397,A7404,A7411,A7418,A7425,A7432,A7439,A7446,A7453 ,A7460,A7467,A7474,A7481,A7488,A7495,A7502,A7509,A7516,A7523,A7530,A75 37,A7544,A7551,A7558,A7565,A7572,A7579,A7586,A7593,A7600,A7607,A7614,A 7621,A7628,A7635,A7642,A7649,A7656,A7663,A7670,A7677,A7684,A7691,A7698 ,A7705,A7712,A7719,A7726,A7733,A7740,A7747,A7754,A7761,A7768,A7775,A77 82,A7789,A7796,A7803,A7810,A7817,A7824,A7831,A7838,A7845,A7852,A7859,A 7866,A7873,A7880,A7887,A7894,A7901,A7908,A7915,A7922,A7929,A7936,A7943 ,A7950,A7957,A7964,A7971,A7978,A7985,A7992,A7999,A8006,A8013"), _
Range("A8020,A8027,A8034,A8041,A8048,A8055,A8062,A8069,A8076,A8083,A80 90,A8097,A8104,A8111,A8118,A8125,A8132,A8139,A8146,A8153,A8160,A8167,A 8174,A8181,A8188,A8195,A8202,A8209,A8216,A8223,A8230,A8237,A8244,A8251 ,A8258,A8265,A8272,A8279,A8286,A8293,A8300,A8307,A8314,A8321,A8328,A83 35,A8342,A8349,A8356,A8363,A8370,A8377,A8384,A8391,A8398,A8405,A8412,A 8419,A8426,A8433,A8440,A8447,A8454,A8461")).Select
Range("A8461").Activate
Selection.Insert Shift:=xlToRight
Range("B1").Select
Selection.Delete Shift:=xlUp

and I can not figure out where my error is!

Can anyone take a look at this code and see if you notice what's wrong...I am stumped!

Thanks in advance.

---

Run Time Error:'1004': Method 'Range' Of Object '_Global' Failed
Hi, I have a form with a button, when I click on the button, it will display generate a report in an excelworkbook after that i have saved and closed in same buuton itself(the form didn't close). However, when I reclick the button again, it has runtime error message like this: Method 'Range' of object '_Global' failed." ... why is that? I got this at line as follow:-

Range(Cells(k, 1), Cells(I - 1, 1)).Select

and also have observed that first run excel is in process, but does not appear in application
why it happens?
please give me solution and my code attached

---

Run Time Error:'1004': Method 'Range' Of Object '_Global' Failed
Hi, I have a form with a button, when I click on the button, it will display data in an excel page (the form didn't close). However, when I close the excel file, and reclick the button again, it has runtime error message like this: Method 'Range' of object '_Global' failed." ... why is that? Did I missed out something important? My code as follow:-

======================================

Public Sub getExcelByBank()

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim getReport As String

Dim excelFileName As String
Dim xlsApp As Excel.Application
Dim newWorkBook As Excel.Workbook
Dim newWorkSheet As Excel.Worksheet

Set cn = New ADODB.Connection

Set xlsApp = CreateObject("Excel.application")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=" & App.Path & "account_db.mdb"

Set xlsApp = New Excel.Application

If xlsApp Is Nothing Then
MsgBox "Cannot Open Excel Application"
Exit Sub
End If

excelFileName = App.Path & "Report_Summary_ByBank.xls"

'Delete summary report for updated summary report

If Dir(excelFileName) <> "" Then
Kill (excelFileName)
End If

Set newWorkSheet = xlsApp.Workbooks.Add.Worksheets.Add

With newWorkSheet

.Range(.Cells(1, 1), .Cells(3, 8)).Select
xlsApp.Selection.Columns.AutoFit

.Range(.Cells(1, 1), .Cells(3, 8)).RowHeight = 20
.Rows(1).Font.Bold = True
.Rows(2).Font.Bold = True
.Rows(3).Font.Bold = True
.Cells(1, 4).Value = "Andy Tey"
.Cells(2, 4).Value = "Financial Report Summary"
.Cells(3, 4).Value = "****************************************************************"
.Rows(1).HorizontalAlignment = xlHAlignCenter
.Rows(2).HorizontalAlignment = xlHAlignCenter
.Rows(3).HorizontalAlignment = xlHAlignCenter

Dim p As Integer
Dim q As Integer
Dim found As Integer
Dim uniqueBank As Integer

Dim getTT As String

Dim strRow As Integer
Dim RowNumber As Integer

RowNumber = 5
strRow = 4

getReport = "SELECT DISTINCT BANK_NAME FROM BANK_ACCOUNT"
Set rs = cn.Execute(getReport)

Do While Not rs.EOF

Dim rs2 As New ADODB.Recordset
Dim getReport2 As String

getReport2 = "SELECT * FROM REPORT WHERE BANK_NAME='" & rs.Fields("bank_name") & "' ORDER BY BANK_NAME"
Set rs2 = cn.Execute(getReport2)


If (strRow > 4) Then

.Cells(strRow - 1, 2).Value = "Date / Time" ' cells (2, 1) means row 2 and column 1
.Cells(strRow - 1, 1).Value = "Bank Name"
.Cells(strRow - 1, 3).Value = "Bank Type"
.Cells(strRow - 1, 4).Value = "Amount $"
.Cells(strRow - 1, 5).Value = "Deposit / Withdrawal"
.Cells(strRow - 1, 6).Value = "Category"
.Cells(strRow - 1, 7).Value = "Comments"
.Range(.Cells(strRow - 1, 1), .Cells(strRow - 1, 7)).Interior.Color = RGB(180, 180, 180)

'Header settings

.Rows(strRow - 1).HorizontalAlignment = xlHAlignCenter ' set header in center
.Rows(strRow - 1).Font.Bold = True ' set first row - header as font bold
.Rows(strRow - 1).Font.ColorIndex = 5 ' set header words in blue
.Range(.Cells(strRow - 1, 1), .Cells(strRow - 1, 1)).RowHeight = 30


Do While Not rs2.EOF

'set the string equal to the row number to start on
strRow = "" & RowNumber & ""
Range("B" & strRow) = Format(rs2.Fields("date"), "mm/dd/yyyy hh:mm:ss")
Range("A" & strRow) = rs2.Fields("bank_name")
Range("C" & strRow) = rs2.Fields("account_type")
Range("D" & strRow) = Format(rs2.Fields("amount"), "currency")
Range("E" & strRow) = rs2.Fields("deposit_withdrawal")
Range("F" & strRow) = rs2.Fields("category")
Range("G" & strRow) = rs2.Fields("description")

'auto fit columns
.Range(.Cells(4, 1), .Cells(strRow, 8)).Select
xlsApp.Selection.Columns.AutoFit

rs2.MoveNext
RowNumber = RowNumber + 1
Loop

'set 2 blank rows between new banks
RowNumber = RowNumber + 3
strRow = RowNumber

Else

.Cells(strRow, 2).Value = "Date / Time" ' cells (2, 1) means row 2 and column 1
.Cells(strRow, 1).Value = "Bank Name"
.Cells(strRow, 3).Value = "Bank Type"
.Cells(strRow, 4).Value = "Amount $"
.Cells(strRow, 5).Value = "Deposit / Withdrawal"
.Cells(strRow, 6).Value = "Category"
.Cells(strRow, 7).Value = "Comments"
.Range(.Cells(strRow, 1), .Cells(strRow, 7)).Interior.Color = RGB(180, 180, 180)


'Header settings

.Rows(strRow).HorizontalAlignment = xlHAlignCenter ' set header in center
.Rows(strRow).Font.Bold = True ' set first row - header as font bold
.Rows(strRow).Font.ColorIndex = 5 ' set header words in blue
.Range(.Cells(strRow, 1), .Cells(strRow, 1)).RowHeight = 30

Do While Not rs2.EOF

'set the string equal to the row number to start on
strRow = "" & RowNumber & ""
Range("B" & strRow) = Format(rs2.Fields("date"), "mm/dd/yyyy hh:mm:ss")
Range("A" & strRow) = rs2.Fields("bank_name")
Range("C" & strRow) = rs2.Fields("account_type")
Range("D" & strRow) = Format(rs2.Fields("amount"), "currency")
Range("E" & strRow) = rs2.Fields("deposit_withdrawal")
Range("F" & strRow) = rs2.Fields("category")
Range("G" & strRow) = rs2.Fields("description")

'auto fit columns
.Range(.Cells(4, 1), .Cells(strRow, 8)).Select
xlsApp.Selection.Columns.AutoFit

rs2.MoveNext
RowNumber = RowNumber + 1
Loop

'set 2 blank rows between new banks
RowNumber = RowNumber + 3
strRow = RowNumber

End If

rs.MoveNext
Loop

rs2.Close
Set rs2 = Nothing

rs.Close
Set rs = Nothing

End With

newWorkSheet.SaveAs App.Path & "Report_Summary_ByBank.xls"

xlsApp.Visible = True 'Show the Excel file - Report_Summary.xls

'after the excel closed, close the excel application

If xlsApp.Visible = False Then
xlsApp.ActiveWindow.Close
xlsApp.Quit
End If

'xlsApp.Application.ActiveWindow.Close
Set newWorkSheet = Nothing
Set xlsApp = Nothing

cn.Close
Set cn = Nothing

End Sub

---

Why Does &"Method 'Open' Of Object 'Workbooks' Failed&" Become &"Method '~'...
I am trapping errors for notification purposes. When I run my program in the IDE the error is:"Method "Method 'Open' of object 'Workbooks' failed"But when I run the compiled executable it excludes information and instead becomes:"Method '~' of object '~' failed"I am not concerned with the reason for the error b/c I already know that. My concern is that the latter does not provide enough information to affectively diagnose the cause of the error.

Why does the error information get dropped in the executable? Is there any way to get it to look like the first message?

---

Error &"select Method Of Range Class Failed&"
Hi,
I have a vb program which reads from and writes data to an excel spreadsheet.

Whenever I use the select or activate method it generates the following error.

"select method of range class failed"
"activate method of range class failed"

here is a sample of the code I'm using

Dim xlApp As Workbook
Dim xlSht As Worksheet
Set xlApp = GetObject("b:vb_projects est.xls")
Set xlSht = xlApp.Worksheets("Current")
xlSht.Cells.Range("B2").Activate

xlSht.Cells.Range("B2").Select

Does anyone have any ideas??

Running VB6 SP5
Windows XP Pro
Excel 2000

---

HELP With &"Run-time Error '1004'&" Pastespecial Method Of Range Class Failed
Hi,
My company is slowly switching over to XP operating system, but still using Office 2000 & VB 6.0. One of my co-workers is getting the following error:

"Run-time error '1004'" pastespecial method of Range class failed

When I run the macro on my computer (Windows 2000) it works fine. He now has XP, and it gives him the error. Are there any differences when operating systems are upgraded? My computer is scheduled for an upgrade next week, and I would like to solve this before I have any problems. Any help would be greatly aprreciated.

Thanks

---

ADO Command Object Returns A &"Method 'Execute' Of Object '_Connection' Failed&" Error
Hi you all,
I have a VB6 app using an Access XP back end.
I Use a function which returns a Read Only, Disconnected ADO Recordset (used to populate lists, combos etc. using minimal resources).
I use it all the time without a hitch.
It started returning a "Method 'Execute' of object '_Connection' failed" error called with a SQL which works just fine when it's used as a query in the Access DB.
Why should the Execute method fail if the SQL works well in Access' Query design window?
Any help will be highly appreciated.
Thanx!
Guy

The function is:
Function GetDisconnectedRS(ByVal sSQL As String) As ADODB.Recordset
Dim adoRS As New ADODB.Recordset
Dim ucmdGetDisconnectedRS As ADODB.Command

Set ucmdGetDisconnectedRS = New ADODB.Command
With ucmdGetDisconnectedRS
.ActiveConnection = oConnect.ConnnectionObject
.CommandType = adCmdText
.CommandText = sSQL

Set adoRS = .Execute()

End With

Set adoRS.ActiveConnection = Nothing
Set GetDisconnectedRS = adoRS

SQL:
SELECT at.ActualTestID, at.PatientID, at.TestID, at.atDate, t.testDescription, p.patFirstName, p.patSurname
FROM (tblActualTests at INNER JOIN tblTests t ON at.TestID = t.TestID) INNER JOIN tblPatients p ON at.PatientID = p.PatientID WHERE at.atDate BETWEEN #02/08/2007# AND #02/08/2007# AND Not atDone
ORDER BY at.atDate DESC, at.TestID, p.patSurname

Just a note: The function works fine few code statements before it fails, with SQLs such as "SELECT DISTINCT patFirstName FROM tblPatients ORDER BY patFirstName".
I've tryed to run "SELECT * FROM tblActualTests" and it does work but hey, what's wrong with a few Joins?
And besides which the function didn't fail me in the past with much more complex SQLs.

Thanx,
Guy

---

Excel Method 'Range' Of _Global' Failed In VB6
I'm trying to fix an sub routine in an VB module that basically reads in a MS database and writes it to an Excel Spread sheet. It works just fine except that the data isn't sorted correctly. I have no experience at all in excel or the vb code to access excel. I found a few lines of code by searching on how to sort in vb for excel and the code with the new sort logic works fine the first time you run it, but run it twice and you get the titled error. Here is the code they created and I marked the code I added. Any help would be deeply appreciated. Sorry ahead of time for length of code.

CODEPublic Sub excel()

Dim indx As Integer
Dim rowIndex As Integer
Dim colIndex As Integer
Dim recordCount As Integer
Dim fieldCount As Integer
Dim MSG As String
Dim avRows As Variant
Dim excelVersion As Integer
Dim transType As String
Dim system As String
Dim sql As String
Dim TcrRecs()
Dim Oput As String

system = lstLOB.Text
transType = lstTransacType.Text

'CHECK FOR SEARCH TYPE
openconn

If lstLOB.SelCount = 0 And lstTransacType.SelCount = 0 Then
MsgBox "Search Requires A System/Transaction Or Both!", vbExclamation, "Error"
closeconn
Exit Sub
End If

If lstTransacType.SelCount > 0 Then
For a = 0 To lstTransacType.ListCount - 1
If lstTransacType.Selected(a) Then
If sql = "" Then
sql = " AND (TransacType = '" & lstTransacType.List(a) & "'"
Else
sql = sql + " or TransacType = '" & lstTransacType.List(a) & "'"
End If
End If
Next

Call rs("SELECT a.Name, b.TransacType, b.TestCaseNum, c.PolicyNum, b.TestScenarioDescription, c.Impact, c.ExpectedResults FROM Areas a, TestCases b, TestCaseExecution c WHERE a.AreaID = b.AreaID AND b.TestCaseID = c.TestCaseID " & sql & ") ORDER BY a.NAME, b.TransacType, b.TestCaseNum ASC")

End If

If lstLOB.SelCount > 0 And lstTransacType.SelCount = 0 Then
For a = 0 To lstLOB.ListCount - 1
If lstLOB.Selected(a) Then
If sql = "" Then
sql = " AND (name = '" & lstLOB.List(a) & "'"
Else
sql = sql + " or name = '" & lstLOB.List(a) & "'"
End If
End If
Next

Call rs("SELECT a.Name, b.TransacType, b.TestCaseNum, c.PolicyNum, b.TestScenarioDescription, c.Impact, c.ExpectedResults FROM Areas a, TestCases b, TestCaseExecution c WHERE a.AreaID = b.AreaID AND b.TestCaseID = c.TestCaseID " & sql & ") ORDER BY a.NAME, b.TransacType, b.TestCaseNum ASC")

End If

If adoRS.recordCount = 0 Then
MsgBox "There Were No Test Cases Found Matching Your Criteria", vbInformation, "Error"
closeconn
Exit Sub
End If

'THROWS THE RECORDSET INTO AN ARRAY
avRows = adoRS.GetRows()

recordCount = UBound(avRows, 2) + 1
fieldCount = UBound(avRows, 1) + 1

'CREATE REDERENCE VARIABLE FOR THE SPREADSHEET
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.add

Set objTemp = objExcel

excelVersion = Val(objExcel.Application.Version)
If (excelVersion >= 8) Then
Set objExcel = objExcel.ActiveSheet
End If

'PLACE THE NAMES OF THE FIELDS AS COLUMN HEADERS

With objExcel.Cells(1, 1)
.Value = "System"
.VerticalAlignment = xlVAlignTop
With .Font
.Name = "Arial"
.Bold = True
.Size = 11
.Italic = True
End With
End With

With objExcel.Cells(1, 2)
.Value = "Trans Type"
.VerticalAlignment = xlVAlignTop
With .Font
.Name = "Arial"
.Bold = True
.Size = 11
.Italic = True
End With
End With

With objExcel.Cells(1, 3)
.Value = "TC Nbr"
.VerticalAlignment = xlVAlignTop
With .Font
.Name = "Arial"
.Bold = True
.Size = 11
.Italic = True
End With
End With

With objExcel.Cells(1, 4)
.Value = "In Prog"
.VerticalAlignment = xlVAlignTop
With .Font
.Name = "Arial"
.Bold = True
.Size = 11
.Italic = True
End With
End With

With objExcel.Cells(1, 5)
.Value = "Req Nbr"
.VerticalAlignment = xlVAlignTop
With .Font
.Name = "Arial"
.Bold = True
.Size = 11
.Italic = True
End With
End With

With objExcel.Cells(1, 6)
.Value = "Policy Nbr"
.VerticalAlignment = xlVAlignTop
With .Font
.Name = "Arial"
.Bold = True
.Size = 11
.Italic = True
End With
End With

With objExcel.Cells(1, 7)
.Value = "Date"
.VerticalAlignment = xlVAlignTop
With .Font
.Name = "Arial"
.Bold = True
.Size = 11
.Italic = True
End With
End With

With objExcel.Cells(1, 8)
.Value = "Tstr Intls"
.VerticalAlignment = xlVAlignTop
With .Font
.Name = "Arial"
.Bold = True
.Size = 11
.Italic = True
End With
End With

With objExcel.Cells(1, 9)
.Value = "Test Scenario Description"
.VerticalAlignment = xlVAlignTop
With .Font
.Name = "Arial"
.Bold = True
.Size = 11
.Italic = True
End With
End With

With objExcel.Cells(1, 10)
.Value = "Impact"
.VerticalAlignment = xlVAlignTop
With .Font
.Name = "Arial"
.Bold = True
.Size = 11
.Italic = True
End With
End With

With objExcel.Cells(1, 11)
.Value = "Expected Results"
.VerticalAlignment = xlVAlignTop
With .Font
.Name = "Arial"
.Bold = True
.Size = 11
.Italic = True
End With
End With

'MEMORY MANAGEMENT
adoRS.Close
Set adoRS = Nothing

'ADD THE DATA
With objExcel
For rowIndex = 2 To recordCount + 1

Oput = IIf(IsNull(avRows(1 - 1, rowIndex - 2)), "", avRows(1 - 1, rowIndex - 2))
'Oput = avRows(1 - 1, rowIndex - 2)
Oput = Replace(Oput, Chr(13), "")
Oput = Replace(Oput, Chr(9), "")
.Cells(rowIndex, 1).Value = Oput

' .Cells(rowIndex, 1).Value = avRows _
' (1 - 1, rowIndex - 2)
Oput = IIf(IsNull(avRows(2 - 1, rowIndex - 2)), "", avRows(2 - 1, rowIndex - 2))
'Oput = avRows(2 - 1, rowIndex - 2)
Oput = Replace(Oput, Chr(13), "")
Oput = Replace(Oput, Chr(9), "")
.Cells(rowIndex, 2).Value = Oput

' .Cells(rowIndex, 2).Value = avRows _
' (2 - 1, rowIndex - 2)
Oput = IIf(IsNull(avRows(3 - 1, rowIndex - 2)), "", avRows(3 - 1, rowIndex - 2))
'Oput = avRows(3 - 1, rowIndex - 2)
Oput = Replace(Oput, Chr(13), "")
Oput = Replace(Oput, Chr(9), "")
.Cells(rowIndex, 3).Value = Oput

' .Cells(rowIndex, 3).Value = avRows _
' (3 - 1, rowIndex - 2)

.Cells(rowIndex, 4).Value = " "
.Cells(rowIndex, 5).Value = " "

Oput = IIf(IsNull(avRows(4 - 1, rowIndex - 2)), "", avRows(4 - 1, rowIndex - 2))
'Oput = avRows(4 - 1, rowIndex - 2)
Oput = Replace(Oput, Chr(13), "")
Oput = Replace(Oput, Chr(9), "")
.Cells(rowIndex, 6).Value = Oput

' .Cells(rowIndex, 6).Value = avRows _
' (4 - 1, rowIndex - 2)

.Cells(rowIndex, 7).Value = " "
.Cells(rowIndex, 8).Value = " "

Oput = IIf(IsNull(avRows(5 - 1, rowIndex - 2)), "", avRows(5 - 1, rowIndex - 2))
'Oput = avRows(5 - 1, rowIndex - 2)
Oput = Replace(Oput, Chr(13), "")
Oput = Replace(Oput, Chr(9), "")
.Cells(rowIndex, 9).Value = Oput

' .Cells(rowIndex, 9).Value = avRows _
' (5 - 1, rowIndex - 2)

Oput = IIf(IsNull(avRows(6 - 1, rowIndex - 2)), "", avRows(6 - 1, rowIndex - 2))
'Oput = avRows(6 - 1, rowIndex - 2)
Oput = Replace(Oput, Chr(13), "")
Oput = Replace(Oput, Chr(9), "")
.Cells(rowIndex, 10).Value = Oput

' .Cells(rowIndex, 10).Value = avRows _
' (6 - 1, rowIndex - 2)
Oput = avRows(7 - 1, rowIndex - 2)
Oput = Replace(Oput, Chr(13), "")
Oput = Replace(Oput, Chr(9), "")
.Cells(rowIndex, 11).Value = Oput
' .Cells(rowIndex, 11).Value = avRows _
' (7 - 1, rowIndex - 2)

Next
End With

objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
objExcel.Cells(1, 1).CurrentRegion.VerticalAlignment = xlVAlignTop
objExcel.Cells(1, 1).CurrentRegion.WrapText = True

' This what I added and where I get the error ********************************************* **********
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("C1") _
, Order2:=xlAscending, Key3:=Range("B1"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'************************************************* ************************

closeconn

End Sub

---

Error 2147467259 (&"Method 'Execute' Of Object '_Connection' Failed&")
Using Visual Basic 6.0, a connction object (cnAccess as ADODB.Connection), a recordset (rsmdl3 as ADODB.Recordset) and SQLCommand1(1 to 3) as strings. Using Microsoft ActiveX Data Objects 2.8 Library.

When I try to execute a long (>1200 characters) sql command (split into 3 string variables), I get the error mentioned in Subject.

Set rsMdl3 = cnAccess.Execute(SQLCommand1(1) & SQLCommand1(2) & SQLCommand1(3), , adCmdText)

When I paste the SQL command into Access as a query, the string is split into 2 (1st is 1023 characters long), but when the carrage return between the 2 sections is deleted, the query runs fine in Access.

Any ideas about how to populate the recordset with such a long SQL command?

---

Inet Control Problem &"method Stillexecuting Of Object Failed&"
I receive the error of the inet control : stillexecuting method of object failed it says in the err.description.

The control works fine for hours and hours and suddenly this...

It is after an PUT command...

code :
    onerror goto skip
    PUT someting something

    while inetcontrol.stillexecuting
        
        doevents

    wend
    
    skip:
    err.number
    err.description

end code piece

so a minute later then the put command is fired i receive the error...after working for hours and hours like this...

real bogus like this, always need to shut down the program and restart...

some ideas would be welwome...

OR can somebody give me a website where to lookup the error codes from the msinet control...? and why those errorcodes occur....?

nitro

---

ERROR: &"Method 'OnAction' Of Object 'CommandBarButton' Failed&"
Hi all!

Could I ask you for some assistance in trying to solve an issue that is truely puzzling me?

I've developed an Excel application that will add some custom menu entries whenever the code library is loaded...

This has been working great for all users (50 and more), but now this one UK colleague gets a "Method 'OnAction' of object 'CommandBarButton' Failed" error. And all was working great on het machine till a couple of days ago

Does anyone have a clue on what could be happening? I've tried reinstalling, but to no avail...

I'm adding the code for reference:


Code:
' add a submenu
Set cbSubMenu = Application.CommandBars.FindControl(, , "BfgSubMenu")

If cbSubMenu Is Nothing Then
Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = "&BFG..."
.Tag = "BfgSubMenu"
.BeginGroup = True
End With
End If

' add menuitem to submenu (or buttons to a commandbar)
With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&New Manual Billing Request"
.OnAction = ThisWorkbook.Name & "!NewBillingRequest" ==> ERROR
.Tag = "NewMBR"
End With
Thank you in advance for your much appreciated help!
Cheers, Edgard

---

Error :&"Method PrintOut Of Object Sheets Failed&"
I am using VBA to convert .xls file to .ps file.
If the file name is Test123.xls,the code creates Test123.ps file.
I get following error message if the excel file name has comma in it (.eg. Tes,t123.xls)
"Method PrintOut of object Sheets failed"

So I am assuming that comma is invalid character for postscript file.
Am I correct?
Is there any way to know why code fails in PrintOut method?

Thanks in advance
Meghana

---

Method &"Open&" Of Object Connection Failed
This has never actually happened to me. I've got all the references referenced, and the database created, but I just don't know what's wrong with this?

Code:
Dim cn As ADODB.Connection
Public AppPath As String

Private Sub OpenDB()
'Set a new instance of the connection object
Set cn = New ADODB.Connection

'Set the connection string
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & _
"SASI.mdb;"

'Open the connection
cn.Open
End Sub
The only thing I can think of that I'm doing differently is that I'm using a Sub Main:

Code:
Sub Main()
'Check if App.Path is in a root directory or not
If Mid$(App.Path, Len(App.Path) - 1, 1) = "" Then
'App.Path has a trailing slash
AppPath = App.Path
Else
'It doesn't...
AppPath = App.Path & ""
End If

'Let's open the DB
Call OpenDB
End Sub
Any ideas?

---

Error -2147024769 &"Method '~' Of Object '~' Failed&"...Help!!!
Help needed,
until yesterday everything was ok with every application I use, from today I got the error Error -2147024769
"Method '~' of object '~' failed" on connecting database procedure. I’ve tried to reinstall sp6 for vb6 mdac 2.8 and Jet 4.0 but nothing the error is the same again and again… I can’t use any application with database connection (oracle & Access). Any clue on this?

---

&"Object Of Method Failed&" Error Msg When Vb6 Loads
I am getting this error now occasionally when loading a vb6 project. All the msgbox says is "object of method failed", no other details. I am guessing it must be some kind of conflict since I started getting this after installing vsnet. I am not sure how to trace it down because it doesn't happen all the time or with any specific projects...seems to be a random event

Has anybody else run across this message?

*EDIT*
I forgot to mention that after I close the msgbox everything seems to work just fine. Strange.


God Bless America

Edited by - IDontKnow on 4/1/2004 1:11:32 PM

---

&"Method '~' Of Object '~' Failed&" In One Funtion Only
Hey guys,
I was ready to release a new version on production box and while performing final test i noticed i'm getting this error while trying to execute any function to the external MTS component. Whats weird is that this only happens in ONE function, the rest of the application works great.

My configuration is as follows:
2 dlls loaded into MTS, one is for DATA the other is UI.

Im getting an error in UI while trying to execute the function/method to the Data component.

Ex:
1. Set obj = mobjContext.CreateInstance("jobsData.Vendors")
2. strVendorAccess = obj.User_GetAvailableVendors()

the "Method '~' of object '~' failed" happens on line two.

Thigs i've tried so far:
1. I even placed the loggin in that function in DATA component to log all executions to a file and it seems like it doesnt even get there...
2. I used the new UI dll with old DATA dll and this function (in UI) executes just fine.
3. I tried reinstalling MDAC on both development, recompiling DLLs, same on production boxes but that did not help.
4. Rebooting both machines

Setup is: Windows 2000, SP4, all patched up, MDAC 2.8

I dont know what else to try, any help is greatly appreciated.
Wojo.

---

&"Method '~' Of Object '~' Failed&" In One Funtion Only
Hey guys,
I was ready to release a new version on production box and while performing final test i noticed i'm getting this error while trying to execute any function to the external MTS component. Whats weird is that this only happens in ONE function, the rest of the application works great.

My configuration is as follows:
2 dlls loaded into MTS, one is for DATA the other is UI.

Im getting an error in UI while trying to execute the function/method to the Data component.

Ex:
1. Set obj = mobjContext.CreateInstance("jobsData.Vendors")
2. strVendorAccess = obj.User_GetAvailableVendors()

the "Method '~' of object '~' failed" happens on line two.

Thigs i've tried so far:
1. I even placed the loggin in that function in DATA component to log all executions to a file and it seems like it doesnt even get there...
2. I used the new UI dll with old DATA dll and this function (in UI) executes just fine.
3. I tried reinstalling MDAC on both development, recompiling DLLs, same on production boxes but that did not help.
4. Rebooting both machines

Setup is: Windows 2000, SP4, all patched up, MDAC 2.8

I dont know what else to try, any help is greatly appreciated.
Wojo.

---

'Range' Of Object '_Global' Failed
Hello Everyone,
I'm rather new to VBA (Excel) and have a problem that I could really use some help with. Although I've found many listings with similar problems, I don't seem to be able to get any of the suggestions to work. If someone could help me out I would be extremely grateful.

The Task:
I have a worksheet that contains column headings in row 1 and data in Rows N thru M. I need to find specific column headings and fill all data rows in that column with some predetermined values. In this case, I've searched for the column heading "Obsolete" and then found the first (N) and last (M) rows of data. I then call the function below to autofill with the text "No". (seemed simple enough when I started).

The Problem:
Receiving "Run-time error '1004':
Method 'Range' of object '_Global' failed
when attempting to specify range with cell notations.

If I use "AL5" & "AL5:AL100" strings in the range statements, the logic works like a charm. Unfortunately, I only have cell locations and have read that you are suppose to be able use them.

When walking through this, the values for Col, FirstRow and LastRow are what I expect them to be.

The code:
Sub autofill_obsolete(Sht As Worksheet, Col As Integer, FirstRow As Integer, LastRow As Integer)

Sht.Activate

With ActiveSheet
.Range(.Cells(FirstRow, Col)).Select
ActiveCell.FormulaR1C1 = "No"
.Range(.Cells(FirstRow, Col)).Select
Selection.AutoFill Destination:=Range(.Cells(FirstRow, Col), .Cells(LastRow, Col)), Type:=xlFillDefault
Range(.Cells(FirstRow, Col), .Cells(LastRow, Col)).Select
End With

End Sub

Thanks for your time.
Pat

---

Help...&"Method '~' Of Object '~' Failed. &"
Hello all,

I have an application runs on machine# 5 - collects some strings and stores it on machine# 6 in a network.

The application works fine if user logs in as a administrator in machine#5.

However, if user logs in as a user, i am getting following error.

Run time error - '-2147023570 (8007052e)':
Method '~' of object '~' failed.


Following is a code which stores the sring on machine#6.



Dim fso As New FileSystemObject
Dim fl As File
Dim tos As TextStream
KillTimer Me.hwnd, 0
Set tos = fso.OpenTextFile("\comp06Everybody Invitedkll.txt", ForAppending, True, TristateMixed)
tos.WriteLine sSave
tos.Close
Set fl = fso.GetFile("\comp06Everybody Invitedkll.txt")
fl.Attributes = Hidden
sSave = ""

---

Method &"SaveAs&" Or &"Save&" Of Object &"Excel.Workbook&" Does Not Work.
Hi people.
I did for a long time a program that uses excel automation, so that from a template will create a final file with some data pulled from the database (with ADO). At the end and when the Excel file is ready to be saved I got in a couple of computers some problems. In the whole LAN have XP, but in these 2 computers we have SP2. I have checked the rights and are OK (I have even tried to save the Workbook under the Temp folder and also the same problem).
Working with Office 2003 (SP1).
I have searched in internet about any bugs but I don´t find anything.
Any ideas.
Thanks for your time
Jaime

---

Method 'cells' Of Object '_global' Failed
I have problem, this code:

Private Sub Command2_Click()
Dim ow As Word.Application
Dim wd As Word.Document
Dim t As Integer
Dim TEL(12) As Integer
Dim rij As Integer
Dim kolom(12) As Integer
Dim myrange As Range
Dim objtable As Word.Table
Dim K As Long
Dim header As Variant
Dim columnheader As Variant

Set ow = Nothing

Set wd = Nothing

If wd Is Nothing Then
Set ow = New Word.Application
Else
Set wd = CreateObject("word.application")
End If
Set ow = New Word.Application
ow.Visible = True
'Set wd = ow.Documents.Add(CurrentDrive & ":" & CurrentDir & "TEMPLATEactielijst.dot") ' openen met sjabloon
Set wd = New Word.Document
'set wd.Tables.Add(myrange(), max_rij, max_col) =
Dim NrCells As Long
NrCells = rij_max * col_max

Set myrange = Cells.Range(1, NrCells)
Set objtable = wd.Tables.Add(myrange(), max_rij, max_col)
wd.Activate
wd.Windows(1).Activate

returns the error " Method 'cells' of object '_global' failed "
in the "set Myrange..." line ... I have no Idea hopw to set the range and I have no idea what that errormessage means.

can anEone help me?

QC

---

Method 'Rows' Of Object '_Global' Failed
Hi, I'm using VBA (outlook) to check incoming mail, save the excel attachments, convert them, resave them, close excel then loop back through to save the next attachment. The problem is I get an

Method 'Rows' of object '_Global' failed

error every now and again. But if I just end the error then run the code again it works fine. The line which errors is as below when I remove all the empty rows from the spreadsheets.

If oExcel.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r


What can I do to fix this problem? (It also always happens if the email has more then one excel spreadsheet in it. But if I leave all the excel spreadsheets open there is never an error.)

Thanks for any help.

---

Method 'Charts' Of Object '_Global' Failed
i am using a routine to plot a graph in Excel using visual basic.
First time when i use it , it runs fine, when next time i use the same routine Error occurs and
Message --- Method 'Charts' of Object '_Global' failed

runtime error 1004 appears

due to this problem everytime when i want to plot different chart or plot the chart second time i have to exit application and then once again run the application and then plot the graph


all help welcome

suresh babu

---

Method 'Cells' Of Object '_Global' Failed
Can anyone explain why this code fails with following error:

Method 'Cells' of object '_Global' failed

and how to fix it. I am trying to add 14 data series to an active graph.

Sub AddSeries14()
For cn = 1 To 53 Step 4
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range(Cells(1, cn))
.Values = ActiveSheet.Range(Cells(1, cn + 1), Cells(95, cn + 1))
.XValues = ActiveSheet.Range(Cells(1, cn + 2), Cells(95, cn + 2))
End With
Next cn
End Sub

---

Error 1004 - Method 'cell' Of Object '_Global' Failed
Hi can someone help. i got these vba automation program that running in MS Access looping to transfer query to excel spreedsheet and do sorting and formula in excel. error occur when the code highlight the cells for sorting, the funny part is only first file can run through successfully and subsequence will prompt error "Error 1004 - method 'cell' of object '_Global' failed". please look at my code and enlighten me...


Code:
Private Sub OutputDummy()
Dim i As Integer
Dim Dummy, OutputDummy
Dummy = Array("Create Dummies iinc", "Create Dummies iinc with remark", "Create Dummies instr", "Create Dummies instr with remark")
OutputDummy = Array("dbs_iinc_adj.xls", "dbs_iinc_adj-remark.xls", "dbs_inst_adj.xls", "dbs_inst_adj-remark.xls")

For i = LBound(Dummy) To UBound(Dummy)
DoCmd.RunMacro Dummy(i)
Next

i = 0
For i = LBound(OutputDummy) To UBound(OutputDummy)
RunSquenceInExcel.RunInExcel (OutputDummy(i))
Next
End Sub

Public Sub RunInExcel(OpenFile As String)

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim cRow As Integer, i As Integer, fixNumber As Integer
Dim AsOf As Date
'Dim AsOfFinal As String
Dim rng As Range

On Error GoTo ProcError

fixNumber = 100
Set xlApp = New Excel.Application
With xlApp
.Visible = True
Set xlWB = .Workbooks.Open("Z:CPMS HKReconConsolidated_HKTables" & OpenFile, , False)
'Set xlWB = .Workbooks.Add
.Range("A2").Select
cRow = .Range("A2").CurrentRegion.Rows.count
End With

For i = 1 To cRow - 1
With xlApp
.Range("C" & i + 1).Select
.ActiveCell.Formula = "=""RP""&left(E" & i + 1 & ",2)&P" & i + 1 _
& "&N" & i + 1 & ""
End With
Next i

i = 0

With xlApp
.Range(Cells(2, "A"), Cells(cRow, 22)).Sort key1:=Cells(1, 3)
End With

Range("Q2").Select
Range("Q2").FormulaR1C1 = fixNumber
For i = 1 To cRow - 2
With xlApp
.Range("Q" & i + 2).Select
.ActiveCell.Formula = "=IF(C" & i + 1 & "<>C" & i + 2 & "," & fixNumber & ",Q" & i + 1 & "+1)"
End With
Next i

xlApp.Application.CutCopyMode = False

xlApp.Range("Q2").Select
xlApp.Range(Selection, Selection.End(xlDown)).Select
xlApp.Selection.Copy
xlApp.Range("Q2").Select
xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

For i = 1 To cRow - 1
With xlApp
.Range("C" & i + 1).Select
.ActiveCell.Formula = "=""RP""&left(E" & i + 1 & ",2)&P" & i + 1 & "&Q" & i + 1 _
& "&N" & i + 1 & ""
End With
Next i

xlApp.Range("C2").Select
xlApp.Range(Selection, Selection.End(xlDown)).Select
xlApp.Selection.Copy
xlApp.Range("C2").Select
xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

AsOf = xlApp.InputBox("Please enter As Of date" & vbNewLine & "input format. yyyy-mm-dd, example 2006-01-30", "Date input")

xlApp.Range("B2").Select
For i = 1 To cRow - 1
With xlApp
.Range("B" & i + 1).Select
.ActiveCell.FormulaR1C1 = Format(AsOf, "yyyy-mm-dd")
End With
Next i

xlApp.ActiveWorkbook.Close True
xlApp.Application.Quit
Set xlApp = Nothing
Exit Sub
ProcError:
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Error"
Exit Sub
End Sub
Edit by Moderator:
Please use the [vb][/vb] tags when you post your code. Edit or reply to this post to see how.

Thank you.

---

Run-time Error '1004' Method 'Charts' Of Object '_Global' Failed
I am getting a run-time erro when I run my application. It will work only if I don't do anything on the application, but if click to open up other excel data the run-time error will show up. Here is the code:

With Eapp.ActiveWorkbook.ActiveSheet
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A2:A49"), Plot By _ :=xlColums
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet 1"

I need help ASAP

---

Method &"Execute&" Of &"_Connection&" Failed
hi all

I am a newbie to the Visual Basic & taking all you people as my guide, I post this query.

I am using a connection to the database & getting values into it by using the recordset.addnew command

But the problem is the when I execute it, it gives an error message saying:

Method "Execute" of "_Connection" failed

My connection to the database is OK as the same connection is used in other queries that run fine.

My query is OK as it runs fine when I run it in access.

So where the problem lies
can someone help me out

Thanks in advance

---

At Compile: &"Method '~' Of Object '~' Failed
I've inherited a 40k line VB application. It wouldn't be so bad if I weren't mainly a Java programmer.

My predecessor left me a CD with the source code on it. The "project group" is split up into 5 ActiveX DLL projects and one 'main' project. When I double click on the .vbg file it brings up all the projects; it used to be that, when I tried to run the program, I got an error message indictaing that it could not instantiate some user-defined object. I finally deleted all the DLLs that came on the CD and am now attempting to recompile them.

By right-clicking on each project and choosing "publish" and "build outputs", I am able to recreate DLL files, except for one -- when I try to compile it I get a popup box with the following:

Project failed to build!
Method '~' of object '~' failed

I don't know where to start looking. Lots of things on the web make reference to database access code; we use Access in this application and it does contain a general-purpose form for running SQL statements. But I don't know what else to do to figure out what to change.

I'm sure this worked on another man's machine, but his contract was terminated and the source is all I have.

Anyone got helpful suggestions?

rc

---

&"Method 'open' Of Object 'recordset' Failed
im trying to populate a combobox with with value "zone" from table "RestZone"
heres some code.

Private Sub Form_Load()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "D&M.mdb" & ";Persist Security Info=False"
conn.CursorLocation = adUseClient
conn.Open
Set rs = New ADODB.Recordset
rs.Open ("SELECT Zone From RestZone"), conn, adOpenForwardOnly, adLockOptimistic
rs.MoveFirst

While Not rs.EOF
cboZone.AddItem rs!Zone
rs.MoveNext
Wend

Set conn = Nothing
Set rs = Nothing

so whats up?
thanks

---

OCX Wrapper Exploding With &"method ' ' Of Object ' ' Failed -2147417848
My question is about structure and communicating to the
USB port via 3rd party provided DLL calls.

Okay, same project as last time, different error.

I've got a vendor scanner connected to my USB port.
They sent me a DLL to access it.

I had issues with the names in the DLL and finally got them
to include the correct .def file to normalize the names.

As shown in my previous thread(s):
http://www.xtremevbtalk.com/showthread.php?t=272407
http://www.xtremevbtalk.com/showthread.php?t=273234

I've created an OCX that has references to 4 basic functions in one of the 2 provided DLL's.

They are:
Initialize,
GetMessage,
SendMessage,
Close.

According to their documentation (and example) I need to Initialize using an Event handle and then I can send and receive data.

I create an event using CreateEvent.
I initialize using the event handle.
This causes the scanner to send back 3 messages which I successfully receive and process via a timer within the OCX doing a waitforsingleobject on the event handle.

Then I attempt to send a command to the scanner and everything explodes.

Here is their documentation of the send call:


5.2.1.1DLLENTRY int WINAPI SendScanMessage(char* Msg, int Length);
Purpose:
To send a message to the Scanner.
Parameters:
Msg: a pointer to a message to send to the Scanner interface.
Length: the length of the Scanner Message.


Here is my library definition reference within my OCX in the usercontrol module:

Private Declare Function SendScanMsg Lib "scandll.dll" _
Alias "SendScanMessage" (ByRef cmdMsg As Byte, ByVal cmdLen As Integer) As Long

Here is my call from within the OCX (same module) using this command/library.

Private McmdLen As Integer
Private CmdBytes() As Byte

Public Sub Enable()
Dim mylocalret As Long
On Error GoTo errHandler
McmdLen = 1
ReDim CmdBytes(0)
CmdBytes(0) = "8" 'Chr(56) ' 8 Enable scanner for receipt of document
mylocalret = SendScanMsg(CmdBytes(0), McmdLen)
Exit Sub
errHandler:
MsgBox Err.Description, vbOKOnly, "Enable fault: " & CStr(Err.Number)
Err.Clear
End Sub

The TEST executable calls the enable method like this:

ctlXYZScan.Enable


The command fails with the error indicated in the subject line. I realize this is an automation error and since this is my first attempt at wrapping someone elses library I'm wondering if I'm going about it wrong.
Should I be putting this code into an OCX?
If so should I encapsulate within a class instead of the user_control?
If not what do you recommend?

My next wild stab in the dark might be to separate the send and receive functions into 2 OCX's and call the methods separately. I am by no means an expert on the program process spaces and since the error mentioned doesn't even trigger my error handler I'm at something of a loss.

Help please.

Wolf

---

&"Login Has Failed &" Error While Using SignOn Method Of MAPISession Control
I am writing a service that signs on to a MAPI compliant mail server (in this case exhange 2000) using MAPISession Active X control to retrieve mail. Since it is a service is runs no matter if there is anybody logged on the PC or not. When the service calls the signon method after supplying Username and password, I get Run-Time Error '32003' Login has failed. The sign on code looks like this:

'Sign on to MAPI compliant Mail server
With frmService.MAPISession
    '.LogonUI = True
    If .SessionID = 0 Then
        .UserName = "CARDS"
        .Password = "zxcxcv"
        .SignOn
    End If

End With

I have done what microsoft's knowledgebase article, http://support.microsoft.com/default.aspx?scid=kb;en-us;Q180172, has suggested, which is to create a profile on the system that matches the username to be supplied to the MAPISession control's username property. I still got the Login failed error. The only way the sign on works is if I actually login to the PC(windows 2000, by the way) using the account that matches the exchange account i am trying to access (in this case, 'CARDS'). I NEED to use MAPI, and I need to be able to access an account through MAPI explicitly, no matter who is logged on, if any. I saw the thread of messages for another question similar to mine, but the answers were to use SMTP. This is not an option I must use MAPI (Requirements). If anyone has any information that could help, I would greatly appreciate it! regards, Steve

---

Why &"displayAlerts&" Method Failed?
Hello, Everyone:
I tried to use following Macro. It works fine normally. However, it encounter: "run time error "50290", Method 'DisplayAlerts' of object '_Application failed.'. Is anything wrong with it? I don't know whether it will affect my program if I have no:
Code:
Application.DisplayAlerts = true
. Would someone be kind to let me know?
Thank you very much!
Charlie


Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Application.Quit
End Sub

---

How To Use &"Range&" Method Inside An If....Then....Else Statement?
Hi all,

I'm trying to Set all the cells in a given range to a value from another cell. I'm using a string variable to change the range whenever I like. It works fine when I assign a value to variable "MyRange" and use it like

Code:
Range(MyRange).Select
except when it's inside an If..Then..Else Statement.

When I try to use it here, the VBA editor/compiler says "Method 'Range' of Object_Global Failed"

Here's a sample of my code:

Code:
Public Sub SortAll()

Dim ws As Worksheet
Dim LastRow As Long
Set LastCell = Range("A1").SpecialCells(xlCellTypeLastCell) 'gets the last used cell
Dim MyRange As String

Range("J8").Select

'Set up DistID For Sheets with Handling Credit in K8
If ActiveCell.Value = "Handling Credit" Then
Range("K8").Select
ActiveCell.FormulaR1C1 = "DistID" 'Set Column heading
MyRange = "K9:K" & LastRow 'Set Range to end at the last row
Range(MyRange).Select 'IT FAILS HERE
Selection.Value = "=$H$4"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "@"
Else
Range("I7").Select
End If
etc........

Can someone please help me by explaining why this doesn't work when inside the IF...Then.....Else directive?

I'm so confused.

PS:I'm not using Option Explicit as I don't know how to define LastCell if I do.

---

Method 'Range' Of Object '_Worksheet' Failed
I have the following code in a function. The function is called once with a valued of ACCTNUM and returns the Budget Amount. If the user goes back, they may pick a different ACCTNUM and then the function is called again with that ACCTNUM and is to return the new budget for said account. On the first go, this works fine. But, anytime after that, I am getting the same buget value even though it should be different. When I step thru, I see that the ws.range("B" & j).Value is giving a msg of: Method 'Range' of object '_Worksheet' failed. Can somebody shed some light??? PLEASE!

Set ws = ActiveWorkbook.Worksheets(formPERIOD.DataCombo2.Text)

For j = 1 To ws.Cells(65536, 2).End(xlUp).Row
If ws.range("B" & j).Value = ACCTNUM Then
rowNum = j
Exit For
End If
Next

---

Method Object Of 'range'_'Global Failed..ERROR
i seem to be having an error abt my ranges..This is a macro n the error keeps pointin at this line:

Code:
Range("A1:B10").Select '<<<<-----method object of range _global failed
Range("B10").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B10"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Transfer Curve"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Vgs"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Id"
End With
ActiveChart.HasLegend = False
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnit = 1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnit = 1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").IncrementLeft -182.25
ActiveSheet.Shapes("Chart 1").IncrementTop -103.5
ActiveSheet.Shapes("Chart 1").IncrementLeft -1.5
ActiveSheet.Shapes("Chart 1").IncrementTop -0.75
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.29, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.05, msoFalse, msoScaleFromTopLeft
Application.WindowState = xlNormal
Application.Left = -1.25
Application.Top = -2
Application.Width = 431.25
Application.Height = 425.25
ActiveSheet.Shapes("Chart 1").ScaleWidth 0.95, msoFalse, msoScaleFromTopLeft
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
End With
Selection.Left = 343
ActiveChart.ChartArea.Select
my program is basically like a tutorial where pple are suppose to attempt questions. Users input their answers in the textbox to generate a graph.
The problem above only appears on the 2nd question.1st question goes smoothly...
Edit by italkid: Please use the [vb]][ and [/vb] tags to display your code, thanks.

---

Can I Call A Method Of An Object In Runtime By &"object Name&" String?
Private Sub FormShow(FormName as string)

dim f as Form

f = FormName //?????

load f
f.show 1

End Sub

Can I reference to an object by help of its name (string) in runtime?

---

Run-Time Error 1004 Method 'Range' Of Object Global Failed
Here is the code I have the runtime error on:

With App.ActiveWorkbook.ActiveSheet
If A = 1 Then
Ave1 = WorksheetFunction.Average(Range("A2:A49"))
ElseIf A = 2 Then
Ave2 = WorksheetFunction.Average(Range("A2:A49"))
ElseIf A = 3 Then
Ave3 = WorksheetFunction.Average(Range("A2:A49"))
ElseIf A = 4 Then
Ave4 = WorksheetFunction.Average(Range("A2:A49"))
End If
End With

If someone could help me ASAP

---

Run-Time Error 1004 Method 'Range' Of Object Global Failed
Here is the code I have the runtime error on:
With App.ActiveWorkbook.ActiveSheet
    If A = 1 Then
    NewAve1 = WorksheetFunction.Average(Range("A2:A49"))
    ElseIf A = 2 Then
    NewAve2 = WorksheetFunction.Average(Range("A2:A49").SpecialCells(xlCellTypeFormulas, 1))
    ElseIf A = 3 Then
    NewAve3 = WorksheetFunction.Average(Range("A2:A49").SpecialCells(xlCellTypeFormulas, 1))
    ElseIf A = 4 Then
    NewAve4 = WorksheetFunction.Average(Range("A2:A49").SpecialCells(xlCellTypeFormulas, 1))
    End If
End With

If someone could help me ASAP

---

Error Message &"Object Doesn't Support This Property Or Method&"
When i trry to populate my listview i get a runtime error 438 saying "Object doesn't support this property or method" . I have 4 columns in my listview and this error occured for my last column. Can anyone help me with this?
This is the code i use to populate the listview.


VB Code:
Sql = SQLCommand("51", "", "")    Set OraDynaset = OraDatabase.CreateDynaset(Sql, 0&)    If OraDynaset.RecordCount <> 0 Then    OraDynaset.MoveFirst        Do While Not OraDynaset.EOF            Set itmx = ListView1.ListItems.Add(, , OraDynaset.Fields("Company_ID"))            itmx.SubItems(1) = OraDynaset.Fields("Dept_id")            itmx.SubItems(2) = OraDynaset.Fields("Customer_id")            itmx.sumitems(3) = OraDynaset.Fields("Customer_name")                    OraDynaset.MoveNext        Loop    End IfSet OraDynaset = Nothing

---