Exporting Queries to Excel issue

[5576joe]

Hi All,

I have been searching all over for a solution to my problem but can't find it anywhere. I am fairly new to access and VBA so please help.

I have a macro that exports a number of queries into an excel file saved on my desktop (Position Trend.xls). Currently the macro exports the queries and saves the file before opening it. What I want is for the macro to export the queries and either save the file as a new name (prompted or unprompted) or to not save the file at all, i.e. I want the original template to remain untouched.

I don't know if this is possible or if there's a more logical way of doing things but any advice will be appreciated.

CODE:


Public Sub ExportTblToExcel()
Const FILE_PATH As String = "C:\Users\o588860\Desktop\Rough Work\"
Dim oApp As Object
Dim FULLPath As String
strFullPath = FILE_PATH

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "Position_Trend", strFullPath & "PositionTrend.xls", False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "Bonds_Static_Data", strFullPath & "PositionTrend.xls", False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "PricingTrend", strFullPath & "PositionTrend.xls", False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "LiborCCY", strFullPath & "PositionTrend.xls", False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "Position_FVA", strFullPath & "PositionTrend.xls", False

DoCmd.SetWarnings True

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

oApp.workbooks.Open "C:\Users\o588860\Desktop\Rough Work\PositionTrend.xls"

End Sub

Thanks!
Joe

 

[pr2-eugin]

Hello 5576joe, Welcome to AWF..

If I am correct, the DoCmd.TransferSpreadsheet function simply replaces any older files you have.. Looking at the code you have, at the end you will only have one PosiyionTrend.xls file with the Position_FVA result in it. Is this correct?

Either you need a Excel automation here or a different naming standard for each file. Simple one on top of my head is.

strFullPath & "PositionTrend - " & Int (Now * [URL="http://www.techonthenet.com/excel/formulas/rnd.php"]Rnd[/URL]) & ".xls"

 

[5576joe]

Hi Paul, thanks for the response.

Yes at the end I have a file with the five queries in five different tabs. I then have a number of other tabs that read the data and do some charts/analysis.

I still want them all in the one file that has the exisiting charts etc. in it, but for it to save as a different name.

Hope that helps.

 

[pr2-eugin]

I remember helping out another user with quiet the similar request, this uses Excel Automation. So I would advise you to look into it, see if you follow, try adapting it to your Code.

It should be quiet simple. Post back if you have troubles.

 

[5576joe]

Thanks a lot, I'll have a look and get back to you if any issues.