Free Microsoft Excel 2013 Quick Reference

How to view access file if already opened using VBA?

Hi,
I searched and there are a ton of links on how to open access files using VBA, but I couln't find (or maybe i wasn;t looking in the right place) code on how to bring the access file in foreground if it's already opened. Below is the code I use with no luck.
    'open the access tool if not already open
    If Not IsFileOpen(strPath) Then         'IsFileOpen checks and returns true if file is already open depending on strPath (file path) 
        
        'close any open access instances
        Call CloseAllAccess
    
        Set oApp = CreateObject("Access.Application")
        oApp.AutomationSecurity = 1
        oApp.OpenCurrentDatabase strPath    
        oApp.Visible = True
        oApp.DoCmd.OpenTable "tbl_Material"
        oApp.DoCmd.OpenForm "frmCPanel"
        Set oApp = Nothing
    Else
        Set oApp = CreateObject("Access.Application")
        oApp.AutomationSecurity = 1
        oApp.OpenCurrentDatabase strPath                    'i am pretty sure this line shouldn;t be here, but i can;t figure what to put instead
        oApp.DoCmd.OpenTable "tbl_Material"
        oApp.DoCmd.OpenForm "frmCPanel"
    End If
Thanks


How to convert Columnar Data Range in UPPERCASE using VBA and not formulas?

Dear Forum,

In a Table I need to have certain Columns of Data in UPPER case, so how do I do the same for existing data , I know about the UPPER function but I want to convert the data which is already there with a Macro.

Also, need some suggestions on using the COUNTA function in VBA..I will be copying data from an Company Application in Excel..

I need to make this Data look neater with BORDERS only till the last filled Row and the remaining portion will not have any GRIDLINES..

I am doing this right now, as am sill no good in VBA but applying the Functional approach..

Dim DataRng As String
DataRng = "2:" & WorksheetFunction.CountA(Range("B:B"))
BorderRng ="A1:M" &WorksheetFunction.CountA(Range("B:B"))

Rows(DataRng).RowHeight = 31.5

Range(BorderRng).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

'=====The code for geting Borders===== This works, however the approach is not VBA oriented so can someone also help me on this as well as getting an Entire Column into UPPERCASE?

This somehow does not work

UCase (Range("B2:B516").Value) Warm Regards
e4excel

Hello,

I would like to know if a specific MS Access file is yet open using Excel VBA.

Thanks in advance for your help

I am required to run macro in closed excel workbook using VBA.
pl help

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

hello,

how do i prevent an excel file being opened as read only if the file is
already opened by another user. this particular file is on the file server
and accessed by multiple users.

What I want is an alert if the file is already opened which says the file
cannot be opened as read only and click ok to close. the alert to inlcude the
username who has the file opened.

workbook sharing is not an option as it breaks my various vba macros

appreciate any help...

cheers

I use the following code at the begining of each macro that opens an Excel File. What I am looking for is to some how incorporate a procedure like this to check to see if the email that I want to open is already open or not.

NEXT, look at the 2nd set of code.

Dim oWB As Object
Dim PWB As String
Dim FSS As String


PWB = ("c:MyMenuFilesMyMenuiJngo.xls")
FSS = "MyMenuiJango.xls"

    'First I need to check to see if "MyMenuiJango.xls" is open or not
If FileAlreadyOpen("c:MyMenuFilesMyMenuiJango.xls") = True Then
    ActivateWorkbook (FSS)
    Application.Quit
    
End
Else


The following code is currently what I use to open my 3 email accounts. This sample is for only 1, which is my hotmail account.

Next, see below this code.

Sub OpenMyeMailNo1()
Dim oShell
Dim sUrl As String
 sUrl = "http://" & Sheets("Setup").Cells(8, 5).Value
    Set oShell = CreateObject("Wscript.Shell")
    oShell.Run (sUrl)
End Sub

This eMail code gets the name of the eMail and .com . . . EX: Hotmail.com from a sheet in the workbook named Setup from cell E8.

I know how to check to see if a file is already open or not, (don't want it opened 2X). BUT . . . How do I check to see if Hotmail or whatever email of the three I'm referring to is open or not.

I'm having odd results using the code cited in this article,
http://support.microsoft.com?kbid=184982, to determine is an Excel file is
already open.

The problem I'm having is when an excel file is marked read-only. (Everthing
works as expected otherwise.) In my testing, when the Excel file is marked as
read only, the code below ALWAYS (wether the file is open or not) returns
Err.Number=75, Err.Description=Path/File access error.

Unfortunately, I can't assume that the file is not read-only so I need to
figure this problem out. Any idea how I can determine if a read-only file is
already open or not?

I tried removing the "Write"s from the Open statement, but that doesn't help
in this situation. What need to know is if the file is open before I attempt
to access it, so that when I'm done I can close or open it depending on the
status before I started my processing.

Thanks for any feedback...

FYI, Here's the code:

Public Sub x()
Debug.print FileLocked("C:", "some.xls")
End sub

Function FileLocked(FilePath As String, FileName As String) As Boolean
On Error Resume Next
' Adapted from http://support.microsoft.com/?kbid=184982
' If the file is already opened by another process,
' and the specified type of access is not allowed,
' the Open operation fails and an error occurs.
Open FilePath & FileName For Binary Access Read Write Lock Read Write As
#1
Close #1

' If an error occurs, the document is currently open.
If Err.Number <> 0 Then
' Display the error number and description.
MsgBox "Error #" & str(Err.Number) & " - " & Err.Description
FileLocked = True
Err.Clear
End If
End Function

Hi all,

I'm looking for a bit of help with VBA which I'm relatively new to. What I know I've taught myself from helpful forums such as this. I have the following section of code


	VB:
	
 
If IsNumeric(ActiveCell.Value) = True And ActiveCell.Value > 100000 Then 
    SetCurrentDirectoryA prometheus & ActiveCell.Value 
    Application.FindFile 
    MsgBox "Please select a valid job number", vbExclamation + vbOKOnly, "Open Job" 
    Exit Sub 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which looks for a value in the selected cell and, if that value meets the citeria, presents the user with an open file dialogue. This works beautifully until the user choses to open an already open file. At this point Excel says ' this file is already open do you wish to open another copy'. Clicking yes opens a second copy and the macro proceeds but clicking no stops the macro in its tracks. What I would like is to change this code so that after clicking no the macro ends and presents a message box. Even better would be (though more complicated I suspect!) for the macro to test if the file is open first and then either open it and continue or continue using the already open version. Any hints?!

Thanks in advance for any help anyone can offer.

Archie

This routine is used to open another Excel File in a seperate instance of Excel.
I do not want the user to beable to click the macro button a second time and then open the workbook a second time.

Every thing works fine until I add the code as shown below.

'ADDED THIS - - - - - - - - - - - - - - TO TEST IF FILE ALREADY OPEN
'testFileFind is a variable with name of file
    'This line of code Goes to below FUNCTION - Should NOT
The "testFileFind" in this line of code is blank after going to Function. Earlier in the routine the "testFileFind" still holds the file name.
I think this is where the problem is.
'TO HERE - - - - - - - - - - - - - -

'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL.
Set oXL = CreateObject("Excel.Application")

'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.
 oXL.Visible = True
 
 Set oWB = oXL.Workbooks.Open("c:extrafilesmiscexcelLinkMenu.xls")

'ADDED THIS - - - - - - - - - - - - - -
End
Else
    MsgBox "File " & "testfilefind" & "is already open."
End If
'TO HERE - - - - - - - - - - - - - - -
When the requested file is already open, the routine should stop here with a message stating that the file is already
open.

If the file is not open, the file should be opened and then the routine should stop.

In both instances, the routine goes to my Function below and comes up with an error.

I don't understand why it goes to this function.

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
       'ADD THIS
       Case Else: Error iErr
   End Select

End Function
I sure hope that someone can understand what I am talking about.

Hi all

I have a proceedure that opens a certain file the problem is that when the
proceedure is run again it reopens the file where it loses some information,
is there a way to check to see if that file is already open?

Thanks in Advance

Jason

Hi everyone - - - I thought that I had this working completely, but the test to see if the "file is already open" isn't working.

Something is wrong with my example.

I have marked in the code where the problem is. Everything else works.
It's marked as such:

HERE IS MY PROBLEM - - - - - - - - - - - - - - - - - - - - - - - - - - -
Can someone please tell me what I'm doing wrong ?

Sub NewExcelWithWorkbook()
    Dim oXL    As Object    'This is needed to open a new instance of Excel.
    'Without it, the file is only opened as a new Window
    Dim OpenFileName    '<-this isn't used
    Dim testFileFind As String
    Dim oWB    As Workbook
    Dim cl     As Range
    'This reads the cell 1 column to the Left so the path & file name can be read
    Set cl = ActiveCell.Offset(0, -1)


    'The following tests for a blank cell and ends processing
    'It is needed because dir() function will not work with a blank.
    If Len(Trim(cl)) = 0 Then
        MsgBox "You have not entered a Path and File name."
        End
    End If

    'The following tests for the existance of the file
    testFileFind = Dir(cl)
    
    'If the file is not found there will be nothing in the variable and processing ends.
    If Len(testFileFind) = 0 Then
        MsgBox "Invalid selection." & Chr(13) & _
               "Filename " & cl.Value & " not found"
        End
    End If




'HERE IS MY PROBLEM - - - - - - - - - - - - - - - - - - - - - - - - - - -
        'THIS TESTS TO SEE IF THE FILE IS ALREADY OPEN OR NOT
    If FileAlreadyOpen("cl") = True Then
        MsgBox "File is already open"
    End
    Else
    End If






    'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL.
    Set oXL = CreateObject("Excel.Application")

    'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.
    oXL.Visible = True

    Set oWB = oXL.Workbooks.Open(cl)

End Sub
Please take note: The path and file name constantly change.

The following Function IS CALLED to check if the File Is Open - or Not

'It all works if the Path & File Name are hard coded. I have to have it working with changing Paths & File names.

Function FileAlreadyOpen(FullFileName As String) As Boolean
' returns True if FullFileName is currently in use by another process
' example: If FileAlreadyOpen("C:FolderNameFileName.xls") Then...
Dim f As Integer
    f = FreeFile
    On Error Resume Next
    Open FullFileName For Binary Access Read Write Lock Read Write As #f
    Close #f
    ' If an error occurs, the document is currently open.
    If Err.Number <> 0 Then
        FileAlreadyOpen = True
        Err.Clear
        'MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
    Else
        FileAlreadyOpen = False
    End If
    On Error GoTo 0
End Function


I have the following Code to Check if a File is already Open.... and perform a procedure

Basically if file is already open then perform procedure... If file is not already open open it and then run the procedure...

For some reason this part:

If IsFileOpen(sPath) Then

MsgBox "File is Open"
GoTo ResumeProcess: Does not work shen the file is already open... I get run time error 1004 stating the file is already open...

I was hoping I would not receive any errors if file is already open and then just resume normal activity...

Code:
Private Sub CommandButton2_Click()

Dim WbBook1 As Worksheet
Dim WbBook2 As Worksheet
Dim sPath As String
sPath = "Nymas01financeBudgetBudget 2008ExportsPLATFORM.xls"

If IsFileOpen(sPath) Then

MsgBox "File is Open"
GoTo ResumeProcess:

Else



Workbooks.Open (sPath)

End If


ResumeProcess:

Set WbBook1 = Workbooks("PLATFORM.xls").Worksheets("PLATFORM")
Set WbBook2 = Workbooks("License Fees_2008.xls").Worksheets("PLATFORM not in FRX")


WbBook2.Range("A2:A" & Rows.Count).Formula = "=IF(ISBLANK(GROUPLIST!A3)," & Chr(34) & Chr(34) &
",IF(COUNTIF(PLATFORM.xls!$B:$B,GROUPLIST!A3)>0," & Chr(34) & Chr(34) & ",GROUPLIST!A3))"
'Dim eSheet As Worksheet
'Set eSheet = Sheets("PLATFORM not in FRX")

'x = eSheet.Rows.Count

'eSheet.Range("A2").Formula = "=IF(ISBLANK(GROUPLIST!A3),"",IF(COUNTIF(PLATFORM.xls!$B:$B,GROUPLIST!A3)>0,"",GROUPLIST!A3))"
'eSheet.Range("A2:A" & x).FillDown

Workbooks("PLATFORM.xls").Close
End Sub
Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Any advice in this matter would be amazing!

Thanks in advance,

How to lock a file (wholly or partly)

Hi.
1)
I would like to lock my file.
Thus if someone opens my file, it needs password to access it.
Do you know how I can do this?

2)
This is a bit tricky.
This time I just wish to lock part of my file (eg some worksheets, some
columns/rows, some cells)
Before someone read the data in that part of my file, it needs password before
the data will show up. Otherwise they appear hidden (unreadable).
Do you know how I can do this?

Thank you

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

Hi,

I'm using the following code to run a task:

Sub Send_info()

Dim wbData As Workbook
Dim wbArchive As Workbook
Dim WS As Worksheet

Set wbData = ActiveWorkbook
Set wbArchive = Workbooks.Open("C:abc.xls")

For Each WS In wbData.Worksheets
    WS.Range("A:G").Copy wbArchive.Sheets(WS.Name).Range("A1")
Next WS

End Sub
Is there a way I can add to this so that if the file is already open it will just paste the data and ONLY if it is closed it will open the file?

Thanks in advance. :-)

Hi everyone. I have the below code that opens all files within a specific
folder. But if one of the files is already open (very likely to happen) I
get an error. Can I add some sort of if stmt that will ignore the file if
it is already open, so the code won't error out? Thank you in advance!
Steph

Dim sFolder As String
Dim wb As Workbook
Dim i As Long

With Application.FileSearch
.NewSearch
.LookIn = "ServerFolder1Folder2"
.SearchSubFolders = False
.filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(filename:=.FoundFiles(i))
Next i
Else
MsgBox "Folder " & sFolder & " contains no required files"
End If
End With

How to supress Enable Macro option while open a excel file. I would like to do it using VBA in Macro of excel 2007 + versions.

I have verified that ther is only one version of Personal.xls anywhere on the
C: drive and that it is in the xlstart folder. The macros are assigned to a
custom toolbar. I can run the macros from the menu just fine. But when I try
to run from the tool bar buttons I get the message that the "file is already
open". I'm not sure this is a Personal.xls problem or a toolbar problem.

Hopefully someone will have some ideas of how to fix this. Thanks, Irene

An excel spreadsheet links to another excel file. However Iwant to remove
this link completely since the file does not exist anymore. How do I remove
it?

I know that by going into the spread sheet and doing a find ! I get all the
cells that call to this external file.

If I delete them all (and there are lots) will I still get the prompt when
opening the spreadsheet that it can't find this source File?

How shall I best handle this situation? Can I simply remove the source link
somehow? I don't get that option (rather, open source, change source).

TIA, Brad

Hi All,

I have a tool called "TestPartner" which uses VBA to test the
applications.
My application opens an Excel file after submitting the data. Now,
using the VBA in TestPartner I have to save that file.

Following is the code written by me.
Dim myExc As Object
Dim myWb As Workbook

Set myExc = CreateObject("Excel.Application")
Set myWb = Excel.ActiveWorkbook

myWb.SaveAs ("Path")

but this is not working.
Please let me know how to save the Excel file which is already opened
using VBA.

Thanks,
Suraj

Good morning/afternoon/evening,
Working on a project and am trying to modify somebody elses' code, which just isn't cutting it for me.

Here's what I have, and what I'm trying to get done. Any help is appreciated.

My spreadsheet has a column with a list of items. Let's just say they're stock tickers.

Each ticker has to be loaded into the UI of a proprietary system here at work, a few changes are to be made to the UI (so far, I'm good). Then a report is generated. That report can take anywhere from 1-10 seconds to load from the time the ticker is entered, ALWAYS has a format "anrqc*.csv" (where the * is 1-5 characters), and always opens in a new excel workbook.

Okay, so what I need to do is
- Activate the file when it's opened, AND NOT proceed to the next ticker until I collect some info from the csv file and close it

I'm sure there's a way I can continuously collect window names until anrqc*.csv is encountered, then activate it, but I'm not sure how to do that, and further, without proceeding to the next symbol until I close that csv file.

Any ideas? Many thanks!

OK, I'm throwing in the towel. Two days is more than enough time to spend on this before asking for help.

This subroutine, copied from another website and embellished somewhat by me, is designed to allow the user to choose the file to open via the open file dialog box. That part works great so long as the file is not already open. If the file is already open though, I need to be able to activate or otherwise reference it to copy a sheet from it to another already open (the one containing this code) workbook. It bombs under the 'Open File comment in the code below. As it is now written, it throws a 'subscript out of range' error when it executes the .Activate code. I think this issue arises because the string variable contains the full path/filename instead of the workbook name only. I also used the FileName variable as Workbook to no avail. That creates a whole new set of problems. Any helpful hints or pearls of wisdom are greatly appreciated.


	VB:
	
 OpenSingleFile() 
     'Open an Excel workbook
     
     
    Dim Filter As String, Title As String, FileName As String 
    Dim FilterIndex As Integer 
     
     
     ' File filters
    Filter = "Excel Files(*.xls,*xlxs),*.xls,*xlxs" 
     
     
     ' Default Filter to *.*
    FilterIndex = 3 
     
     
     ' Set Dialog Caption
    Title = "Select a File to Open" 
     
    FileName = Application.GetOpenFilename(Filter, FilterIndex, Title 
     
     ' Exit on Cancel
    If FileName = "False" Then 
        MsgBox "No file was selected.", , "Select File" 
        Exit Sub 
    End If 
     
     
     ' Open File
    If Not IsFileOpen(FileName) Then 
        Workbooks.Open FileName 
         '*** It will be the active workbook here b/c it was opened
    Else 
        Message = "The file " & FileName & " is already open." 
        vbResponse = MsgBox(Message, vbOKCancel, "Select file") 
         '*** I need to activate it here if already open
        Workbooks(FileName).Activate ' doesn't work - subscript out or range error
        MsgBox "The active workbook is " & ActiveWorkbook.Name 
    End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
End Sub

How to view the Resource Plan view

I'm using the web access.
how do I use the Resource Plan option? that allows Managers  to create resource plans for their projects.
I was able to build up a view , but I don't know where I can see that view I build 
thanks

Does anyone have any suggestions on how to load .xla file automatically when
starting Excel 2003? Whenever I open a file, it opens a dialogue box to
request editing links, and find some .xla file's, which cannot be found, but
the xla file is located under this specific directory. Therefore, I have to
activate manually everytime for opening this file.
Does anyone have any suggestions on how to load .xla file automatically?
Thanks in advance for any suggestions
Eric

Hello,

I need to search an Excel file in my computer which has a specific VBA
subroutines I wrote. I tried using Windows file search feature to search the
text string. It does not work. And it is almost impossible to open every
Excel file to seach for the text string in the VBE.

Any suggestions on how to conduct a file search by looking for a text string
in VBA code?

Thank you in advance.

Hi,

IS there a way on how to create second criteria if the search value has a duplicate.
I would like the second column to be the next look up if there is a duplicate. Should i create a text box or an input box to search the value in the second column to find the rows of the first and second look ups?

Please see the attached file.
Thanks,

klowjet