VBA Save File On Time Interval?

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


ADVERTISEMENT

Filter Column Data By Time Interval And Count Number Of Records For Each Interval?

Apr 28, 2014

I have a column of "timestamp" data (in mins) which i want to filter by a given time interval, say 10 mins. Then i want to count the number of records for each time interval and output the data to a sheet. how can i achieve this? through vba?

I attached a pic illustrating what i want to accomplish.

QQ截图20140429104406.png

View 1 Replies View Related

Cannot Get (Last Save Time) From Other File

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

How Do I Save A File With Date And Time

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

Auto Save File Name Based On Time Of Day

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

Save File Name With Date/time Stamp

Mar 13, 2007

I've got a simple save macro below and was looking for a way that when this macro saves the file can it add the system date and time to the name.

'ChDir "C:
ashfinch"
' ActiveWorkbook.SaveAs Filename:="C:
ashfinchNFC-ORDER.XLS", FileFormat:= _
' xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
' , CreateBackup:=False

View 3 Replies View Related

Excel File Takes Long Time To Save

Jan 20, 2014

[URL]

Excel takes about 10 minutes in the saving process. When I say 10 minutes, I mean, the excel screen freezes (says not responding) for about 10 minutes, then it actually saves at the very end in the normal time any other file would take as you watch the progress bar go forward.

I know many of the common answers and have tried. reducing the calculation time (which in turn reduces the saving time).

But in my circumstance, the calculation takes a very reasonable amount of time, and you see the progress % going forward.

- I would say I have about 2000 rows, and 15 columns.
- They have sumifs formulas.
- They link to a different workbook.
- The workbook I am working on saves to the network
- the source of my sumifs are also in the same folder on the network
- the recalculation takes about 10 seconds at most
- i have turned off recalculate before saving, it is all on manual calcs

- when i hit save, there are no calcs being performed
- there are no macros in the workbook
- there are only about 2 names in the name manager
- then it freezes for about 10 minutes.
- then the progress bar starts moving then it saves.

What is it doing in those 10 minutes?

1 more item to note, when I break the links to the workbook and thereby removing the sumifs formulas, its a snap.

Why does the existence of the sumifs extend saving time? I would completely understand if it elongated calculation time, but if calculation is off, then why does it even worry about it when saving?

View 4 Replies View Related

Large File Taking Much Time To Save Or Open?

Mar 29, 2014

i always receive 200 mb file. which i has, around 10 sheets , with pivot tables and formulas.

i have to open this file update refresh and save as this file, which i am doing via vba.

however the challenge for me here is though i set xl calculation to manual at the begining and xl calculation to automatic at the end. Though i used this code, its taking close to 5 mins to complete this via Vba.

how or what is the alternate way to reduce.

View 6 Replies View Related

Establishing A Time Interval

Sep 5, 2008

I have a list of times, and I need to work out a way to establish what time interval it applies to, using a function. In production, this will be used over hundreds of entries at a time, but for the sake of example I'll cut it down to 15 times:

17:28:35
16:11:14
17:08:20
19:21:51
15:29:01
15:31:45
14:32:24
13:39:51
15:44:41
16:52:38
20:17:37
13:26:05
15:45:01
20:12:24
12:53:26

Now, there are 27 different time intervals there times can fall into:

1: 6:00 - 6:30
2: 6:30 - 7:00
3: 7:00 - 7:30
4: 7:30 - 8:00
5: 8:00 - 8:30
6: 8:30 - 9:00
7: 9:00 - 9:30
8: 9:30 - 10:00
9: 10:00 - 10:30..............

So, what I'm looking for is a formula that will match up the time to the interval. For example, it would look at 16:52:38 and output that it falls within interval 20.

View 5 Replies View Related

Large File Takes Long Time To Open/Save

May 19, 2008

I have a large Excel 2007 file, around 60.000KB. 54.000KB are due to one of the worksheets where I have 8760rows x 160columns with data. The calculation time is not a problem, it is very fast, it only takes 2/3 seconds. The problem is when I open or save the file, it takes around 2 minutes... it is not too much, but it becomes too long when one has to open and save it several times. It there any trick to decrease the time when openning or saving an excel file??

View 3 Replies View Related

Getting Total Values Within Time Interval

Mar 14, 2014

I need to get the total values within a criteria. Please see attached sample file.

View 5 Replies View Related

How To Re-divide The Data At The Same Time Interval

Apr 18, 2009

I have 2 columns of data (value and time):

for example:

15 4/2/08 13:00
4 4/2/08 19:00
7 4/5/08 12:00
13 4/9/08 3:00

They are continuous data. so I want to divid the value into hourly data as follows.

15 4/2/08 13:00
? 4/2/08 14:00
? 4/2/08 15:00
. .
. .
? 4/9/08 2:00
13 4/9/09 3:00

View 9 Replies View Related

Determine Hours Per Time Interval?

Nov 2, 2013

Im trying to create a spreadshet to determin hrs per time interval

i.e

06:00 - 14:00
14:00 - 22:00
22:00 - 06:00

so a start time of 06:00 and finish time of 14:00 would show 8hrs in first interval and 0 in the other 2

and a start time of 10:00 and a finish time of 18:00 would show 4hrs in first interval and 6 in second and 0 in last

ive currently got start time in A1 , finish time in B1 and want hours for interval 1 in D1 , interval 2 in E1 and interval 3 in F1

View 2 Replies View Related

Changing Date/time To An Interval

Aug 29, 2008

I have a column that shows the date and time and it looks like this:
8/1/2008 6:36 AM
8/1/2008 11:15 PM
8/1/2008 8:01 PM
8/1/2008 3:12 AM

I want to convert it to show just the time but I want it to be in 30 minute increaments. So in the example above, I'd want to see this:
06:30
23:00
20:00
03:00

View 9 Replies View Related

To 'round' A Time To The Next Nearest 15min Interval

Jun 23, 2006

Is it possible to 'round' a time to the next nearest 15min interval?

As an example in cell A1 I have a value that returns 2:07 PM (its formated
as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min
interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would
want to show 5:45 PM in B1 etc etc

View 13 Replies View Related

Fill In Date And Time Where Missing On 3 Minutes Interval

Jul 20, 2012

I am having trouble getting a VBA code to do the following:

2012/05/01 00:00:00
2012/05/01 00:03:00
2012/05/01 00:06:00
2012/05/01 00:15:00
2012/05/01 00:18:00

From this above to this below

2012/05/01 00:00:00
2012/05/01 00:03:00
2012/05/01 00:06:00
2012/05/01 00:09:00
2012/05/01 00:12:00
2012/05/01 00:15:00
2012/05/01 00:18:00

There are data entries next to these time stamps. I am able to create just the time stamps starting at the beginning and ending at the end of the month but I need it to fill in missing entries in a data set. The code below works but only some of the time. I have tried a few different things but nothing works.

Code:
Sub Insert_missing_3min()
'Inserts a row with the date and time where the missing date and time stamp is and a zero next to the date added.

Dim min3 As Date
Dim CurTime As Date
Dim CurCell As Date
Dim NextCell As Date

min3 = 3 / 24 / 60

[Code] ........

View 1 Replies View Related

Sum Every Nth Cell & Calculate Difference Between 2 Different Time Interval List

Jan 24, 2008

I have two sets of data, one is recorded every 5 minutes and the other is every 15 minutes. I am trying to add every 3 cells in the 5 minute column so I can compare it side by side with the 15 minute column. I have tried one of the responses in this forum with placing 0s in 2 cells and then the formula in the third however this does not allow me to compare the 2 sets side by side.

View 4 Replies View Related

Formula To Count Number Of Patients Per Time Interval Over 24 Hours+

Jan 23, 2014

I have been trying to work out a formula for capturing the number of patients in the hospital at half hour time intervals. There are a lot of formulas for capturing this information within a 24 hour period however not a lot of information when the Length of Stay or episode time is +24 hours.

As you can see in my example spreadsheet below, some of the patients stay for 244 hours (row 9).

The outcome that I am looking for is that a 1 is placed in all of the time slots when the patient is there. For example if they arrive on Jan 1st at 2.15 and leave on Jan 3rd at 10.30 all of the time slots in between would have a 1 placed in them.

I have been playing around a lot and think it is probably only possible if you set it up as I have in the example i.e. by having the date running down and the time running across.

How this could work? I have tried SUMIFs and SUMPRODUCT formulas which generally work for Jan 1st but then go wrong for any date after that.

View 4 Replies View Related

Find Date / Time Entries Within 1 Hour Interval And Remove All But One Of Them

May 8, 2013

I have a large data set which contains four coloumns: Supplier, Supplier number, order number, and date/time of delivery. The date/time coloumn is formatted as YYYY-MM-DD HH:MM with a 24h time notation. What i want to do is to find deliveries that occurs within 1 hour and that are from the same supplier. So i basically want to group (?) the data with regards to the suppliers and then, within these subsets, check for date/time entries that occurs within 1 hour from each others by "reading" each date entry and compare it to the following one(s) (and maybe stop comparing when the 1 hour interval is passed)?

Furthermore, even if this one might be very hard, it would be good if i could make sure that the entries that are "tagged" as within a 1 hour interval, wont be used as basis for a new interval or be included in other intervals.

The result i am after would be number of 1 hour intervals for each supplier and the number of entries in each interval.

Below is an example from the date/time coloumn:

12-03-08 15:32
12-03-08 15:33 ... Interval with 2 entries
12-03-12 14:54
12-03-28 11:57
12-04-16 09:10
12-05-07 13:41
12-05-07 13:46 ... Interval with 2 entries
12-05-28 11:55
12-05-28 12:00
12-06-04 12:01 ... Interval with 2 entries
12-06-04 12:09
12-06-11 08:30
12-06-11 08:31
12-06-11 08:59 ... Interval with 3 entries
12-07-02 11:10

View 8 Replies View Related

Save Excel File As Another File Using Current Date As Part Of File Name

Oct 10, 2011

VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.

View 1 Replies View Related

Excel 2010 :: Save Open Worksheet To File And Include Date Of Save?

Mar 11, 2014

am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....

I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....

I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...

View 6 Replies View Related

Excel Save As Should Default File Save Type To XLSX Using VBA

Aug 21, 2012

What I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.

All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.

View 2 Replies View Related

VB Macro To Run Multiple Macro In Time Interval

Jun 26, 2014

I have to run quite a few excel VB macro every morning one after the other.

I have to manually go thru each of them.

I need to create 1 VB macro which will run all macro after 1 min of interval in between.

Example:

Macro to run Sub Store1
Wait 1 min and then run Macro for Store2.
Macro to run Sub Store2
Wait 1 min and then run Macro for Store3.
Macro to run Sub Store3 and so on.

Below is the code I started with but not successful.

[Code] .....

View 2 Replies View Related

Save As Macro :: Save File To Folder

Jan 17, 2010

I have a macro that copy one sheet of the Active workbook and sends it via email.

I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with its original file name.

View 10 Replies View Related

Open Website - Download CSV File / Save As In Specific Folder Under Different Name And File Type

Jul 6, 2012

I am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).

I am successful at opening the website with the following code, but how to do the rest.

Sub Searchez()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com
IE.Visible = True
While IE.Busy
DoEvents
Wend

'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()"
'this is the name of the download link as from when i hover my pointer over download link.
End Sub

View 1 Replies View Related

Date Function- Open A File, Make An Exact Copy And Save It Under A New File Name

Jun 4, 2009

I'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-NAFTA.XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

Date: 2009-06-03

What I'm trying too do is open a file, make an exact copy and save it under a new file name.

My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.

is there a way too get this too work irregardles of what the day might be?

View 9 Replies View Related

VBA Code To Require Fields - Temporarily Save File Email It Then Delete Temp File

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

Excel Not Allowing To Save File With Uppercase Letters In File Name?

Jan 27, 2014

A co-worker of mine is exporting files from Quickpen as excel files, but every time he names the files with any Uppercase letters in the files name, they are automatically changing to all lower case. Even when he tries to rename the files, they will not stay with any uppercase letters....I have searched all over and cannot find a solution. If he sends ME the file, I can open it in excel and save it with any cases.

View 2 Replies View Related

Save Each Worksheet As PDF File And Name Each File Based On Cell Value In Each Sheet

Jul 10, 2014

I have a Workbook that already has a macro in it that will generate multiple sheets based on certain criteria.

For each of these newly generated sheets (numbered 1-6 in the attached example), I need to be able save each of them to a PDF file based on a unique name contained in a certain cell (in this case, each named is referenced in cell Q1 of each sheet). As such, I should end up with 6 pdfs based on the attached sample files.

The PDFs would ideally need to be landscaped and should be just 1 page per sheet.

In my attached sample workbook, you'll notice that I have a "PracticePrint" macro that doesn't quite get the job done. One other criteria involves not printing any sheet with the characters "1010" in it (my PracticePrint macro is setup to do that, but it fails to do much else).

View 1 Replies View Related

Macro To Save Worksheet As New File Then Return To Original File

Mar 14, 2014

I have a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...

Dim myPath As String, fName As String
myPath = Sheets("Date").Range("C8").Text
fName = Sheets("Date").Range("C9").Text
Sheets("Sage CSV File").Copy
With ActiveWorkbook
.SaveAs Filename:=myPath & fName
End With'

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved