Hi,
I thought this was a simple issue, but apparently it's not. I need to export the contents of about 30 queries, each into a specific sheet, and cell range, of an existing Excel workbook.
Here are all of the details I need in one sample transfer:
Query = "Query A"
Excel workbook = "C:\Documen ts and Settings\All Users\Workbook1 .xlsx"
Sheet = "BB DATA"
Range = B2:J32
HasFieldNames = True
There are a lot of threads about exporting to Excel from Access, but I have searched all afternoon and can't find an answer that works. Sorry if I'm being a noob on this, but I just can't figure this one out.
Thanks so much for any help
11 45752
Hi CarrieR,
Had you tried using the DoCmd.TransferS preadsheet function? -
Dim fileName As String
-
fileName = "C:\Documents and Settings\All Users\Workbook1.xlsx"
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query A", fileName, True,"B2:J32"
Here's something extra I work along with as well, creating the file name more dynamically. The code below will create the excel file in the same location where the database located, and the file is named with current date when executed. - fileName = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "Report" & "-" & Format(Date, "yyyymmdd") & ".xlsx"
The transferSpreads heet command would only be exporting the query to a new excel file, or replacing an existing file. So put this as a reference for creating a new file.
Yes, the problem with the TransferSpreads heet command, unfortunately, is that it won't export to a specific sheet. This is a pretty vital need, since the spreadsheet is a (complex) template for a lot of reporting...
Sorry I maybe mis-understood the question. As you want to put the generated records into existing workbooks (excel files), here's a sample code on what I've done from my work. - Dim xlApp As Excel.Application
-
Dim xlWB As Excel.Workbook
-
Dim xlWS As Excel.Worksheet
-
Dim acRng As Variant
-
Dim xlRow As Integer
-
-
Dim qry As QueryDef
-
Dim rst As Recordset
-
Set xlApp = New Excel.Application
-
Set xlWB = xlApp.Workbooks.Open("C:\Documents and Settings\All Users\Workbook1.xlsx")
-
Set xlWS = xlWB.Worksheets("BB Data")
-
-
xlRow = (xlWS.Columns("A").End(xlDown).Row)
-
-
Set qry = CurrentDb.QueryDefs("Query A")
-
Set rst = qry.OpenRecordset
-
-
Dim c As Integer
-
c = 1
-
xlRow = xlRow + 1
-
-
Do Until rst.EOF
-
For Each acRng In rst.Fields
-
xlWS.Cells(xlRow, c).Formula = acRng
-
c = c + 1
-
Next acRng
-
xlRow = xlRow + 1
-
c = 1
-
rst.MoveNext
-
If xlRow > 25 Then GoTo rq_Exit
-
Loop
-
-
rq_Exit:
-
rst.Close
-
Set rst = Nothing
-
Set xlWS = Nothing
-
xlWB.Close acSaveYes
-
Set xlWB = Nothing
-
xlApp.Quit
-
Set xlApp = Nothing
-
Exit Function
-
The code before at the end of loop is simply limiting the copy process only allow 25 records, so remove it as you like. In the end of the exit part, it is a good hand on having a .Close and .Quit, otherwise the excel won't close itself and you need to kill the process from Task manager.
Thanks for your help!
Sorry this response is a little late - I had to leave and come back to this particular problem for a while...
Problem is solved (mostly, except for one or two small issues that I can work around).
I did want to note the issues here, for others that might see this thread, or in case anyone wanted to respond.
To note, I followed the above code exactly. To adjust the starting row the query would be exported to, I changed these lines: -
Dim c As Integer
-
c = 1
-
xlRow = xlRow + 1
the "x1Rox + 1" will insert the file data starting in Row 3. So to begin the data in Row 41, it would be changed to: -
Dim c As Integer
-
c = 1
-
xlRow = xlRow + 39
If I misunderstood any of this please let me know. These were the two issues I had:
1. I couldn't adjust the column that the first record appeared in, even if I adjusted: -
xlRow = (xlWS.Columns("A").End(xlDown).Row)
to: -
xlRow = (xlWS.Columns("B").End(xlDown).Row)
Not sure if changing the first column can be done. To solve my issue, I just made sure all of my destinations began in column A, which worked out fine for this project.
2. Export did not export the field names, only the data. Again, this was OK, but not sure if this is because I was doing something wrong.
Yes you did misunderstood the code you tried to modified. - Dim c As Integer
-
c = 1
-
xlRow = xlRow + 1
xlRow simply record the row number of the 1st empty cell in your spreadsheet in column A. So if A3 is the first empty cell from top, then you will get 3 in xlRow+1, and therefore change the column from reading A to B is correct, if you are leaving column A for something else.
The variable c is the one that stores column number, in which c = 1 means column A, c will be 2 for column B and so on.
In part of the code I posted earlier. - xlWS.Cells(xlRow, c).Formula = acRng
If xlRow+1 is 41, and c = 3, then the xlWS.Cells will be point to cell C41
Thanks, I appreciate the clarification, and patience! This did exactly what I needed to do.
Just to note:
I changed the initial set of "xlRow = xlRow + 1" (the one before the loop) to simply set "xlRow = 3", or whatever row number I need for each of these I need to write. This worked perfectly.
Thanks again for your help!
No worries =)
As if you will be overwriting the information whenever you generate the data, then I do agree you use xlRow = 3, so that it will starts putting data from row 3 every time you execute the code.
hi,
I tried and this too works... - DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query A", fileName, True, "BB DATA!B2:J32"
change the range as required... may be for 6th row.. it may come like "BB DATA!A6:J32"
Hi, If you use a named range in your spreadsheet you can export directly to it, for example
On worksheet:
Create a named range for DATA A6:J32 = "Range1" - DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query A", fileName, True, "Range1"
This will export directly to the correct sheets and range.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: DC Gringo |
last post by:
I have a simple button that should open another window and export a datagrid
to an Excel file. I'm getting: "Name 'window' is not declared."
What do I need to declare or import?
<INPUT ID="Button5" ONCLICK="Button5_Click" NAME="Button5" TYPE="button"
VALUE="Export to Excel">
Sub Button5_Click()
|
by: Hemant Sipahimalani |
last post by:
The following piece of code is being used to export HTML to excel.
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
HttpContext.Current.Response.AddHeader("content-disposition",
"attachment;filename=ABC.xls")
HttpContext.Current.Response.Write(strHTML)
HttpContext.Current.Response.End()
However when the user tries to save it the Default File Type is Web
Page(*.htm; *.html)
|
by: Pradnya Patil |
last post by:
Hi ,
I need to export some of the reports to MS EXCEL & MS WORD
in a WEB
APPLICATION.I also need to LOCK some of the Columns in
EXCEL-sheet.Right now I need to
run the Interoperability setup on the machine where the software will
run.The machine
|
by: JawzX01 |
last post by:
Hello All,
First, thank you for any help you can provide.
I'm trying to do a simple export to excel. I've used the classic
code that is all over the internet, and of course it worked without a
problem.
The problem is that I want to add text above the data grid in the
excel sheet. The text already exists on the web page in various panels
(there is different text depending on the parameters of the datagrid
|
by: tasmontique |
last post by:
I am working on an access 2002 flight schedule database. I am new to access but have some basic understanding of sql and vb6 code.
I have learned a lot from this website. Thanks much
Hopefully you can help me with this one.
This database handles a varying number of flights for a varying number of days for a varying number of months.
I have a number of tables as follows. I tried to normalize to the best of my ability.
| |
by: yovation |
last post by:
Hi,
I have a 3 table database.
1 parent
1 child
1 child of child
I would like to get the data into excel as 1 sheet (similar to a
grouped report).
|
by: Access user |
last post by:
My apologies for crossposting this, but I did not get any response in
microsoft.public.access
http://groups.google.com/group/microsoft.public.access/browse_thread/thread/4929214ae589afd9/b11bf56313b894d4?lnk=st&q=access+export+to+excel#b11bf56313b894d4
The specific problem is mentioned in the above post, but here is a
basic description of the problem.
When using saved queries that are used as part of a union query, the
UNION query...
|
by: hemantbasva |
last post by:
Note We need to have a template on server for generating report in multiple sheet
as we do not had msoffice on server
moreover this require a batch job to delete excel file created by the method....
it creates 6 sheets
# region Namespaces
using System;
|
by: jay manu |
last post by:
I need to export excel data sheet to ESRI Arcmap10 frame work. Join using lat lons and make new layers and save it.
Can anyone help me to find the solution?
|
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...
|
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,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
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...
| | |