[RESOLVED] Excel VB6.0 - AVOIDING USING .ACTIVATE PROBLEM

[gphillips]

Hi,

I am trying to avoid using the Rang("a3").activate method to activatre a reference cell. I have started uisng object variables.

Have some code here which says when run object required.

Can someone help debug the code ammendments and where I am going wrong.

Just copying and pasting between cells and using some text string statements. The routine is getting
to the left statements below and becoming unstuck - please see the find the comment where error occurred:-


' From here program getting confused


In module

visual basic code:--------------------------------------------------------------------------------Public FileName As String
[Highlight=VB]Public FileName2 As String
Public length As Integer
Public Length2 As Integer
'Public xlApp As Object
Public xlApp As Excel.Application
Public wkbobj As Excel.Workbook
Public xlstart As Excel.Worksheet
Public xloutput As Excel.Worksheet

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



In form


visual basic code:--------------------------------------------------------------------------------

Dim FileLength As Long
Dim i As Long
Dim arraydata() As String
Dim x As Integer
Dim columns As Integer
Dim j As Integer
Dim FirstGap As Integer
Dim desc As String
Dim pos As Integer
Dim mywbook As String
Dim thiscell As String
Dim thiscell2 As String
 
'Striping out the path to get the filename
 
frmEBS.txtFileName.Text = FileName
pos = InStrRev(FileName, "\")
mywbook = Mid(FileName, pos + 1, Len(FileName) - pos + 1)
 
 
Set xlApp = Excel.Application
Set wkbobj = xlApp.Workbooks(mywbook)
Set xlstart = wkbobj.Sheets("Starting point")
 
 
xlstart.Range("A2").Copy
'Worksheets("Output1").Activate - taken out
xloutput.Range("C2").PasteSpecial
xloutput.Range("e2").PasteSpecial
 
 
' From here program getting confused
 
thiscell2 = xloutput.Cells(2, 5)
If Left(thiscell2, 2).Text = "ND" Or _
   Left(thiscell2, 2).Text = "SD" Or _
   Left(thiscell2, 2).Text = "EBS" Then
   'Don't do anything
   
Else
xloutput.Cells(2, 5) = "EBS-" & thiscell2
End If
xloutput.Cells(2, 5).Copy ' copy one one description to the next description
xloutput.Range("i2").PasteSpecial

--------------------------------------------------------------------------------[/Highlight]


Cheers,
B

[westconn1]

what is the problem it looks ok?

[gphillips]

Please take into account all the variable posted in previous post. I have also set xloutput as well. It working eal

Set xloutput = wkbobj.Sheets("Output1")

It stops at the left and provides an error

Run time error '424'

Object required.

xloutput.Range("C2").Value = xlstart.Range("A2").Value
xloutput.Range("e2").Value = xlstart.Range("A2").Value
 
 
thiscell2 = xloutput.Range("e2").Value
 
'stops here with error
If Left(thiscell2, 2).Text = "ND" Or _
   Left(thiscell2, 2).Text = "SD" Or _
   Left(thiscell2, 3).Text = "EBS" Then
   'Don't do anything
   
Else
xloutput.Cells(2, 5).Text = "EBS-" & thiscell2
End If
xloutput.Range("i2").Value = xloutput.Range("e2").Value

Any ideas? I can't see anything wrong with code

Many thnaks

[westconn1]

is output1 an existing sheet in the same workbook??

[gphillips]

yes it was created via code eralier on.

[B]sheetcount = Worksheets.Count
 
'Add worksheets after the last sheet
Worksheets.Add After:=Sheets(sheetcount)
 
'Count Worksheets
sheetcount = Worksheets.Count
 
'Name the sheet and add the number of sheets
Worksheets(sheetcount).Name = "Output1"
 
 
Set xloutput = wkbobj.Sheets("Output1")
 
xloutput.Range("A1").Formula = "Level"
xloutput.Range("B1").Formula = "Type"
 
 
xloutput.Range("C2").Value = xlstart.Range("A2").Value
xloutput.Range("e2").Value = xlstart.Range("A2").Value[/B]

don't think this is the problem as the copy and paste code above is working

as well as put heading titles.
It stops at the left code with object required. Run time error 424.

Many thnaks.

[westconn1]

Left(thiscell2, 2).Text = "SD" Or _

i think this is your problem thiscell2 is your variable, it has no .text property

Left(thiscell2, 2) = "SD" Or _

in all instances

[gphillips]

Don't know why you couldn't use .text - the logic? - but it works!