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]

Try this: you'll need to paste this into a new module in Access. Name the module basExcel and save it.

For the Command button, you need to set it to trigger the code. Go to the form in Design view, right-click the command button to get its Properties, and select the Events tab.
Double-click in the blank line for On Click. You'll see [Event Procedure]. Click the builder (...) button to go the the code.
In the blank line at the cursor position, type basExcel.OpenExcel

Save and close the form.

Here's the code:

Option Explicit

Const fPath = "C:\My Documents\" 'adjust to suit
Const fname = "MyFile.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([SomeIdField].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

Denis

 

[King]

Gday Denis

Thanks for your help I really appreciate it

Ok I have tried what you said but in the first Dim statement I get an error which is User-defined type not defined what does this mean?

King

 

[SydneyGeek]

Go to the module and select Tools | References. Find the references for Microsoft Office XXX and Microsoft Excel XXX (where XXX is your Office version) -- then try again.

Denis

 

[King]

Excellent Denis

that has fixed that problem but now I'm getting an error at

Wbk.Sheets.Add Name:=shName

The error message is Named argument not found

Cheers King

 

[SydneyGeek]

Try this:

I reckon that this reference here
shName = CStr([SomeIdField].Value) 'adjust to suit your field name
needs to be changed to suit the name of your ID field. Otherwise shName will com eup as a null and cause problems.

Denis

 

[King]

Hey Denis
Yeh I have already done that but still getting the problem. As I said I'm pretty new at this VB stuff so I could Have stuffed up elsewhere here is the code as I have it so far.

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 may be way off the mark but should the Dim shName have something after it?

Really appreciating your help
King

 

[SydneyGeek]

Looks OK -- did you add the constants to the top of the module so you have a valid file name to work with?

Denis

 

[King]

I'm not sure what you mean so that probably means no I haven't. In which case what do I have to do?

King

 

[King]

sorry Denis I just realised what you meant, yes I did add the file paths etc
but I just make sure I got it right the fPath and fName are the location and name of the excel file I want to open is that correct?

King