Can I export query data to .xlsm file

[puforee]

Office 2013. I have a need to export the results of a query to an Macro Enabled spreadsheet. I can pull the data from the spreadsheet but I really need to push it from Access.

Can this be done. Trying the manual Data - Export - Excel won't show excel files with the .xlsm extension.

Can this be done with VBA? If so, can someone show me a little code?

Thanks,

 

[SkipVought]

Hi,

I can pull the data from the spreadsheet but I really need to push it from Access.

Not knowing your process, (that is is this a snapshot of you table(s) at a particular time that would be missed if the data were pulled from Excel at a different time?) or is there some other reason for pushing the data out to Excel? Pulling data into Excel has always been my go-to process.

Alternatively, if this is a time or event sensitive issue, export the data as a .csv and have Excel IMPORT the text file in the Workbook_Open event. This can be a mere REFRESH once the query is established on a sheet.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[puforee]

Thanks Skip. It is an issue because of what happens next. Currently I do pull the data into the macro enabled excel from the db and it works. However I am trying to do a little automation for my users. The DB has one form with two buttons an update button and exit DB button. The update runs several table build queries to prepare the data. And the exit db button does Application.Quit.

Next, when the spreadsheet is opened it runs macros to update the data (Refresh All) from the db and format it for viewing.

What I was trying to do was open the spreadsheet form the db quit code..just before quit. But this is not working because as soon as the spreadsheet opens the focus on the db is lost and it won't quit. This causes the spreadsheet Refresh All to not work.

So, I thought, if I could push the data from the db to the spreadsheet I could remove the Refresh All code. But, it seems Access cannot export to an xlsm file type. I guess I will just have to have the users do two actions, db update/close, and then manually open the spreadsheet.

Again, thanks for the words of wisdom.

 

[combo]

For existing workbook I have no problem with:
[tt]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Query1", "E:\test\test.xlsm"[/tt]
Access adds worksheet to target workbook with the name same as the query name.


combo

 

[puforee]

That looks great. And, if I remember correctly, I should be able to select a sheet and range (Cell) with code also. Yes? Can you add a for instance for that to your code above? Like Shee1 Cell A2.

I would be very grateful.

 

[combo]

The [tt]Range[/tt] argument in Transferspreadsheet refers only to acImport action. For more precise action I think that you either have to (by code, automation) open workbook and process data or use excel CopyFromRecordset method, the latter does not transfer headers, so you still need some additional code.

combo

 

[Andrzejek]

the [CopyFromRecordset] does not transfer headers, so you still need some additional code". I use this code:

With xlApp
    For i = 0 To rst.Fields.Count - 1
        .Cells(1, i + 1) = rst.Fields(i).Name
    Next[green]
    'Copy the rst starting in cell A2 [/green]
    .Range("A2").CopyFromRecordset rst
End With

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Guys, I don't want to work from excel to the DB. I need to push from the DB to excel. I found a partial code through google. I show it below with my actual item names.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Combined_PTEAR", "C:\Users\xjjs8542\Desktop\Test.xlsm", "PTEAR Combined", "A1", True

I am transferring to an xlsm spreadsheet. Combined_PTEAR is a DB query that gathers my data. The next item is the Path to the spreadsheet followed by the spreadsheet Tab name followed by the cell range.

Dbug Compile likes it syntax wise.

The is the only code that runs. Nothing before it and nothing after it. When I run it I get:
Run-time error 2498: An expression you entered is the wrong data type for one of the arguments.

Any Ideas?

 

[SkipVought]

1) chech HELP on TransferSpreadsheet
2) insert a BREAK anduse the Watch Window to inspect the data types of esch argument

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[puforee]

Skip. I only have the one line of code shown above. When the error dialog box showed I went to help. It did not take me anywhere that talked about Transferspreadsheet. I brought up the watch window and it remained blank.

Can you detect anything? Do I need any code before I run the DoCmd?

Thanks

 

[puforee]

Everyone. I found the answer. New Code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Combined_PTEAR", "C:\Users\xjjs8542\Desktop\Test.xlsm"

None of the help articles were very clear but I got just enough from them. When doing an acExport the from item ("Combined_PTEAR") also defines the target in the spreadsheet you are exporting to. So, the data from my query went into my selected spreadsheet in a tab create or existing but the same name Combined_PTEAR. Since the code cannot define a range it goes into the A1 cell as the start of the range. This works great since I can easily name my queries to the names I want for my tabs in excel.

I will do some more testing but I think this will do nicely.

Thanks for all your help.

 

[Andrzejek]

According to this information, it looks like in [tt]DoCmd.TransferSpreadsheet[/tt] you need to specify the Excel file, but not which worksheet your data will go to.

What combo and I presented is what you want (I guess): " I need to push from the DB to excel" - which is what is does: it takes a recordset in Access and pushes it from Access to any place in any Excel file.


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Then here’s what I’d do. ONE TIME, in Excel, add a QueryTable to access Access, with et querybyou run in Access. That’s the one time task

Then on a recurring basis, from Access VBA, Open the Workbook and REFRESH the query rather than transferspreadsheet.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[puforee]

Skip...roger that. But, remember, I have a reason to push rather than pull. See the chain above for the reason. And I have used the Pull and refresh process many times in the past. As usual, I appreciate all your suggestion and training.