Refer to a Worksheet

[ddrew]

How do I reference a specific worksheet in an excel workbook.

This is part of a restore process that I am doing. My code is below:

Dim sSource As String    'The name of the file to be copied

    sSource = "c:\Gundog Training Databases\BackUp\"

        'Ask if you want to overwrite it
    If MsgBox("Are you sure you want to restore an older version of your data?", vbQuestion + vbYesNo) = vbYes Then

        'Copies the files from Excel
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Animal", sSource & Me.BackUpName, True
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Breed", sSource & Me.BackUpName, True

End if

The bit I dont know is what to put after 'Me.BackUpName, to reference the specific worksheet relevent to the table in Access.

Sheet names are the same as the table names as this would be getting restored from a backup!

 

[nanscombe]

I'm out and about at the moment but perhaps you might find something useful here ->

http://msdn.microsoft.com/en-us/library/bb214134(v=office.12).aspx

In MSDN under "DoCmd.TransferSpreadsheet Method" in case the link doesn't work

 

[tehNellie]

If you need to write to specific, named worksheets rather than have the docmd. export create a worksheet I think you'll need to manipulate Excel from within your code:

dim xlapp as object
dim xlwBook as object
dim xlwsheet as object

Set xlApp = CreateObject("Excel.application")

'make Excel Visible so you can see what is going on
xlApp.Visible = True

'open an existing file
set xlwBook = xlApp.Workbooks.Open(FileName:=strPath)

'or create a new file
set xlwBook = xlApp.Workbooks.Add

'go to an existing worksheet
 Set xlwSheet = xlwBook.Sheets("WorksheetName or Index number")
      
 'or add a new worksheet (to the end of the current worksheets) and give it a name         
 Set xlwSheet = xlwBook.Sheets.Add(After:=xlwBook.Sheets(xlwBook.Sheets.Count))
 xlwSheet.Name = "NameoftheSheet"


'open your output query into a recordset
set rs = db.openrecordset("outputQuery")

'Paste the column headers into your worksheet
      For i = 1 To (rs.Fields.Count)
      
        xlwSheet.cells(Row, Column ).value = rs.Fields(i - 1).Name
      
      Next

'copy your recordset to your worksheet
xlwSheet.Range(ColumnLetter &  RowNumber [i]i.e. "B1"[/i]).CopyFromRecordset rs


'save your workbook
xlwBook.saveas FileName:="Path and filename i.e. c:\temp\myspreadsheet.xls"

'close and quit excel
      xlwBook.Close True
      xlApp.Quit
    
  'dispose of the objects.
  Set xlwSheet = Nothing
  Set xlwBook = Nothing
  Set xlApp = Nothing

 

[nanscombe]

It would appear that to import a specific Worksheet from Excel into an Access table you would need to use -

docmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, [B]TableName[/B], [B]FileName[/B],
 HasFieldNames, [B]Range[/B])

Where Range is in one of the following forms:

"A1:G99" - A range of cells
"Sheet2!A1:G99" - A range of cells on a particular worksheet
"Sheet2!" - An entire worksheet


Personally, I would import into a temporary table and manipulate it from there.