-1

can anyone help me? i have this syntax

Dim oExcel As Variant
Dim oWB As Variant

'Set oWB = CreateObject("Object.Workbook")
Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks.Open("Z:\MPR\Maret 2017\31 - 03 -2017\DF\PAL - DF.xlsx")
oWB.Sheets(1).select
Dim oNumRow As Integer

'oNumRow = oWB.Sheets(1).UsedRange.Count
With oWB
    oNumRow = oWB.Sheets(1).Cells(oWB.Sheets(1).Rows.Count, "A").End(xlUp).row 'error
End With
oNumRow = oWB.Sheets(1).Cells(oWB.Sheets(1).Rows.Count, 1).End.row 'error
oNumRow = oWB.Sheets(1).Range("A1", oWB.Sheets(1).Range("A1").End(xlDown).row).Rows.Count'error

if i change the Dim oExcel As Variant to Dim oExcel As Excel.Application, the program error in there. Can anyone suggest any idea? I'm new in programing vba for macro

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Fenny
  • 41
  • 9
  • Thats not VB.NET code. VB.NET is to vba as *carpet* is to *car* – Ňɏssa Pøngjǣrdenlarp Jan 17 '18 at 03:42
  • yeah, i know that not vb.net :) but maybe anyone in vb .net know that code too – Fenny Jan 17 '18 at 03:45
  • Thats not how it works. Maybe someone using python or c++ could answer too. The questions pertains solely to VBA/Excel-vba, so remove the tag please – Ňɏssa Pøngjǣrdenlarp Jan 17 '18 at 03:48
  • 1
    Where are you running the code? Is it in Excel's VBA? Or somewhere else? If, for instance, it was in Word VBA, then `xlUp` (a constant in the Excel Object library) won't be defined and will be treated as `0` instead of as `-4162`. – YowE3K Jan 17 '18 at 03:54
  • i run it in ACCPAC macro. this is how its work. First we read the excel file, and insert all the data to ACCPAC system using macro – Fenny Jan 17 '18 at 04:02
  • 1
    Then the comment by YowE3K is completely relevant. You either need the reference to the Excel library, or you need to use the corresponding numerical value instead of xlUp. xlUp is internal to Excel. – Cindy Meister Jan 17 '18 at 14:14

1 Answers1

0

If you are not running your code in Excel VBA, or otherwise have a reference to the Microsoft Excel Object Library, then none of the constants defined in that library will be available to your code. You will have to set their values yourself.

You could add the following lines to the start of your code:

Const xlUp As Long = -4162
Const xlDown As Long = -4121

They were the only two constants I could see in your posted code but, if you have others, they should be added to your code as well.

YowE3K
  • 23,852
  • 7
  • 26
  • 40