• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

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

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
kwarden13
Asked:
kwarden13
  • 8
  • 3
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
kwarden13Author Commented:
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
 
kwarden13Author Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Dale FyeCommented:

I generally use code similar to what capricorn1 shows in his code, above.
0
 
kwarden13Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<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
 
kwarden13Author Commented:
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
 
kwarden13Author Commented:
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
 
kwarden13Author Commented:
export function is
 
Function Delinquents()

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

End Function

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
how are you creating your named range?
0
 
Dale FyeCommented:

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
 
kwarden13Author Commented:
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
 
Dale FyeCommented:

Personally, I'd get rid of the range altogether and use code similar to Cap1's.
0
 
kwarden13Author Commented:
I will try it
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now