Vba - Input Filename To Open Each Time

May 13, 2007

Below is the code that references a file to import = Jan.txt.
There are different files I want to import, using this macro.

However I have not been successful with creating a variable
and then creating a statement which requires me to input the filename that I want to import for the macro run.

With ActiveSheet.QueryTables.Add(Connection:="TEXT;U:Jan.txt", Destination _
:=Range("A1"))

View 6 Replies


ADVERTISEMENT

Open File With Date In Filename

Aug 6, 2013

I have a csv file named "BB31_2013-08-01". I would like to open this file via vba and this is not my only file. I also have another similar fileames such as "BB31_2013-08-08", therefore, I try to create some function like yyyy, mm & dd where I can modifie it easily for next file.

Here is my code:

VB:
'change weekly
yyyy = "2013"
mm = "08"
dd = "01"

Workbooks.Open Filename:= _
"C:Documents and Settings1314228DesktopTest ProgressBB31& yyyy &-& mm &-& dd.csv"

[Code] .....

View 3 Replies View Related

Error-prooofing Get Open Filename

Jan 23, 2008

I am trying to skip over any errors associated with the GetOpenFilename window, specifically where the Cancel button is selected, or no files selected. In the code below, I get an error "Object required" at the line shown every time this is run, even when the file path is valid and correct, and then the msgbox appears as a result of the On Error statement .

myExcelFilePath is dim Variant

On Error Resume Next
myExcelFilePath = Application.GetOpenFilename("Excel files (*.xls), *.xls", , "locate Report.xls....")
If myExcelFilePath Is Empty Or myExcelFilePath Is Null Then

View 9 Replies View Related

Hyperlinks With # In Filename Wont Open

Apr 14, 2009

Is there any solution to the fact that hyperlinks in excel with a # in filename wont open?

View 9 Replies View Related

Error 53 At Open Filename For Output

Jun 2, 2009

I try to make a macro for saving an Excel sheet to a .CSV file with "@" as delimiter.

At the line "Open fname For Output As #fnum", the Error 53, "no file found" occurs.

The Excel-help is saying that if the file is not present, a new file will be made.

This error occurs now and then, How to proceed?

Full macro:

Sub SaveAsCSV()
Path = "O:actueel"
Fname1 = Path & Range("E2").Value & ".csv"
fname = Application.GetSaveAsFilename(Fname1, "CSV bestand (*.csv), *.csv", , "")
If fname = False Then
MsgBox "Macro Geannuleerd"
Exit Sub
End If

View 9 Replies View Related

VBA Open Filename Works For Some Users Fails For Others

Sep 23, 2013

My company has several users that access a file (File A) that has a macro in that opens another file on their F drive called TEST.XLS. The code (in File A) to do this is:

Workbooks.Open Filename:="F: est.xls"

Just recently this code has failed for some users but not others. For those where it failed I confirmed that they do indeed have the file located on drive F.

As a test, one of the users where the above code failed ran the macro recorder while opening the file (Test.xls) on their F drive. The recorder produced the following code:

Workbooks.Open Filename:="\companynetsta$jsmith est.xls"

It appears the code is failing because Excel is looking for a UNC path as opposed to a drive letter (F).

I prefer that the code that looks at the Drive letter F be used.

How can I get the following code to workWorkbooks.Open Filename:="F: est.xls" ....for all users that have an F drive?

View 6 Replies View Related

Open File With Changing Filename-2007

Aug 1, 2008

I would like to create a macro to open a file in Excel 2007. My problem is that the filename changes often. After updates are made the filename is changed and the old file is moved to a backup folder. So, there is only one similar file in the folder

My filename is "RFQ Worksheet 7 29 2008.xlsx".
The path is "C:Documents and SettingsahaynijDesktop".

View 5 Replies View Related

Open File In Directory With Part Of Filename As String

Mar 11, 2014

I have a Workbook "forecast.xlsx" and several Workbooks which have a individual Number in the Filename (E.g. "3960........xls", "3961.......xls etc.) in the same directory as "forecast.xlsx" + projects"

Now, I would like to browse through "forecast.xlsx" in column H, searching for hyperlinks. In Column H are these Projectnumbers located which are used in the filenames above. Whenever the Cell cointains a hyperlink, following should happen (that far i managed it by myself):

- Store the Cellvalue in a String Variable
- Go to the Directory this.workbook + projects
- browse this folder, searching for a File which contains the stored string in its filename
- open the file
- copy a range
- close file
- This.workbook.Activate
- follow the Hyperlink which has been stored as String before
- paste selection
- continue browsing through column H, looking for the next hyperlink.

I have a VBA which runs bugless, but it copies the wrong range, but to the correct destination.

See the code below:

Sub RESLT_INPUT_ALL()
Dim cell As Object
For Counter = 1 To 1000
Set cell = Worksheets("Projects overview").Cells(Counter, 8)
If cell.Hyperlinks.Count > 0 Then

[Code] ......

View 1 Replies View Related

Open A File, Extract & Paste The Filename Into Another Workbook

Aug 1, 2006

Am working on a procedure to open all files in a folder and copy some data from the files into another workbook (master). I have this piece of the code. However, I also need to get the filename (from which data was copied) and paste this into an adjacent cell in the master (so I know which file each data point came from).

View 8 Replies View Related

Open File With Inputbox Result In The Middle Of The Filename Address

May 26, 2007

I am trying to open a workbook with

Workbook.Open filename:="xxxxx"

The address in the xxxx is something like this "G:AnimalMammalZZZZGorillayyyy.xls"

The ZZZZZ portion is the result of an InputBox.

What is the syntax for placing the result of that InputBox in the middle of the address?

View 3 Replies View Related

Excel 2010 :: Macro To Open XLSM File Based On Latest Date Found In Filename

Feb 11, 2013

I'm on excel 2010 and I have a small group excel files I open everyday. Most of the files are static in name and location. I've got a macro created to open those files, which works fine with workbooks.open and the file path.

There are two report files I want to incorporate into my macro of workbooks to open. The files are created weekly and the files names have the following format: "Report Name (YYYY-MM-DD).xlsm". I don't want to use the file's last modified date because older files may get edited after the more recent ones are created. The files are also not always created on the same day, so the solution needs to be flexible enough to not refer to a specific day of the week or anything.

Macro open an excel file based on the latest date found in filename.

View 9 Replies View Related

Generate A 6 Digits Code Based On Filename And Time Of Generation?

Aug 20, 2009

I need to enter a revision string into a file.

Basically, I need to combine the filename and its generation time and then encode it into a string (i.e. a 6 digits HEX code or 6 digits using [0-9][a-z][A-Z]) so it would generate a pretty much unique revision number.

So, is there any function that you can think of that would do something similar?
If not, what is the best to tackle this?
Generate a CRC or md5sum?

What would be in your opinion a quick and efficient way to solve this tricky one?

View 10 Replies View Related

Excel 2003 :: Renaming All FileName In Folder Using Macro At Time With Different Names

Sep 23, 2010

Now iam Working in excel 2003, I got a Requirement that, After Downloading datas From SAP, It Directly Stored in Excel Sheet , Using tht Sheet1, I need to rename all the Filenames at a time, Is It possible, Any code is there to rename,

For Example,

From SAP to Excel Sheet Datas are Like this in Sheet1,

DmsNo Filename
50007685 SDFFG.jpg
50004678 HGJKID.jpg
50003421 VGFTHR.jpg

Then i have rename that filename, rename 'SDFFG.jpg' To '50007685.jpg', and it Should be directly rename to my Source File.

For Example:
Source File: C: estSDFFG.jpg

After renaming it Should be,

Source File: C: est50007685.jpg

any code is there to rename,

View 4 Replies View Related

Time Formula: Input The Arrival Time Of A Car

Feb 18, 2007

when i input the arrival time of A Car, the time he arrives late appears in column D it should read 15 Minutes. how i can get this time to appear automatic after i have registered the arrival time. A Car due at 19:00 arrived at 19:15. 15minutes late. I would be grateful if you could lead me in the right direction.............

View 3 Replies View Related

Open A Website And Input Text In Search Box?

Apr 22, 2014

I have an Excel sheet with about 1,000 rows. Each row has an origin and destination column. Now I need a code that opens a specific website and goes to a specific tab and then search for the origin and destination fields queries. The code then takes each row and place them in the search query. The result from each row can result a valid connection between an origin and a destination OR no connection exists. So simply if connection is successful, the code simply returns "yes" otherwise "no".

I can open the website from Excel, but what I need to click on specific tab where I do my searches. One of the problems I am facing is that when I try to click on the tab I need I get the message that I must use Internet Explorer or Chrome or Mozilla. Although Excel uses Internet Explorer to open the website. I am not sure why this error.

Attached you will find the following:

1 - Few data rows that we need to run the loop on.

2 - A snapshot of the website that we need to input the data from the rows (FOLLOW LINK)

3 A snapshot to show you the tab ("Ocean Schedule") that we need to access after we open the main website in vba. (FOLLOW LINK)

View 3 Replies View Related

Date / Time Input On Every Row

Nov 3, 2008

I am running excel 2003. I am creating a book that needs date & time input on every row. I am trying to input this automatically based on adjacent cell being >0. Every time there is a new input, the previous dates / times update to the latest one.

View 5 Replies View Related

Input The Exact Time

Oct 25, 2007

I have a sticky problem. I have a spreadsheet set up that counts almost everything I do at work, and I would like to input the exact time I receive a call and have the spreadsheet break it down into time periods, e.g., 8-10, 10-12, 12-2, etc., to count what time of day I get most of my calls and sales. In other words, I want the sheet to read the time I input and count it as part of a specific time period, and then count the number of calls taken and sales made from those calls. I can easily compute my closing percentage when I get those numbers. Also, since I have all the days of the month on the same spreadsheet, would it be possible to break them down by day (range)?

I can see where knowing how to do this could come in handy for other projects as well.

no VBA. I play with it at home, but I also struggle with it mightily.

View 9 Replies View Related

Input Time Without Colon

Oct 25, 2007

I am making a template for our security kiosk log and I wanted to simplify it as much as possible. I found the "time mask entry" and imputed that into the vba code. It was working for awhile however stopped working a few days ago. I went back and copied and pasted it again and changed the range to what I needed and an error came up stating "unable to set the NumberFormat property to the range class."

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub

With Target

vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
Application.EnableEvents = False
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With

Application.EnableEvents = True

End Sub

View 9 Replies View Related

Time Fractions And User Input

Mar 12, 2008

What I am doing is creating a spreadsheet solution to tack race times. These show up in past performances in the following formats in paper copies: 45 2/5 seconds. The end user would have to put in maybe 30 of these times based on the number of contestants. Keeping in mind that the 45 2/5 number will be used in some calculations, I want to give the user the easiest input interface. Meaning instead of inputting like this; 0:45.4 (mm:ss/00 format), I would like them to just enter 45.4. the spreadsheet would input format this cell into a time format that would be able to have calculations done to it. Another example might be 1:35.60 (mm:ss.00 format) which is 1 minute 35 and 3/5 seconds. This would be entered as 135.6.

A few questions first:

1) Would it be easier to leave the number inputed alone until the calculations ar finished then translate into a time factor?

or

2) would it be necessary to convert into a time factor as inputted be cause of the 60 factor involved with the time factor?

Having asked those questions, setting this all up with the exact scope I am working towards, any help or sugesstions as to the best way to proceed would be fantastic!

View 9 Replies View Related

Input Future Date And Time Into One Cell

Oct 20, 2012

I need to input a future date and time into one cell. For example, if I have a member ship that ends on October 20th at 3:00 pm exactly the kind of format you get when you enter the function "NOW". I would like to put that into one cell and then be able to add and subtract from it.

For example I would like to do something like this, if I had inputed the date and time in A1:

"=Now-A1", it would be cool if I can get the remaining hours or minutes along with how many days are left but not completely needed.

It would also be beneficial if I can drill down further and do it by time zone as well.

View 2 Replies View Related

Auto Date And Time - Input Data

Dec 6, 2013

Auto date and time using excel macro.

In cell B2 until Z2 I am inputting data.

Once the data has been inputted in cell B2, I like to have date and time to have in Cell B6.

If input in cell C2 then cell C6 to have date and time..

View 4 Replies View Related

How To Lock Down Time And Date When Input Data

May 23, 2012

Is there anyway to lock down the time & date when I input a data? Assuming I have a TIME formula in column A, and a DATE formula in column B. So when I input any data in column C, A & B can automatically show and also lock the time & date.

View 5 Replies View Related

System Name And Time Update Based On Input?

Feb 26, 2013

In Excel sheet1 Column G has Names(John,Augutus,Jennie ect..) In Coulmn H i need to daily update the Present or Absent status, once i update the status i need Coulmn I to automatically fetch the System name and Column J should update with Time and date..

View 7 Replies View Related

How To Automatically Lock Cells Every Time Input A Value

May 6, 2004

a code that would automatically lock a cell every time I input a value to it?

View 2 Replies View Related

Input Current Date Time When Saving

Aug 26, 2006

I am trying to create a code so that when the save button is hit, it puts the current date and time into a given cell before it saves. This way I will know the last time something was updated and saved.

View 9 Replies View Related

Lock User Input For A Given Amount Of Time

Jan 8, 2008

Is there a way to freeze the user input when it first comes up for a given amount of time. In essence, I am trying to force the user to read before clicking.

View 9 Replies View Related

Format Cells So 24 Hour Time May Be Input Without Colon

Nov 30, 2003

Is there a way to format cells so 24 hour time may be input without the colon, as an example 1425 instead or 14:25? We can do it in Access. One would think it possible in Excel, yet I have not discover how if indeed its possible.

View 9 Replies View Related

Check User Input To Prevent Run-Time Error

Dec 28, 2007

what I want. It relies on an input box asking the user to enter a part code, which when found creates a duplicate copy. However, if the user enters a part that doesn't exist I get a run-time error. Can anybody point me in the right direction so when a user enters an incorrect part they get a message telling them it doesn't exist.

Private Sub SortTest_Click()
Dim s As String
Dim r As Excel. Range

Range("A2").Activate
s = InputBox("Enter the number you wish to find")
If StrPtr(s) = 0 Then
MsgBox "You must enter an existing part number!"
Else
Set r = Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Range(Cells(r.Row, "A"), Cells(r.Row, "AH")).Copy
Sheets("APL").Cells(r.Row, "A").Insert Shift:=xlDown


Application.CutCopyMode = False
Application.Goto Sheets("APL").Cells(r.Row, "H")
Selection.Offset(-1, -5).ClearContents
Selection.Offset(-1, 0).Select
End If

End Sub

View 7 Replies View Related

Input And Display Data In Cell In H:mm Format Without Changing To Time

Feb 25, 2010

Can i put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32 +B36:AF36 in a h:mm format.

When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.

Example:

B20= 1:15 (1h15m) NOT 1:15:00 AM

B24= :30 (30min)

B28= :45 (45min)

B32= 1:45 (1h45m) NOT 1:45:00 AM

B38= 4:15 (4h15min)

I have attached a copy of the form for better clarification.

It doesnt seem to be complicated, but it's certainly a lot more than I thought.

View 4 Replies View Related

Search Timestamp Value Giving Input With Date And Time Rounded Off Value Alone

Jul 29, 2013

I am a beginner to Excel macros. I am writing a macro to find the value based on the search criteria am giving

E.g. ( Record in table)

Column A
24/07/2013 15:04:05

Input am giving

24/07/2013 15:04 ( since am not sure about the milliseconds)

I need to find and fetch the Datas based on the input am giving

View 2 Replies View Related







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