Developer Forum

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

Cheers James - will try out that first bit of code tomorrow morning and
post how she goes. I tried putting in a delay (running a dummy query
and closing it again), but that didn't seem to do it. When I've put
your code in and cleaned mine up so I don't look so ignorant, I'll post
it all and see if you've got any ideas. Thanks heaps for all the help
champ,

Cheers

Reg