Regnab wrote:

I've got my code working so that it'll count the number of columns in

the table and move across (eg Range A-P and then range Q-W). Problem is

when I get to the end of the single letters and get into AA etc. Have

got an idea of how I could do it by using Chr() and having an ongoing

counter which is divided by 26 when it goes past Z to work out

location. However, I'd dearly like there to be an easier way. Any

ideas?

Another problem I'm getting is my code is exporting the first 2 tables

fine to excel (moving across the columns), but the third one (who's

range is between Q-Y so the above problem should not apply) has a

runtime error:

"The Microsoft Jet Database could not find the object. Make sure the

object exists and that you spell the name and the path correctly".

Now it appears to work when I have the excel spreadsheet open (though

the export is A LOT slower). It also works if I don't export the first

table, and do only the last two (proving that the table does exist and

can be happily exported). I am rather mystified.... all help/ideas

appreciated.

Cheers

Reg

PS I could post the code if its helpful.... only thing is she's pretty

ugly - I'm new to this vb game (I gotta get me a book and learn some

basics).

Let X = number base 10

X = a * 26 + b where 0 <= a < 26, 0 <= b < 26 and 0 < X < 702. ab are

the Base 26 digits.

Obviously, X \ 26 = a

Thus b = X - (X \ 26) * 26

Now, convert ab Base 26 to letters. To obtain a one based

correspondence this involves a special case for b = 0 but none for 'a'

since 'a' contains an innate shift due to the first 26 cells. If a = 0

only output a single letter.

Examples:

1 A a = 0 b = 1

25 Y a = 0 b = 25

26 Z a = 0 b = 0

27 AA a = 1 b = 1

28 AB a = 1 b = 2

51 AY a = 1 b = 25

52 AZ a = 1 b = 0

53 BA a = 2 b = 1

Since b = 0 whenever X is a multiple of 26, any b = 0 corresponds to

'Z.' I don't know how far out Excel goes so I made it capable of going

up to ZZ.

Public Function DecimalToExcelCol(intN As Integer) As String

Dim a As Integer

Dim b As Integer

Dim intAsciiA As Integer

Dim intAsciiB As Integer

DecimalToExcelCol = ""

If intN < 1 Then

MsgBox ("N must be positive.")

Exit Function

End If

If intN > 26 * 27 Then

MsgBox ("N can't be bigger than 702.")

Exit Function

End If

a = (intN - 1) \ 26

b = intN - (intN \ 26) * 26

intAsciiB = 64 + b + Abs(b = 0) * 26

If a = 0 Then

'Return single letter (26 cases)

DecimalToExcelCol = Chr(intAsciiB)

Else

'Return two letters (26 * 26 cases)

intAsciiA = 64 + a

DecimalToExcelCol = Chr(intAsciiA) & Chr(intAsciiB)

End If

End Function

Perhaps the Q-Y thing in a matter of timing. That would explain why

omitting the first export would cause the other two to work since two

in a row didn't cause a problem. Maybe some DoEvents in a loop would

give each export time to finish writing the data before trying to do

another export. The exports working when Excel is opened beforehand

seems to implicate the time it takes Access to open an instance of

Excel. That extra time delay seems to exacerbate the timing problem.

I hope this helps.

James A. Fortune