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.
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.")
If intN > 26 * 27 Then
MsgBox ("N can't be bigger than 702.")
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)
'Return two letters (26 * 26 cases)
intAsciiA = 64 + a
DecimalToExcelCol = Chr(intAsciiA) & Chr(intAsciiB)
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