How do i call a procedure

Topic Specific Tutorials and Tips.

Moderator: Rathinagiri

ROBROS
Posts: 56
Joined: Thu May 25, 2017 6:30 pm
DBs Used: DBF
Has thanked: 31 times
Been thanked: 1 time

Re: How do i call a procedure

Post by ROBROS » Tue Sep 05, 2017 6:34 am

I googled and found:
Range("B4").End(xlUp).Select

This example selects the cell at the top of column B in the region that contains cell B4.

Robert

And I found this:

Describes how to use VBA macros or procedures to select cells, ranges, and named ranges in Excel. ... Count to count the number of columns in the specified item. Rows. .... Column lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row

User avatar
mol
Posts: 2801
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 112 times
Been thanked: 52 times
Contact:

Post by mol » Tue Sep 05, 2017 6:48 am

KDJ wrote:
Tue Sep 05, 2017 4:54 am
ROBROS wrote:
Mon Sep 04, 2017 6:53 pm
...
oExcel:Selection:End(xlUp):Select()
...
What is the value of xlUp ?
xlUp is defined in excel.ch as:

Code: Select all

#DEFINE xlUp   -4162

ROBROS
Posts: 56
Joined: Thu May 25, 2017 6:30 pm
DBs Used: DBF
Has thanked: 31 times
Been thanked: 1 time

Post by ROBROS » Tue Sep 05, 2017 8:30 am

Now the problem is solved:

I simply added Mol's #define
and left out the line oExcel:Cells:Item(65536, nColumn).Select()

Now it works.

Thank you for your answers.
Robert

ROBROS
Posts: 56
Joined: Thu May 25, 2017 6:30 pm
DBs Used: DBF
Has thanked: 31 times
Been thanked: 1 time

Post by ROBROS » Tue Sep 05, 2017 8:43 am

Sorry, my answer was given too quick, not all rows are appended,
but when I insert:
oExcel:Cells:Item(65536, nColumn).Select()
I get a syntax error.

I will search the net for a solution (VBA), if I find it I will post it here.
Thx again
Robert

User avatar
mol
Posts: 2801
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 112 times
Been thanked: 52 times
Contact:

Post by mol » Tue Sep 05, 2017 8:47 am

change:
oExcel:Cells:Item(65536, nColumn).[/b]Select()

to
oExcel:Cells:Item(65536, nColumn):Select()

ROBROS
Posts: 56
Joined: Thu May 25, 2017 6:30 pm
DBs Used: DBF
Has thanked: 31 times
Been thanked: 1 time

Post by ROBROS » Tue Sep 05, 2017 9:48 am

Hi Marek,
the syntax error is gone, but I still get a runtime error (tole)
As a workaround i did the following:
manually open worksheet, find the last row with ctrl end, type this in a cell above the range to be imported and read it into nLastRow.

But I will search for an automatic solution.

User avatar
mol
Posts: 2801
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 112 times
Been thanked: 52 times
Contact:

Post by mol » Tue Sep 05, 2017 10:00 am

Why do you want to go to the last row?

ROBROS
Posts: 56
Joined: Thu May 25, 2017 6:30 pm
DBs Used: DBF
Has thanked: 31 times
Been thanked: 1 time

Post by ROBROS » Tue Sep 05, 2017 11:46 am

mol wrote:
Tue Sep 05, 2017 10:00 am
Why do you want to go to the last row?
to get the number of the last row for the for next loop.

User avatar
mol
Posts: 2801
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 112 times
Been thanked: 52 times
Contact:

Post by mol » Tue Sep 05, 2017 11:57 am

You have to know which row is occupied by data?
I'm testing first cell in row while importing data if it's not empty. If it doesn't contain any value, I'm finishing process.

Code: Select all

		nRow := 1
		do while .t.
			DO EVENTS 
			xCell :=oXLSSheet:Cells( nRow, 3 ):Value
			if empty(xCell)
				exit
			endif
			nRow++
		enddo

ROBROS
Posts: 56
Joined: Thu May 25, 2017 6:30 pm
DBs Used: DBF
Has thanked: 31 times
Been thanked: 1 time

Post by ROBROS » Tue Sep 05, 2017 12:15 pm

The problem is: the xls-sheet is provided by a report generator from another company and there are empty lines before the last line with data.

Post Reply