Using VBA in Access - check to see if an excel file is open, and if not, open it, otherwise activate it

shanesuzanne

New Member
Joined
Dec 1, 2016
Messages
21
I have pieced together code that emails an excel spreadsheet as an attachment from Access, using a non-Outlook email client.

The file may or may not already be open, and it is password protected. I have the code working to see if a specific file is open in Excel (I only want 1 instance of this file open). What I can't get working is that if the file is already open, to make that the Active workbook for further action (running a macro that emails the file to email addresses in the file in the email tab). There may be multiple files open on the users machine - I want it to look for a specific one - no user prompts or changing of the file name is necessary.

I'm pasting all parts of my code, so once I get it working it can be used of benefit to others.

********************************************************

Excel macro code (works good):

Sub emailAsAttachment()
Dim wkbk As Object
Dim wkst As Worksheet
Dim lastRow As Long

Set wkbk = ActiveWorkbook
Set wkst = Worksheets("email")
wkst.Visible = True
wkst.Activate
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

'the email addresses are in the sheet called "email", in column A - this loops through each email in that tab and emails this spreadsheet as an attachment
For Each c In wkst.Range("A2:A" & lastRow)
If c.Value <> "" Then
distList = c
End If

wkbk.SendMail Recipients:=distList

wkst.Visible = False

Next c
End Sub


Access IsFileOpen code (works good):

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function


Here is my ACCESS code that I need help with:


Sub cmdRates_Click()
Dim xlApp As Excel.Application

Set xlApp = CreateObject("Excel.Application")

If Not IsFileOpen("G:\Finance\Rates.xlsm") Then
Workbooks.Open "G:\Finance\Rates.xlsm", , , , "12345"
Else
'this is the code that errors out - I need it to activate the workbook that is already open
xlApp.Workbooks("Rates.xlsm").Activate
End If

'runs the macro emailAsAttachment from the file above
xlApp.Run "emailAsAttachment"

'cleanup
Set xlApp = Nothing
Set xlWb = Nothing


End Sub
****************************************************************


Thanks in advance for your time and help!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Does this work?

Code:
    Else
        'this is the code that errors out - I need it to activate the workbook that is already open
        Windows("Rates.xlsm").Activate
    End If
 
Upvote 0
I think if you open it read-only it won't matter if it's already open.
And actually, it's better if you do it that way anyway so you can open it, send the email, and then close it without messing with already open workbooks.
 
Last edited:
Upvote 0
Every time I try to use the code to open a file that is already open (as Read only), it makes me manually type in the password again - even though it's stored in the code. That's what started me down this path. Any suggestions on how to avoid that?

I also worry a user could have a copy open with changes, that may be missed if I'm sending a read-only copy based on their last save. But this wouldn't be a deal breaker like having to manually enter the password on the read-only file.

Thoughts?
 
Upvote 0
I'm not familiar with opening files with passwords but I presume its the same whether its open or not open - you are opening the file in both cases, aren't you? So, need password. Use the same method passing in a password for the password.

To open as read-only, you need to pass a value for the read-only parameter.

Whether you open read only or use an already open workbook, you will not get anything other than the last save either way, so again, same thing.

Is this supposed to be a shared workbook? Prefer not to work in code opening workbooks that might be open by somebody else, if you really want my opinion (and if you don't, I just gave it anyway!).
 
Last edited:
Upvote 0
xenou - thanks for your responses - I very much welcome all opinions.

It's actually the EXACT same line of code that opens the file - if it's not open yet, it passes the password and opens the file without prompting for the password. If it's already open, it opens a read-only copy - BUT - it prompts for the password. Since it's the same exact line of code that is being executed, it makes no sense to me.

And it's a file that many others in the office use, and it occasionally has to be emailed to others - but it's NOT shared - so only one user can make edits at a time. You bring up a good point about getting the last save - I was actually thinking of doing a save in the code after activating it. No one should be emailing the file without opening it and reviewing it first - which is why I worry often it will be open on the users machine when they run the code to email it from Access.
 
Upvote 0
Why not email it from Excel instead of from Access?

This is not what I mean:
It's actually the EXACT same line of code that opens the file

Open it with the read-only flag set to true. Then it will always open read-only (open or not) and you should not get prompts. Although I don't know why it would still prompt for a password. I don't use passwords on excel files so I have no experience with that.
 
Last edited:
Upvote 0
I finally got it working after quite a bit of tweaking. Thanks all who responded and offered their help! (The Access sub IsFileOpen and Excel Macro emailAsAttachment remain unchanged. It's the "Here is my ACCESS code that I need help with" that I modified and tweaked.)

Access vba code:
Code:
Sub cmdRates_Click()
    If Not IsFileOpen("G:\Finance\Rates.xlsm") Then
       Excel.Application.Workbooks.Open _
        fileName:="G:\Finance\Rates.xlsm", _
        Password:="123456"
        
        Excel.Application.Visible = True
       
    End If
        
    AppActivate "Rates"
    
    Excel.Application.Run ("emailAsAttachment")
    
    MsgBox ("The report has been emailed.")
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top