Open Excel from access

[King]

Okay I have no idea what to do here.
I have a form in access on which I have placed a command button to run MS excel. What I want this command button to do is
1- open a particular file in excel
2- The command button is on a from which contians infromation about landholders ( each of which have an ID number). The excel file I want to open has one worksheet per ID number. Is it possible to open the excel file via the command button and open the correct worksheet.
3- If the access from has a new landholder added to it can the command button open the excel file to a new worksheet?

I know this all sounds a bit complicated and I am very new to VB (which is why I'm asking)

Cheers

 

[SydneyGeek]

That's right. Make sure you have the closing \ for the path or else the macro will spit the dummy.

Denis

 

[King]

Hey Denis
Yes I think I have the file paths etc correct but still getting the same problem I'll paste the entire code this time.

Option Compare Database

Option Explicit

Const fPath = "S:\Temp\Darran King\" 'adjust to suit
Const fname = "test1.xls" ' Name of target file

Function OpenExcel()


On Error GoTo ErrorHandler

Dim appExcel As Excel.Application
Dim Wbk As Excel.Workbook
Dim Sht As Excel.Worksheet
Dim shName
Dim strSheet As String
Dim MyFile As String
Dim i As Integer

MyFile = fPath & fname 'Point to the Data file
shName = CStr([ID&A_Number].Value) 'adjust to suit your field name

'open the file
Set appExcel = GetObject(, "Excel.Application")
appExcel.Workbooks.Open (MyFile)
Set Wbk = appExcel.ActiveWorkbook
i = 0
'does the sheet exist?
'if so, activate it. Otherwise, create it first
For Each Sht In Wbk
If UCase(Sht.Name) = UCase(shName) Then
i = 1
Exit For
End If
Next Sht
If i = 1 Then 'sheet exists
Wbk.Sheets(shName).Activate
Else 'need to create the sheet first
Wbk.Sheets.Add Name:=shName 'create a blank sheet and rename it
Wbk.Sheets(shName).Activate
End If

'show Excel spreadsheet -- comment out to leave in background
appExcel.Application.Visible = True
ErrorHandlerExit:
Exit Function

ErrorHandler:
If Err = 429 Then
'Excel is not running; open Excel with CreateObject
Set appExcel = CreateObject("Excel.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If

End Function


I realise you are probably getting sick of me by now but I really appreciate the Help
cheers King

 

[Norie]

Wbk.Sheets.Add Name:=shNam

Try removing Name:= from this line. The error 'Named argument...' indicates that Excel does not recognise 'Name' as an argument of the Add method of the Sheets collection.

 

[SydneyGeek]

OK, I went through this with a test system. Found and fixed 2 errors, and the following code works:

Function OpenExcel()


On Error GoTo ErrorHandler

Dim appExcel As Excel.Application
Dim Wbk As Excel.Workbook
Dim Sht As Excel.Worksheet
Dim shName As String
Dim strSheet As String
Dim MyFile As String
Dim i As Integer

shName = Forms![frmEmployees].[FirstName] 'adjust to suit your field name
MyFile = fPath & fname 'Point to the Data file

'open the file
Set appExcel = GetObject(, "Excel.Application")
appExcel.Workbooks.Open (MyFile)
Set Wbk = appExcel.ActiveWorkbook
i = 0
'does the sheet exist?
'if so, activate it. Otherwise, create it first
For Each Sht In Wbk.Sheets
  If UCase(Sht.Name) = UCase(shName) Then
    i = 1
    Exit For
  End If
Next Sht
If i = 1 Then 'sheet exists
  Wbk.Sheets(shName).Activate
Else 'need to create the sheet first
  Wbk.Sheets.Add  'create a blank sheet
  Wbk.ActiveSheet.Name = shName 'name the sheet
  Wbk.Sheets(shName).Activate
End If

'show Excel spreadsheet -- comment out to leave in background
appExcel.Application.Visible = True

ErrorHandlerExit:
Exit Function

ErrorHandler:
If Err = 429 Then
'Excel is not running; open Excel with CreateObject
Set appExcel = CreateObject("Excel.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If

End Function

First problem: You need to specifically refer to the ID field using Forms![frmWhatever].[ID_Field_Name] syntax. This gets around Error: Object Required

Second Problem: In the For statement, I should have had the Sheets collection, thus -- For Each Sht In Wbk.Sheets

Third Problem: As Norie mentioned, naming the sheet as you create it is not supported in automation (works fine in Excel, though). So now there's another line to name the sheet.

Denis

 

[Norie]

naming the sheet as you create it is not supported in automation

Denis

I just tested in Excel the following

Worksheets.Add Name:="NewSheet"


Guess what error I got? Could be a version issue I suppose - I'm on 97.

 

[SydneyGeek]

Norie,

I know it works in XP -- I actually lifted the original post from a working system -- but I had to change it when the client upgraded from 97 so the version does matter.

Denis

 

[King]

Excellent It works perfectly thanks for your help and your patients I really appreciate it
King