Code To Check If File Is Accessible
Apr 2, 2007
i want to improve some of my application by testing if a folder is accessible to the user... i've got some code to check if a file is already is use which is useful... but before that we have folders in work that are only accessible to certain users for reporting and if the person doesn't have access to it within a macro it Errors out... i'd like some kind of macro to display a message box like windows does... Directory is not accessible, Access Violation... it has to check directly for the Accessible part and not just an error...
View 2 Replies
ADVERTISEMENT
Oct 8, 2007
I tried opening an excel 2003 file in an xp environment and it says that the file is not accesible? how can i open the file?
View 3 Replies
View Related
May 13, 2014
Is there a way to protect the conditional formatting of accessible cells in a protected sheet.
Detail: I have a protected excel sheet. However some of the cells are made accessible so others (my secretary) can fill them up. The accessible cells have conditional formatting which become nonfunctional during pasting from other sources if "special paste" is not used.
View 2 Replies
View Related
Sep 28, 2006
I have some VBA which currently creates a directory when it produces an error. the code is as follows
Function create_year()
On Error Goto makenew
ChDir "I:Reports" & Year( Date)
Goto skipmakenew
makenew:
MkDir "I:Reports" & Year(Date)
skipmakenew:
End Function
What I would like to do is have an IF statement which checks if the directory exists.
View 9 Replies
View Related
Jul 11, 2012
I have an excel file with multiple sheets. I want my HR team to update that file, but one user shouldnt see the entries of other users. I want to make a consolidated file out of three files. This excel template is for salary input.
View 3 Replies
View Related
Jan 5, 2014
I have a protected worksheet that is shared between 2 departments. Is there a way of making the hide/unhide function available without unprotecting the worksheet.
Macro would be the most obvious way but believe you can not share a workbook when a macro is incorporated.
View 2 Replies
View Related
Apr 16, 2002
I've got a couple of user-defined functions that I coded in via one workbook (wkb A), but would like these functions to be accessible to any workbook (wkb B...Z). How is this done so that when I start a new blank wkb I can use these user-defined functions there as well?
View 9 Replies
View Related
Nov 18, 2009
In the code below, I am trying to add code that will verify that the file to be opened is actually there. If it's not, I want a message box to tell the user that the file is missing or not there. My efforts have XXXXXXXXXXXXXXX above and below my inserted code. Sorry, but I'm lost and need a method of advising the user via a message.
View 3 Replies
View Related
Oct 4, 2006
How do I check to see if a particular file exists using Visual Basic
View 2 Replies
View Related
Jun 4, 2012
Is it possible to write vba code that will generate a text file with ALL changes that were made to an excel file. Ex. If Cell A17 = "Monday, June 4, 2012" and a user updates Cell A17 to "N/A", I would like to know what the value was before and after the udpate was made.
View 8 Replies
View Related
Apr 9, 2014
I have a spreadsheet where I want to require certain fields to be completed then I want to have that file auto emailed. I have learned that I do need to have the file saved before sending otherwise the data will not appear in the email, so with this I want to have the file temporarily saved emailed then the temp file deleted.
Here is the code I have so far but it errors on the blue text, I did change the TempFileName from = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") to = [C16] & "_" & [B6] & "_" & [D6]
Private Sub CommandButton1_Click()
If Range("B6").Value = "" Or _
Range("d6").Value = "" Or _
Range("f6").Value = "" Or _
Range("E9").Value = "" Or _
[Code] ......
View 1 Replies
View Related
Jan 29, 2014
I have a file that I save with a new version number each time I make major changes. The file name currently is: "Telephony Equipment Inventory v26 (Summary).xlsm". The "26" is the variable number. give me the vba code to ensure I open the file with the highest version number?
View 6 Replies
View Related
May 26, 2014
I want to create something like this: macro checking if file aaa.csv isn't open, if it is macro should stop and show message box "Close aaa.csv", and if file isn't open it should continue to work.
The file aaa.csv is stored in the same folder as workbook with my macro.
View 3 Replies
View Related
Nov 8, 2007
I have an excel spreadsheet used to populate word documents based on a template file that is loaded like this....
View 9 Replies
View Related
Nov 7, 2008
It checks to see if DataImport2, 3 and 4 exist and then executes code.
Currently DataImport4 does not exist so it should not execute anycode but for some reason the code is still trying to execute it and select Sheets("DataImport4").Select
View 5 Replies
View Related
Oct 2, 2012
How can I check if a file exists with VBA?
View 3 Replies
View Related
Aug 1, 2013
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True
[Code].....
View 1 Replies
View Related
Jan 22, 2010
I have a userform with two check boxes and four textbox... and i have a command button... i want that when i check checkbox1, the value in textbox1 and textbox2 will appear on cell A1 and A2... and when i check checkbox2, the value in textbox3 and textbox4 will appear in cell A1 and cell A2.
View 9 Replies
View Related
Oct 28, 2009
i have a spreadsheet with the following worksheets:
"Data Entry" (sheet 1)
"Menu" (sheet 2)
"Username" (sheet 3)
In username the range a2:b5 contains the following:
a2= John b2=Write
a3=Scott b3=Readonly
where John or Scott are usernames in Environ("Username")
Is it possible to write a macro that does the following:
1. When the file is opened it looks at the network username and checks again the Username range in sheet3, if it is Write then the user can change cells in Data Entry and save the workbook, if Readonly then they can only view and not save, and if not there at all, then the worksheet closes.
2. When the file is opened write a log in a hidden sheet callled "Log" of the time opened and the username
3. Is it possible to do the same but making a copy of all cells changed ??
View 3 Replies
View Related
Jan 3, 2009
I am using below mentioned Code for checking if certain file exist on Path.
Its working on my home PC (XP SP2 Excel 2003).
BUT its not working on my office PC (XP SP2 Excel 2003).
Can somebody tell me why its not working in my office PC ( or if its missing something, then from where can i download it.)
Sub CheckFiles()
Const strFolder = "C:Documents and SettingskreshnrDesktopTest"
Dim fso, msg, i
Dim rngData As Range
Set fso = CreateObject("Scripting.FileSystemObject")
Set rngData = Sheets("Sheet1").Range("A1")
With rngData
Do While .Offset(i, 0).Value ""
If (fso.FileExists(strFolder & .Offset(i, 0).Value & ". ")) Then
.Offset(i, 2).Value = "Yes"
Else
.Offset(i, 2).Value = "No"
End If
i = i + 1
Loop
End With
End Sub
View 9 Replies
View Related
May 2, 2006
I can use a FileDialog to have a file selected. But I still need to check if it really exists before continuing. How to do?
(I know there is a function FileExists but do not know how to use it with FileDialog if this is the way to do it)
View 9 Replies
View Related
Dec 19, 2006
I have a form in Access with a button that opens an Excel file. I think I've seen vba code to tell if a specific Excel file is open already. What I'm looking for is a way to tell if an Excel file is already open when you don't know the filename of the Excel file. Is that possible? Or at the very least is there a way to be able to tell if Excel is already open?
View 4 Replies
View Related
Jun 10, 2014
Obviously there is the Remove Duplicates option but I want something a bit different plus this option won't be available as the workbook is on complete lockdown.
There will always only be 214 rows to check in column B and the data cannot be sorted or it will mess a lot of things up!
Just wondering if there's some code that can check for duplicate entries because for the workbook to work, each row must have a different entry.
When the user selects this sheet a message box pops up telling them they can't have two or more entries the same but I feel this isn't enough as there's nothing actually stopping them doing this.
View 2 Replies
View Related
Nov 2, 2011
I use a userform to make sales data entries into a database. The userform contains textboxes for:
Invoice No.Invoice DateGrossVatNet The Invoice No. is the first entry box on the userform and it is important that users enter the correct format (which is alphanumeric).
It is also important that a checking is carried out to ensure that the Invoice Number being entered has not already been used.
I use the following code to ensure that the numbers are entered in the desired format:
Code:
Private Sub txtInv_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtInv.Value = vbNullString Then Exit Sub
If (Not UCase(Me.txtInv.Value) Like "ST###") And (Not UCase(Me.txtInv.Value) Like "ST####") Then
MsgBox "Non Valid Invoice Number.”
Cancel = True
End If
End Sub
And this code is pasted lower down the VB editor window. And it seems to be working fine!
For the checking to ensure that the invoice number being typed into the textbox has not already been used, I use the following two codes combined together:
a) to check for invoice No.:
Code:
check for Invoice Number
If Trim(Me.txtInv.Value) = "" Then
Me.txtInv.SetFocus
MsgBox "Please Enter Invoice No."
Exit Sub
End If
b) And to it I add the following additional code for CHECKING that the no. does not already exist in the database (the invoice nos are located in Column A of the database):
Code:
Dim x
X= Application.Match(Me.txtInv.Value, Columns(1),0)
If Not IsError(x) Then
MsgBox Me.txtInv.Value & "Invoice Number Is Already Used"
Me.txtInv.SetFocus
Cancel = True
Exit Sub
End If
My problem with the second set of codes in (a) & (b) (for the Checking of already existing No.) is that the code allows the user to key in the Invoice No. and go through filling in the other textboxes. It is only after clicking the ‘Add’ button that a msgbox comes up to indicate that the ‘invoice no. is already used’.
I need a tweaking of the codes so that the moment an invoice no. that has already been used is typed into the textbox and the TAB button pressed, a msgbox will show up at that stage to indicate that the ‘Invoice no. is already used’. This will reduce time and avoid filling in the other textboxes and clicking the ‘Add’ button before the checking is done.
View 3 Replies
View Related
Apr 12, 2014
I need a vba code to run a macro when a specific word entered in a cell. i.e. when a word "Duplicate" entered in a cell O2, the macro run automatically.
View 1 Replies
View Related
Oct 27, 2009
I have some code that will Kill a DB if it already exists, but I want to check if it exists and warn the user before this happens.
I am not that familiar with ADO, so I was fumbling through the Help topics trying to learn about ADO type names, etc. b/c i thought I could use something like:
If TypeName(MyDB) = "ADODB" Then . . .
but even if that ran, the argument in parentheses would be a string and not the actual DB object, so I am at a loss.
View 9 Replies
View Related
Sep 9, 2006
I want Excel VBA to check for named worksheets. Based on the return, I will have VBA either (1) delete the named worksheets and replace them or (2) refresh the pivot tables on the named worksheet. how to make VBA check for the presence of the named worksheets. My efforts are below and comments at the end of the Sub detail what I want to have happen.
Sub EnterProgram()
Dim Current_P As String, New_P As String
Current_P = Range("data!C2")
If Current_P = "" Then
New_P = InputBox("Which program?")
Do While New_P = ""
Prog = InputBox("Try again... Which program?")
Loop
Range("data!C2") = New_P
ElseIf Current_P = New_P Then
If MsgBox("Use the current program (yes or no)?", vbYesNo) = vbYes Then
Range("data!C2") = New_P
End If........................................
View 2 Replies
View Related
Sep 13, 2006
I have a macro that creates a spreadsheet on a weekly basis and have been using it for quite sometime. Due to the large number of spreadsheets I would like to incorporate some code that would create a folder every month. I would assume that the code would need to include a check routine to see if a folder for that month exsists.
View 3 Replies
View Related
Jul 22, 2007
I want to create a user form in excel that auto calcs the discount depending on customer type and no. of books purchased. There are two types of customers one individual and the other schools, library etc. The discounts are below
More than 50 books - 30%
25 - 49 - 20%
15 - 24 - 15%
5 - 14 - 10%
<5 - 5%
Individual
>25 - 25%
5 - 24 - 15%
<5 - 0%
I have created a form with option and text boxes to capture data but am really struggling with this as in my head I understand a set calculation will need to take place once relevant conditions are met but am really struggling to put it into code (yes I am new to this).
View 2 Replies
View Related
Jul 5, 2014
how I could perform a simple file path check using excel.
For example:
File Path
Exists
C:ImagesMatrix.png
Yes
C:ImagesCubic.png
No
View 5 Replies
View Related
