[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Acess VBA - Export query to Excel + Create New Sheets

Posted on 2006-11-15
4
Medium Priority
?
1,433 Views
Last Modified: 2008-02-01
Hi all,
Just a quick question... I have an access DB that exports a query called "lastName" to Excel. This works fine, but I want to be able to create a new sheet based on a variable called textInput.

Here is my code right now...

Private Sub Command6_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim querySQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("lastName")
querySQL = "My SQL query..."
If Me.searchText.Value = "" Then
    MsgBox ("Invalid Input")

Else
qdf.SQL = querySQL
DoCmd.OpenQuery "lastName"
Set qdf = Nothing
Set db = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "lastName", "C:\Desktop\MyField.xls", True

End If
End Sub


The query is called "lastName."

I want to take the results of that same query, but export it to a different sheet name. Any ideas?
0
Comment
Question by:gigantorTRON
[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
  • 3
4 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17948706
try specifing the sheetname
e.g

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "lastName", "C:\Desktop\MyField.xls", True, "Sheet2!"
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1040 total points
ID: 17948715
typo

try this - specify a range
eg

Sheet2$A1:Z65536

instead of Sheet2
0
 
LVL 1

Author Comment

by:gigantorTRON
ID: 17948793
Wow, I really thought that it would be harder than that for some reason!

Thanks for the quick response!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17948827
No probs :)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 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