Create Excel file (1 Viewer)

[tmyers]

How can I create a new excel workbook, save it with a name based off a field on my form then open the newly created excel file?

I would imagine it would be along the lines of creating a new excel application that is hidden, close it, rename it then reopen it.

My reason for this is to assure that the file is named correctly and saved in the right spot. I already store the file path I want to save to in a control on my form, so that part is easy.

I know how to create the file using the CreateObject method, but beyond that, I am lost.

 

[theDBguy]

What would the new Excel file contain? Can't you just create an empty Excel file and then simply copy it, instead of creating one everytime? Just a thought...

 

[tmyers]

What would the new Excel file contain? Can't you just create an empty Excel file and then simply copy it, instead of creating one everytime? Just a thought...

It would just be a blank workbook for the user to use as a worksheet, as sometimes what we do is done easier and looks cleaner in excel. I just want to make sure it is named and saved correctly for others to also see.

 

[theDBguy]

It would just be a blank workbook for the user to use as a worksheet, as sometimes what we do is done easier and looks cleaner in excel. I just want to make sure it is named and saved correctly for others to also see.

So again, for a blank workbook, is copying an empty one not available in this situation? Otherwise, yes, CreateObject would be the way to create a new Excel file.

 

[tmyers]

So again, for a blank workbook, is copying an empty one not available in this situation? Otherwise, yes, CreateObject would be the way to create a new Excel file.

I suppose copying could work.

 

[Minty]

Unless I'm really missing something here

Create a new spreadsheet, by default, it isn't called anything at this point

 Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add(-4167)

SaveAs Your File name in the place it needs to be.

    sPath = (TrailingSlash(CurrentProject.path))
    sFileName = Me.MyFileName 'Whatever you want to call your file
    xlWb.SaveAs (sPath & sFileName & ".xlsx")

Leave it open.

Now obviously this isn't the whole code as required but you get the idea?

 

[Isaac]

Just for fun since i'm already looking at this thread

dim excelapp as object, wb as object
set excelapp=createobject("excel.application")
set wb = excelapp.workbooks.add
wb.saveas 'options here:

 

[Isaac]

Ahh, too slow

 

[Gasman]

How can I create a new excel workbook, save it with a name based off a field on my form then open the newly created excel file?

I would imagine it would be along the lines of creating a new excel application that is hidden, close it, rename it then reopen it.

My reason for this is to assure that the file is named correctly and saved in the right spot. I already store the file path I want to save to in a control on my form, so that part is easy.

I know how to create the file using the CreateObject method, but beyond that, I am lost.

Wouldn't it already be open as you have just saved it with a set name?
Just show it?

 

[theDBguy]

I suppose copying could work.

Like I said though, if you really want to "create" a new object everytime, we can still help you with that. Just thought copying would be easier for you. Let us know...