Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Export data from Access to Specific Excel cells (Named Range)

Posted on 2011-03-25
14
Medium Priority
?
514 Views
Last Modified: 2012-05-11
Hi

I am currently using a transfer spreadsheet code to export a qry to a specific excel tab, however, I would like to export to a specific "starting" cell.

I think I need to build a dynamic range. I have been playing with this for hours. Please I need HELP!
0
Comment
Question by:kwarden13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 3
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35216427
what do you mean by building a dynamic range?

if cells destination will vary you can
* create named ranges and use this
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryName", xlPath, True, "NameOfrange"

* use recordsets

Set rs = db.OpenRecordset("Query1")
Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add
Set Sheet = xlObj.activeworkbook.Sheets(1)
   
'copy the headers
Dim iRow, iCol
iRow = 1
    For iCol = 0 To rs.Fields.Count - 1
        Sheet.cells(iRow, iCol + 1).Value = rs.Fields(iCol).Name
    Next


Sheet.Range("A2").CopyFromRecordset rs  'this copy just the data

Open in new window




0
 

Author Comment

by:kwarden13
ID: 35216451
The start cell of the destination will not vary, however how many records does. So there could be 1 or 500 records depending on the month.
0
 

Author Comment

by:kwarden13
ID: 35216461
all the fields are the same and I would like it NOT to export with headers and keep the formatting of the workbook everytime and just replace the named range
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 35216465

I generally use code similar to what capricorn1 shows in his code, above.
0
 

Author Comment

by:kwarden13
ID: 35216495
ok I will give it a try will it meet all my needs. Just want to make sure before I waste more time on this.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35216513
<So there could be 1 or 500 records depending on the month.
 I would like it NOT to export with headers and keep the formatting of the workbook everytime and just replace the named range >

just create a Namedrange  big enough to handle your varying records, say 1000 rows

A2:F1000  < select this cells a give it a name, then use the namedrange here


   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryName", xlPath, True, "NameOfrange"
0
 

Author Comment

by:kwarden13
ID: 35216602
So the named range only works 1 time. If I tell i to export twice, it creates a new tab. Also, the headers are still there
0
 

Author Comment

by:kwarden13
ID: 35216609
So 1st try the named range was: ='Past Due'!$A$6:$O$1048576

2nd try the named range was: ==#REF!$A$1:$O$938
0
 

Author Comment

by:kwarden13
ID: 35216615
export function is
 
Function Delinquents()

DoCmd.TransferSpreadsheet acExport, , "qryDelRepCAN", "File_Path", True, "Canada_Past_Due"

End Function

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35216760
how are you creating your named range?
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 35216801

Are you using the named range for anything besides this export?

If so, you could use Cap1's code (minus the headings) to export so that the record 1, column 1 of your recordset appears in cell "A2" (or whatever cell you want), then you could delete the named range (also via VBA), and then recreate it via code.

You might also consider making the range dynamic, see the solution in:  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_22468473.html?sfQueryTermInfo=1+10+30+creat+dynam+excel+name+rang+vba
0
 

Author Comment

by:kwarden13
ID: 35216946
No I am just using it for Export. I am creating it by clicking on the Formulas tab > Define Name > and highlight the cells I want to be in the range.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 35217026

Personally, I'd get rid of the range altogether and use code similar to Cap1's.
0
 

Author Comment

by:kwarden13
ID: 35217908
I will try it
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question