Time Limitations In File
Aug 22, 2006
We have an Excel file that is located on the network and is accessed by numerous individuals. Should someone need access when another user has the file open, we get a message that the file is currently in use and it also lists the user id of that person. The issue is that a particular user will open the file and keep it on their desktop for hours at a time. Is there a way to limit the amount of time that user can have the file open? Is there something within Excel that I can turn on that should the file not be modified for a particular amount of time that Excel saves the file and closes it down?
View 4 Replies
ADVERTISEMENT
Nov 25, 2009
I'm currently using a Lookup(2,1) that looks up an unopened excel sheet stored locally.
When I enter the formula I always get a #NA.
What I was wondering was if its a limitation of the lookup funcation, or my PC not having enough resources.
The linked XL sheet is ~10mb, and has over 8000 rows.
Below is my current forumla that results in a #NA
=LOOKUP(2,1/('[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$B$1:$B$8500&":"&'[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$E$1:$E$8500&":"&'[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$M$1:$M$8500&"Team Leader"&":James Kinsey"&":""Privilege"),'[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$Q$1:$Q$8500)
View 12 Replies
View Related
Oct 12, 2006
is there a combo boxes limitation in a certain worksheets or workbook.?
if there is then how many? coz i got a problem , i have to make around 2000 combo boxes, if i save it , i got no problem , but when i closed it and then reopen , the problem occurs..
View 4 Replies
View Related
Mar 27, 2014
I recently bought a new Dell XPS 10 tablet with a keyboard. When I copied my spreadsheets from my old notebook, none of the countifs or countif functions are working, all popping out a zero. It work completely fine on my old notebook on which I have Excel 2010 installed. On the tablet I have the pre-installed Home & Student 2013 RT. Could this version have limitations to my functions? What can I do to get my functions working again?
View 4 Replies
View Related
Nov 19, 2013
I got this wonderful, beautiful spreadsheet/template i'm using now for my small business, and well, i've got it all tweaked and tuned so it looks beautiful on my screen (100% view in excel). I can look at it, and they way it looks, is the way it prints whether i opt for a PDF or (EEEEEK!) paper. I've sent this report and template i've put together with Mrexcel's support, and it's all primp and pretty and the person i send the spreadsheet to for review? he's looking at it at the same 100%, and lines are cut off when multiple rows are merged that have multiple sentences typed (technically reference to) in. he's tried to print both pdf and paper from his computer and had no issue. it comes out perfect. It only looks visually cut off. we're trying to avoid a second transmission back to me to reformat say something needs to be omitted. QA if you will.
my question: Is this some sort of issue relative to our screen resolution? is there a way i can optimally get what he is seeing when he views it electronically to be what i'm seeing electronically?
View 2 Replies
View Related
Jan 28, 2007
I have a macro that copys a worksheet a user-inputted number of times but throws an error after adding Sheet #62. The line it hangs on is:
Sheets(myTemplate).Copy After:=Sheets(i - 1)
Does anyone know if this is a problem with my code, a memory issue, or the workbook I'm using to copy? In the past when I've copied a lot of sheets in a workbook (to the same workbook), Excel sometimes won't let me add anymore but if I copy all the sheets to a new workbook it will sometimes let me add more sheets. I figured it was a problem with how Excel counts sheets.
Sub CopySheets()
myTemplate = ActiveSheet.Name
'First, jump through the validation hoops
Dim AddSheetQuestion As Variant
'Define the application input box question
showAddSheetQuestion:
AddSheetQuestion = Application.InputBox("Please enter the number of sheets you want to add," & vbCrLf & _
"or click the Cancel button to cancel the addition:", _
"How many sheets do you want to add?")
'Cancel or the X was clicked
If AddSheetQuestion = False Then..................................
View 9 Replies
View Related
Jun 15, 2007
I am writing a macro that scrapes information from other workbooks. I wanted to add a hyperlink on each line to the workbook that row's information came from. I tried both the VBA method of adding a hyperlink as well as the formula method. When I click on the link that was created, windows explorer opens up to a folder rather than opening the workbook. I have a feeling I am hitting some folder level limitation. The link is to a network share and here is an example of what it looks like:
\MYCOMPANY.CORPFILESCBOSEALDRIVEDeptROPSPT Billing2007 AdjustmentsMCBDApproved#01305.20075.25.07 - C - NAME.xls
View 2 Replies
View Related
Jul 20, 2013
I am using excel 2002 and I have found that when the workbook is shared and getting 2 or more users to open the workbook around the same time brings up a read only type prompt box for the 2nd ( and 3rd) user when the book hasn't fully opened for the 1st user/is still fully opening. Is this a limitation within excel or is there a way around this for example via vb code.
I know the more data a workbook has can cause it open a lot slower but is this the same for shared mode.
View 5 Replies
View Related
Dec 29, 2011
I'm trying to do a nested formula to automatically assign a region number based on what the County is. We have 88 counties we deal with, split into 7 regions. What is the best way to go about setting up a formula to do this, so I don't have to do it manually? I believe the last time I tried doing something like this, I received an error saying that Excel didn't support a formula as big as what I was writing.
View 9 Replies
View Related
Jun 14, 2007
I am trying to use the validation feature to calculate a check digit and the formula is too long to put in validation. I rewrote the formula to be shorter using math features instead of text, but apparently you can't use arrays in validation. Because of certain distribution constraints, using VBA is not possible so a UDF is out. Can either of these be shortened in anyway? I am not sure if it is even technically possible to solve this problem with these constraints.
Here is the "human readable" explanation of formula:
(Accept only 10 digit numeric value.)
Example Number: 123456789-3
Digits 1,3,5,7,9 are multiplied by 2 (weighted 5)
So 1,2,3,4,5,6,7,8,9 becomes 2,2,6,4,10,6,14,8,18Weighted 5's multiplied values then have their individual digits added (ex:12=1+2=3)
So 2,2,6,4,10,6,14,8,18 becomes 2,2,6,4,1,6,5,8,9
Weighted 5's sum is added to the sum of the Nonweighted 4 digits.
So 2,2,6,4,1,6,5,8,9,3 becomes 43
Add 24 to sum.
43+24 = 67
Checksum = (10-(67 mod 10)) mod 10
(Sum round up to 10 minus sum)
Method 1 ...
View 9 Replies
View Related
Feb 9, 2007
I think Match has trouble finding a value thats within a cell containing a lot of words.
Heres the example. I'm trying to find the word "Hello world" in cell B1, in Column A.
Column A
1 Lots of Text
2 Text Text. Hello world. text text
3 Text Text
Cell B1
Hello world
Column B2
Match("*"&B1&"*",A:A,0)
When theres only 1 sentence in Cell A2, the function works and returns 2.
However, when Cell A2 contains a few paragraphs the function returns #NA.
I don't necessarily need to use Match, if this is indeed a limitation does anyone of suggestions to work around, other than vlookup?
View 14 Replies
View Related
Feb 28, 2007
Excel 2003. When the Vlookup List becomes impractably long to select data from the cell dropdown. Is there an alternative prefered method to entering data from the long list?
View 7 Replies
View Related
Mar 16, 2007
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
View 9 Replies
View Related
Apr 19, 2011
I have a workbook that retrieves data from a file from a URL address upon opening. Actually, I am saving the URL file to a local drive and then updating my workbook with this data. The problem is that the job that updates the URL file sometimes fails. I want to notify the user of my workbook that the data has not been update recently. I am trying to use the built in document properties (Last Save Time) but I cannot get it to work except for the workbook that has the macros in it. Is there a way to find the last save time of a workbook (from a URL address) seperate from the workbook that has the marco? Below is my current code that errors out at:
dp = Application.Workbooks("DockReportExport.xls").BuiltinDocumentProperties("Last Save Time") with and runtime error.
Sub FTP()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Open file from URL addres to check last save time
Workbooks.Open Filename:="http://172.16.1.94/Files/Operations/...portExport.xls"
'Check if data on server has been updated in the last 15 minutes
[Code] ........
View 5 Replies
View Related
Feb 25, 2009
How do I add the system time to a file name
My
ChDir "S:ProductionProduction Request Forms"
ActiveWorkbook.SaveAs Filename:= _
"S:ProductionProduction Request Forms" & SaveWorkbook & "_Request" & Time & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
View 9 Replies
View Related
Oct 11, 2013
I have a file that sits open all the time, and performs some refresh functions every thirty minutes. I need the file to save a copy of the tab as a CSV file at a given time interval. The code below is almost there, just need to work with the time interval part. The way it should work is to open the csv, copy / paste the active sheet; then close the csv; leaving the original excel file open. I can run it, and it works, but the time interval is not triggering.
I can get the time interval to work by itself, and the save csv part to work by itself also; I need them to work together.
VB:
Sub test()
Application.OnTime Now + TimeSerial(0, 1, 0), "test"
Dim OutputFile As Workbook, InputFile As Workbook
Dim sDD As Worksheet
[Code].....
View 2 Replies
View Related
Oct 21, 2008
I need a Macro to run when a file is opened through a macro on another workbook. The code I have for the workbook that open's the file is:
PHP
Sub opensaveclose()
Application.Workbooks.Open ("C:...testfile_revised.xls")
Sheets("Call Report").Select
End Sub
Private Sub runmacro()
Application.Run ("RefreshStats")
End Sub
View 6 Replies
View Related
Dec 20, 2008
I'm trying to automate/keep records of my day by setting up a spreadsheet which : 1) Plays a wav music file at a schedule time as a reminder and 2) at the same time calls up a form where I can enter the results of the activity then 3) saves the results to a sheet where consecutive daily records can be recorded and graphed.
Example
At 5am each day I want a "Motivation.wav" music file to open and play and a form appear where I enter in the number of sit ups, push-ups I get done, which I enter in the form and it gets saved to a sheet where I can graph my progress based on the results recorded.
View 12 Replies
View Related
Mar 6, 2007
What i want is for my user to click the save button linked to a macro that saves my file as Order0000. If there is a file already called this i want it to change the name to Order00001 and so on for Order00002.
Is this possible and if so what VBA code
View 9 Replies
View Related
Apr 24, 2008
Is it possible that each time I save a file it saves the filename along with date and time as the version of the file in a specific path
View 9 Replies
View Related
Oct 13, 2009
i've created a spreadsheet that will take in scanned files (from a barcode), log into our system, update the files based on certain criteria, then take information from the file and place it in another worksheet for inventory purposes.
what i'm asking is... is there a way i can have excel drop to the next row each time i scan in each file?
i've noticed the output of the scanner is formatted like this: +000000
i've tried using the sendkeys method with the plus sign, but it always moves down THEN enters in the numbers. i'm not sure if there is a way with the Len function... or some variation of that.
i'm also thinking if i could maybe find the .dll for the scanner, maybe i could set up a reference to it, and have it do something like "afterbarcodescan.activecell.offset(1,0).activate".
View 9 Replies
View Related
Mar 1, 2007
I want to open each file in a folder, one at a time, and save each file with a new name (based on cells in the worksheet) to a different folder. This is what I have so far:
Sub aaa()
Dim i As Long, files As String, data As String
Dim fName As String
ChDir _
"C:Files1"
Cells.Select
Range("A1").Activate
Selection.ClearContents
Application. ScreenUpdating = False
i = 0
files = Dir("*.txt")
Do While files <> ""
Open files For Input As 1
This code doesn't open any files unless there is a value already in the cells that fName uses.
View 6 Replies
View Related
Oct 17, 2007
i know there is information on this ontime method, i haev read through it, but since i am working with reuters running live in the background they just dont work.
Anyway, on a normal excel sheet, I have made the automated macros run on a timer, which is great. BUT If someone switches off my pc, its not going to be so great because i dont think the macro would run without excel being open.
QUESTION:
How can i make my macro run every morning at 8am? Assuming the worksheet is closed, even excel is closed. i am sure the answer is starring me in the face, i am just getting boerd of looking at it! in the Thisworkbook:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "thismodule", , False
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("20:00:00"), "thismodule"
End Sub
and in the Module:
Sub thismodule()
dTime = Now + TimeValue("08:00:00")
Application.OnTime dTime, "thismodule"
View 3 Replies
View Related
Apr 12, 2008
A coworker of mine will keep a shared file open for hours at a time, not doing anything to it, just keeping open, meaning no one else can edit it as required. Is there a way to set a time limit for that file to remain open before it saves and closes the file? The file shouldn't need to be open more than 30 minutes at a time for any individual. If it's required to stay open longer for certain instances, is there a way to extend the time as well? I can't find anything even remotely relating to time limits on Excel files. Is there coding or an app I need to apply to accomplish this? Is there an option I haven't seen in the program already?
View 3 Replies
View Related
Jan 16, 2013
I have a file that I want to clear certain cells and all option buttons when first opened. I then want the user to be able to save and close the file with the data they have entered. When they go to reopen the file I want the data they entered to still be there. I want the user to get the blank file off the company's server and then save it to there machine when it has been completed and when they go back to this file the information is still entered. I have attached the file.
Request Form 2012 Draft4.xlsm‎
View 1 Replies
View Related
Aug 1, 2013
I was wondering if it's possible for several users to be working in an excel file at the same time? I know that 1 person can be working in a file & other users can look at it in Read-only, but is there any way for more than 1 user to be WORKING in it at a time?
View 3 Replies
View Related
Nov 11, 2007
I have the following code,
View 11 Replies
View Related
Dec 23, 2008
I want to read in a space delimited file and to put each line in one cell starting with cell B2 (I will take care of splitting it into columns later). However, rather than reading one line at a time, it reads in the entire txt file at the first pass......
View 5 Replies
View Related
Jun 1, 2012
I have a file that will successfully save every 10 seconds.
I want this file's name to change based on what time of the day it is
for example from 630-230pm i want the file to include "day"
230-1030pm include "swing"
this is the code i have
Code:
Dim newFile As String, fName As String
On Error GoTo backup
If Hour(Now()) > 14 And Hour(Now()) < 23 Then
newFile = Format$(Date, "mm-dd-yyyy") & " " & "swing"
Else
newFile = Format$(Date, "mm-dd-yyyy") & " " & "day"
backup: newFile = Format$(Date, "mm-dd-yyyy") & " " & "dayerror"
End If
It currently returns the backup....
View 2 Replies
View Related
Jun 17, 2014
I have a piece of code that runs and creates an output Excel sheet that currently has the run date as a suffix for the automatically generated file name. However, I now sometimes have to run this report multiple times in one day, which currently means overwriting the previously run spreadsheet (whereas I would like to keep both versions of the ruin output).
Is there a way that I can create some kind of counter for the number of times the report is run and add that as a suffix for the file name? At the moment the only way I can think to do this is to set up code that looks at the file name containing the date field and adds 1 to every number that is added as a suffix, but this doesn't seem very efficient...
View 2 Replies
View Related
