473,837 Members | 1,603 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Exporting to Excel: Multiple tables in 1 spreadsheet - the last of 3 tables won't export.

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).

Nov 13 '05 #1
2 3479
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 DecimalToExcelC ol(intN As Integer) As String
Dim a As Integer
Dim b As Integer
Dim intAsciiA As Integer
Dim intAsciiB As Integer

DecimalToExcelC ol = ""
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)
DecimalToExcelC ol = Chr(intAsciiB)
Else
'Return two letters (26 * 26 cases)
intAsciiA = 64 + a
DecimalToExcelC ol = 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

Nov 13 '05 #2
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

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
9251
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works only exporting to single worksheet. but i need to export data to multiple worksheets. it is very urgent to us. so please help me in code.
4
14796
by: Paolo | last post by:
Friends, I need help with some code to export different tables to a single spreadsheet in Excel. My excel file is named REPORT and the spreadsheet is named CLIENTS. I do have the code to export a single table to Excel but have problems with multimple tables. Thanks.
5
11711
by: jsudo | last post by:
I have a Access database with a large amount of records (close to 500,000) that I would like to export to Excel. I found out that Excel has the capability of of about 65,000 rows so I know I cannot export the whole file at once. What I would like to do is divide the database records into smaller groups that Excel can handle. Does anyone know how I can export a group of records to Excel without getting an error? I tried exporting...
13
3255
by: Arno R | last post by:
Hi all, I will have to handle a lot of really 'nice' data in a LOT of Excel sheets. It is all about music files (Billboard top 100) I am afraid there really is a sheet for every year ... (Don't know this for sure yet...) I definitely need to get this data in Access and get it normalized. (sigh ....) The data is so 'nice' for the Import- and TransferSpreadsheet-wizard that the 'wizard' really chokes on it ;-) Either the data is not...
2
3071
by: deko | last post by:
I use a complied query to export to Excel like this: SELECT * INTO . FROM tblExcelData; But I have a situation where I need to export several tables into the same worksheet. The idea is to have each contiguous block of data on the worksheet separated by 50 or so rows so a graph can be inserted between each set of data.
21
6258
by: bobh | last post by:
Hi All, In Access97 I have a table that's greater than 65k records and I'm looking for a VBA way to export the records to Excel. Anyone have vba code to export from access to excel and have the code use multiple excel tabs within a workbook???? Anyone have vba code that would create a temp table write 65,000 records to it, export those to excel, clean the temp table, append the next 65,000 records, export it to excel with a different...
2
3190
by: Snozz | last post by:
The short of it: If you needed to import a CSV file of a certain structure on a regular basis(say 32 csv files, each to one a table in 32 databases), what would be your first instinct on how to set this up so as to do it reliably and minimize overhead? There are currently no constraints on the destination table. Assume the user or some configuration specifies the database name, server name, and filename+fullpath. The server is SQL...
1
9781
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm having I'd be most appreciative. The database is already constructed, I'm just wanting to export the data to an excel file. In short, I'm hoping to export two Tables (or queries...not sure which to use - they both seem to have the same data) in...
2
7659
by: nofear | last post by:
I used to export my reports as snapshot but now I have to export them to Excel When I export my report to a Excel Spreadsheet the report header and footer are not included Only the data gets exported. How do I format a Access report for Excel the right way?
0
9852
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9696
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10642
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10288
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7824
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7014
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5680
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4062
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3128
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.