Exporting to new Workbook

[Guest]

Hi all,

I'm trying to export the values of an excel worksheet. I can get the values
over to a new file, but I'm stuck on how to do if it I don't have a file name
beforehand. I'm not entirely sure it's possible, so I thought I'd ask. I
haven't found any examples for this specific problem, but if someone can
point me in the right direction, it'd be appreciated. On to the problem:

After a user clicks a button on a form in A97, I want data to be transferred
to an excel worksheet and a pivot table created (in A97 VBA) from that data.
I've got the data transfer working, but I want the data to be created in a
default workbook, not in a previously saved workbook, and I can't figure out
how to do it.

Here's the order I'm trying to do things and my code.

Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWsht As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlWbk = xlApp.Workbooks.Add

I create two worksheets ("data" and "pivot") for the workbook.

Set xlWsht = xlWbk.Worksheets.Add
xlWsht.Name = "data"
Set xlWsht = xlWbk.Worksheets.Add
xlWsht.Name = "pivot"

I then delete the Sheet1, Sheet2, Sheet3 sheets, because I don't want the
default worksheets. I don't know of a way to stop them from being created in
the first place.

xlWbk.Sheets("Sheet1").Delete
xlWbk.Sheets("Sheet2").Delete
xlWbk.Sheets("Sheet3").Delete

Here's where I run into an issue. I don't know how to specify the workbook
I'm working in without a path name for the file. The following produces an
error:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tblDetail",
xlWbk, True, "data"

I then want to open that default workbook in Excel and then cut all ties to
Access.

I tried:
xlApp.Workbooks.Open (xlWbk)
xlApp.Visible = True
But I get an error but don't know what I can do to get the workbook
displayed in excel without initially saving it.

Basically, I want all the saving functions and such handled within excel. I
only want excel to handle the creation and displaying of the new workbook.
After that, I don't need A97 unless it's to create another workbook.

Any help is greatly appreciated.

Thanks,
Jay

 

[Guest]

Whoops. Made a mistake in the first sentence. Allow me to rephrase:

I'm trying to export the values of a query to an excel worksheet.

Jay

 

[Ken Snell [MVP]]

You can use this concatenation to get the path and file name of the xlWbk
workbook:
xlWbk.Path & "\" & xlWbk.Name

 

[Guest]

That works, but it still causes an error.

However, if I just write: xlApp.Visible = True, then it opens excel and
displays the workbook I created. I don't know if that's a good way to do it,
but it seems to work.

Thanks though, I never thought of doing what you did. It did help.

Cheers,
Jay

 

[Ken Snell [MVP]]

Might be causing an error because the workbook hasn't been saved yet,
therefore VBA isn't able to access it as a regular file.

 

[Guest]

Perfect, thanks again for your help.

Jay

Might be causing an error because the workbook hasn't been saved yet,
therefore VBA isn't able to access it as a regular file.