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

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

1. Right Click -> Export To Excel Is Exporting The Wrong Table?

2. exporting table to existing Excel spreadsheet?

3. Exporting Access Table/Query to Excel Spreadsheet

4. Exporting Word Table (2003) to Excel spreadsheet

5. Export data from an Excel Spreadsheet to an Access Database Table

6. exporting multiple tables or parts of tables

7. Export Error Table created After Exportin A Table From Access To Excel?

8. Export to Excel, name and format exported spreadsheets

9. Exporting from multiple tables in Access To Excel

10. export multiple tables to one excel worksheet of workbook

11. Export Access Table into multiple Excel Workbooks

12. Export table to multiple excel worksheets`

13. Exporting a multiple table Access file to Excel

14. Excel Mobile wont open spreadsheet exported from Access 2000

15. Export Access table to Excel across multiple tabs

3 post • Page:**1** of **1**