Using xlCellTypeLastCell in VB6

Hi, new to the board as you will be able to tell! :wave:

Anyway, I am trying to create an exe file using VB6, I only need the exe file to do a few things, them being...

Open Excel, take some data, play with a little etc..

I got it all to work using VBA, but I am stuggling adapting it to VB, especially xlCellTypeLastCell!!

Here's my code as it stands,

Private Sub Evident_Click()

Dim objExcel As Object
Dim objExcel_data As Object
Dim wkbk1 As Object
Dim WkSht As Object
Dim wshSrc As Object

Dim NWShareDrive As String
Dim vExcel As String
Dim FileName As String
Dim vLastCell As String
Dim vFirstCell As String

Dim PATemplate As String
Dim intX As Integer 'used to reference the current cell on the source sheet
Dim intY As Integer 'used to reference the current cell on the output sheet
Dim intZ As Integer 'used to reference the current column on both sheets
Dim strProvider As String 'this is contains the last used provider


NWShareDrive = "Path"
PATemplate = "FILE.xls"
FileName = "FILE2.xls"

Set objExcel = CreateObject("Excel.Application")

Set wkbk1 = Nothing
Set wkbk1 = objExcel.application.Workbooks.Open(NWShareDrive & PATemplate)
Set WkSht = wkbk1.ActiveSheet

objExcel.Visible = True
objExcel.DisplayAlerts = False
objExcel.application.Interactive = True

vFirstCell = "A1"

'Copy Data

objExcel.Sheets("Data").Select
objExcel.ActiveCell.SpecialCells(xlCellTypeLastCell).Select
vLastCell = objExcel.ActiveCell.Address
objExcel.Range(vFirstCell & ":" & vLastCell).Select
With objExcel.Selection
.Copy
objExcel.Workbooks.Add
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With

Set wkbk1 = Nothing
Set WkSht = Nothing

'------------------------------------------------------
'This will create a new sheet for each Content Provder
'------------------------------------------------------

intX = 2 'set the position to row 2 in the source sheet as this is where data starts

Set wshSrc = objExcel.Sheets("Sheet1") 'set reference to the source sheet

Do Until wshSrc.Cells(intX, 1).Value = "" 'loop through the source sheet until we hit an empty row

'checking the source provider is the same, if not we create a new sheet
If Not wshSrc.Cells(intX, 1).Value = strProvider Then
'sets the new provider to the variable
strProvider = wshSrc.Cells(intX, 1).Value
'adds new sheet
Set WkSht = ThisWorkbook.Sheets.Add
'names the sheet
WkSht.Name = strProvider
'copies the headings
For intY = 1 To 8
WkSht.Cells(1, intY).Value = wshSrc.Cells(1, intY).Value
WkSht.Cells(1, intY).Font.Bold = True
Next
'set the current row on the output sheet to 2, so data is entered after the heading
intY = 2
End If

'loops through the data on the current row from the source sheet
'and enters it to the current row on the output sheet
For intZ = 1 To 8
WkSht.Cells(intY, intZ).Value = wshSrc.Cells(intX, intZ).Value
Next

WkSht.Columns.AutoFit

'add one to our row pointers so we move on a line
intY = intY + 1
intX = intX + 1

Loop
End Sub

Any help would be greatly appreciated!!!!

Thanks,

Andy

Welcome to the Forums.

I noticed that your using late binding but you refer to the excel constant. If your late binding then you need to declare the const.

VB Code:

Const xlCellTypeLastCell = 11

What errors are you receiving?

It says;

Unable to get the SpecialCells property of the Range Class

Thanks for your help.

Andy

Fixed that bit, I didnt have excel reference selected in VB6!!! D'oh!! But I am stuck here now....

vLastCell = objExcel.ActiveCell.Address
objExcel.Range(vFirstCell & ":" & vLastCell).Select
With objExcel.Selection

Whats the error? It could be because your not passing any parameters so its using all the defaults for the .Address.
Your range arguments may not be in the correct format because of that.

Whats the values for vLastCell and vFirstCell?