Delete a worksheet from an Excel Workbook

[JimfromIndy]

From Access 2003, I created a new Excel workbook using the Transferspreadsheet method. It works.

I actually want a blank workbook, so I create a new worksheet, set my active sheet to that new sheet, then try to delete the original sheet. The original sheet WON'T GO AWAY. After I close the workbook, it's still there. No open excel processes, either. What am I doing wrong?


Private Sub btnExportExcel_Click()
Dim ObjExcel As New Excel.Application
'Set ObjExcel = CreateObject("excel.application")

Dim strLocation As String
Dim strFileName As String
strLocation = "c:"
' Put code here to locate the spreadsheet...
strFileName = fOSUserName() & Format(Now(), "yymmddhhss") & ".xls"
Dim x As Excel.Workbook
Dim y As Excel.Worksheet
'DoCmd.OutputTo acOutputTable, "tblHoursBudget", , strLocation & "\" & strFileName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblHoursBudget", strLocation & "\" & strFileName
Set x = ObjExcel.Workbooks.Open(strLocation & "\" & strFileName)
x.Worksheets.Add
x.ActiveSheet.Name = "Project " & ProjectID.Column(1)
Set y = x.ActiveSheet
x.Sheets("tblHoursBudget").Select
x.ActiveSheet.Delete
x.Worksheets("tblHoursBudget").Delete
y.Activate
x.Save
x.Close (True)
Set y = Nothing
Set x = Nothing
Set ObjExcel = Nothing

End Sub

[JimmyTheHand]

Hi and welcome to VBAX :hi:

If the only thing you want is a blank workbook, why bother with TransferSpreadsheet method?

Sub CreateBlankWorkbook()
Dim ObjExcel As New Excel.Application
Dim x As Excel.Workbook
Set x = ObjExcel.Workbooks.Add
end sub

HTH

Jimmy

[JimfromIndy]

Jimmy,
Your answer helped me alot, but the Excel Model is still a little obscure to me. My problem is trying to figure out what methods apply to what objects (application, workbook, worksheet, etc.) For example, I want to set certain columns to "autofit". I couldn't figure it out, so I did a quick "Record New Macro" and examined the result:

Columns("B:P").select
Columns("B:P").entirecolumn.autofit


That's great, I figured out the worksheet.columns object takes a range, and has a method called entirecolumn.autofit (or something like that...)

But, Now I want (in vba from Access 2003) to select a discontinuous series of columns I understand in numeric coordinates.

So, how do I represent a discontinuous set of columns?
And how do I convert my numeric understanding of the column identifiers (other than bludgeoning it to death with a translation array)?
:banghead:You don't have to "answer" the question, so much as point me to resources. I'm perfectly willing to read and test. I just need some strategy help.

Thanks again Jimmy!

JimS

[JimmyTheHand]

Hi JimS

Handling an Excel object from Access application is, in my experience, pretty much the same as handling it from Excel application. The main difference is that in Access you need to explicitly create the Excel.Application object, and also qualify everything properly, because Access may have other default qualifiers.
E.g. when in Excel you write simply
Set WS = Workbooks("Test").Sheets(1)
Set rngWhatNot = Union (WS.Range("A1"), WS.Range("C3")
in Access you need to do it this way:
Set ExObj = New Excel.Application
Set WS = ExObj.Workbooks("Test").Sheets(1)
Set rngWhatNot = ExObj.Union(WS.Range("A1"), WS.Range("C3")


As for referencing a discontinuous range of columns, I don't know. I do know that it can be done like this:
Range("A:A, C:C, F:F") but to do the same using numbers instead of letters, I have no idea.
Maybe a loop would do, where you supply the colum indices via an array. Or something.

As it is really an Excel problem, I suggest you take this question to the Excel Forum on VBAX. And when solved it should be easy to adopt into Access environment.

VBAX Excel Forum is the best source of information that I know of. Other than that, I can also recommend Chip Pearson's site and Ozgrid's. And macro recorder, and VBA Help.

Jimmy

[Norie]

JimS

What are you actually trying to achieve here?

This seems a bit contradictory.


No open excel processes, either

Since you have this in your code.

Dim ObjExcel As New Excel.Application
' ...
Set x = ObjExcel.Workbooks.Open(strLocation & "\" & strFileName)

If you want to use numeric references for ranges take a look at Cells or even Columns.

By the way one of the best ways to find code for Excel VBA is the macro recorder.