Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










VBA Macro To Find Specific File & Open


I've got a VBA macro set up in one Master excel file that manipulates a number of other files. Both the Master and the other files are all in a folder which is currently called C:Documents and SettingsusernameDesktopPipeline and my VBA script has this defined as the filepath and works exactly as I need it to. I will be moving the folder "Pipeline" to a shared drive on my server so my staff can access it, and will be sending it to colleagues who will be installing it on their own servers so they and their staff can use it. Is there a way to amend the code so that the VBA in the Master file looks in the folder in which it is locate, "Pipeline", no matter what the filepath leading to the "Pipeline" folder is?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Find And Open File In Specific Folder
I am trying to write a macro to find a file in a specific folder. The file name changes weekly. (Ex. C:My Folder31207.xls). I need to find the most recently modified version of the file and open it.

View Replies!   View Related
Edit Macro To Open Specific File Automatically
I am using this code to get me to a path then I click on the file I want and it opens it and makes it a .csv file. Then my code does stuff with it and closes it. My question is, do I even have to click on it? Can I get the macro to automatically open the file. Note the "NewestFolderInThePath" is a function that open me up to the "newest" created folder in the path. From there I double click file.

NewestFolderInThePath = ReturnNewestFolder("\datawhse
ootLAW81LAWSONprintRBLEVINS2anrvwfins1")
Path = "\datawhse
ootLAW81LAWSONprintRBLEVINS2anrvwfins1"

TheFile = Path & NewestFolderInThePath
CreateObject("WScript.Shell").CurrentDirectory = TheFile

TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Balance Sheet File FRPMBSDTL")
If TheFile = "False" Then
continue = MsgBox("Do you want to Continue to IS Download?", _................

View Replies!   View Related
Open File To Specific Sheet Other Than Last Save
If i have a sheet with multiple tabs can I somehow make excel open that file on a particular tab rather than the tab that the user was on when they saved/closed it.

View Replies!   View Related
Macro -open The Look In List” And Stop So I Can Pick A File To Open
I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box

View Replies!   View Related
Open File Macro, Error - Incrementing File Name
When it opens the desired file, it increments the file name by 1 each time it is opened (via the macro).

Example;
The first time it is opened you see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages1"...
I close the file and open it again (via the macro), and see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages2"...
The file name continues to increment on every opening until the PC is rebooted, then it starts at 1 again.

Sub OpnLTpages()

Dim wb As Workbook
Dim AlreadyOpen As Boolean

AlreadyOpen = False

For Each wb In Workbooks 'Scan open workbooks
If wb.Name = "P'Binder L&T Pages.xls" Then ........................


View Replies!   View Related
Measure Time Taken To Find File & Open
I am trying to measure how long it takes to locate a file the user wants to open. Eg., from when they click File|Open to when they click the "Open" button in the Open dialog box (after they have navigated through the directories to find their file).

I have approached this by using a class module and withEvents. This uses the Click event to trap when they have hit the File|Open button from the File menu and timestamp it.

class module: EventFileOpen

Public WithEvents cbOpen As CommandBarButton

Private Sub cbOpen_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
MsgBox "File open started at: " & Now()
End Sub

Private Sub Class_Initialize()
Set cbOpen = Application. CommandBars.FindControl(ID:=23)
End Sub

Private Sub Class_Terminate()
Set cbOpen = Nothing
End Sub..........

View Replies!   View Related
Saving A File With Specific File Name Using Macro
I am trying to save my workbook in a specific directory with a specific filename and to incorporate a date field from within the spreadsheet. I have changed the format of the cell so it does not include / as i know this would not work. Saving it is the easy part but adding in the field from the spreadsheet is where im falling over.

View Replies!   View Related
Open Excel File With VBA
I have a folder that contains many excel files. Each excel file is named as that date it was created, i.e. 02/18/09.xls

I would like to have some vba code that will open 'todays' file. Here is what I have so far:

An Excel file with the date founction in A1, =today(). I'd like to have a button that reads cell a1 and opens an excel file in a specific folder.

View Replies!   View Related
VBA To Open A File On ANY Desktop
I am sending this workbook out for several buyers to use. Part of the macro opens a workbook on their desktop named "Backorder List.xls" and copied data to it. The problem is, the way it is written, I have to go around to each buyers computer and edit it so the vba is looking in the right directory for the file. Is there any way to modify this so that it will open the file "Backorder List.xls" no matter who's desktop it is on?

View Replies!   View Related
Using VBA To Open A File Name That Changes Slightly
I want to use vba to open a file where the name changes slightly each week it comes in. Let's say the file name is f:/Folder A 05212009

I can do this to open it:

View Replies!   View Related
Open TEXT File In VBA...
I am trying to open a txt file, but I get an error message that says "Run-time error '52': Bad file name or number. The file name is correct. I tried two ways to pass the name -- as a variable and from Application.GetOpenFilename("Text Files (*.txt), *.txt"). The line where VBA complains is "Open fileToOpen For Input As #InFile"

View Replies!   View Related
Using VBA To Open And Close Source File
I am working with a bunch of files to do a dashboard. I have the main file which shows end results and what my users will see "Dashboard". I have about 10-15 files which I export weekly from MS Access. and I have the "Dashboard Data" file. In this file I have a sheet which links all the information for the files exported from MS Access. In here I have named ranges and dynamic ranges plus additional columns created to look up information in other tables. I use this file as my source data for my Dashboard File so that I can reduce the size of the actually dashboard. Dashboard Data file is about 5.5 mb and my Dashboard is 300kb. I have 12 branches that need to view the data over a radius of a hundred miles so the smal file are better.

My problem: I can get the Dashboard Data file to update without opening all the 10-15 other files. I cant however make the Dashboard file update without opening the Dashboard Data file. I have it to auto update without prompting but its still tells me that it cannot update. I was wondering if there is some VBA code I could use that would work like this:

I open the Dashboard File, Dashboard Data file opens so that Dashboard file updates and then close Dashboard Data File so that the user never has to see the Dashboard Data file. I dont want them to see it or have access to it.

View Replies!   View Related
Copy A .txt File That Is Currently Open - VBA
I am trying to copy a text file that is currently open in the workbook directory to another directory. I have been using

View Replies!   View Related
VBA Cancelling The Open File Dialogue
I have this code, the problem is when I try to cancel the Open File dialogue it gives me an application defined error and I tried it a dozen ways and it keeps giving me the same thing.

View Replies!   View Related
Open/Run Another Application/File Using VBA
I need to open/launch
-applications like Outlook, IE
-files like .bat, .xls,
-folders (with windows explorer)

using VBA.

How to code this?

View Replies!   View Related
Show The VBA Part Without Open File
I have one excel file and i want to see the VBA code without open excel file. because when i open that excel file automatic delete userform and save then open that file. i have a second copy of that file in that file there is a one other option to see and learn from it. 1.my computer security is High but when i open the file one userform is come and ask me about enable or desable the macro when i check the box that file is open and run properly.

and after that never ask for Enable or desable the macro, every time that file is open in high security without ask..security. for your information many vba code in that file. i have only one question how see the code without open that file ?

View Replies!   View Related
File Remains Open In Vba Background
When I open a file in excel and I close it without closing excel-application, the file remains open in the Vba-background. This is not a big problem but when I reopen the file I get 2 versions in the Vba-explorer. This continues until I close the whole excel-application. So everytime I reopen the file the vba-explorer adds a new version. Is somebody familiar with this? Is this normal or is this a bug?

View Replies!   View Related
VBA Open File In Dynamic Named Folder
Anyone up for getting me started on code to open a file in a "dynamic" directory, specifically:

Where

Main directory is G:REPORTS
The file name is "Pivot_xx-xx-xx.xls"
The directory where the file is located is G:REPORTSxx-xx-xx
xx-xx-xx is yesterday's date

View Replies!   View Related
Open File Macro: Same Directory As Macro Workbook
I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.

Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"

View Replies!   View Related
Open File With Macro
I was wondering what the code is to open a file (already specified in macro) when you run a macro

View Replies!   View Related
Macro To Open Specific Files And Copy Data From Week Number Sheets
What im trying to do is have a macro that will open a file called Blue 1.xls in location "I:SchedulesBlue" and then copy data from cells N13:034. Then paste it into another excel file (Press.xls)on worksheet "Press Break" cell G14.

The data that is copied not always fills up the cells N13:O34 so I need it to go to the next blank cell in column N and paste the information from file Blue 2, and so on for Yellow 1, Yellow 2, YellNR, and Green.

So to summarise I need a macro to open 6 files copy data from the same location on each of the files(N13:O34) then close and paste it into a master document(Press.xls) worksheet "Press Break".

View Replies!   View Related
Macro That Will Open The File Path
hi, i need a macro that will open the below file path, and when i select the file i would like the macro to copy the active sheet from the source file being opened from range A2:L10000 and then paste special values to destination CELL A16 of the activesheet of the workbook.

I would like the date format for the below to be generic so it will apply for all months and years.

S:RECSBANKRECNominees200812-2008

View Replies!   View Related
Autostart Macro When Open The File
how to produce an autostart macro, so when i open the excel file it will go straight to a specific worksheet, or is there another easier way of doing this ?

View Replies!   View Related
Macro: Check If File Open
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 Replies!   View Related
Macro Needs To Run Each Time I Open The File
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 Replies!   View Related
Create A Border Macro When Open The File
I have recorded a macro which will create the border for me when the file is opened, but it turned out too long. if someone can show me how to reduce the codeing. I have the attached file.

Range A5 to E20 thin Border all sides and thick border allround (16 rows)
Range A21 to E36 thin border all sides and thick border allround (16 rows)

end range is A356.

View Replies!   View Related
Error In Macro That Checks If The File Is Already Open
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: ...

View Replies!   View Related
Open A Pdf File In Macro Code
how to open a pdf file (located: "C:myfile.pdf") in macro code?

View Replies!   View Related
Macro To Parse And Open Text File
1. Open a text file from a directory on my computer
2. Feed the data into a Excel spreadsheet
3. Parse the text from the text file into columns

an example of some of the stuff in the text file (its all fake btw )

192.168.1.216,,,
J2MARTIN,20/08/1999,8:57:27 a.m.,
372,http://www.microsoft.com/isapi/redir.dll?,,
192.168.1.216,,,
J2MARTIN,20/08/1999,8:57:27 a.m.,
326,http://home.microsoft.com/,,
192.168.1.216,,,
J2MARTIN,20/08/1999,8:57:29 a.m.,
37014,http://www.msn.com/default.asp?,,

Is it possible to feed all this data into excel into seperate rows and then start parsing it into columns of

IP Username Date Time KbUsage URL

View Replies!   View Related
MACRO Saveas Keeping Current File Open
I have the following Macro that I run assigned to a Button.

Sub AskAndDo()
If MsgBox("Have you Finished collecting data ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
If MsgBox("Have you Printed the Reports ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
ActiveWorkbook.SaveAs Filename:="\gamingntcompanyFLOAT MASTERFloat_Sheet_" & Format(Date, "_YY_MM_DD")
End If
End If
Application.Quit
End Sub.......

View Replies!   View Related
How Do I Get A Certain Macro To Run Anytime I Open Any Excel File
I want to put in an if-then statement in a macro that will check to what kind of report i open so it will automatically know what to do with it. Where do I put that macro? I tried it in auto_open in my personal.xlsb file but it stops before the file actually opens.

View Replies!   View Related
Macro: Allow User To Choose Workbook/File To Open
I am making a macro that requires that user to enter a directory path into a input box and the macro will open the file. Right now it works fine bit i would like to change the code so that instead of using an input box it brings up a box that will allow them to browse files on their computer and then select one to open.

View Replies!   View Related
Run Excel Macro From Batch File. Workbook Open
I wanted to run a batch file which does something like,

Dir /b >Test.csv
Start Test.csv

And then runs an Excel macro once the workbook is open.

I looked into this and discovered that it’s not really that possible to run an excel macro through batch file but other options were to use ‘Workbook_Open’ etc.

I thought this might cause problems because Test.csv doesn’t contain a macro to auto open and because csv’s usually don’t like macros.

I do however have personal.xlsb which opens every time if that helps.

View Replies!   View Related
How To Supress Enable Macro Option While Open A Excel File
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.

View Replies!   View Related
Macro: Paste Cell From Specific Files To 1 General File
i have about 80 files that all have the same structure. I also have a general excel file that I need to fill with values from each of the 80 files. I'd like to use a macro that can be applied to each of the 80 files at a time (or at the same time, if that's possible) so I can copy the cells (only the text, not the formula) I need from each individual file to the general sheet.

e.g.,
each file is:

xyz 123
abc 456
ghi 956

I need to copy the "123" to a cell in the general sheet, "456" to a different cell in the general sheet and so on (to be able to choose which cell in the general sheet I am copying to).

View Replies!   View Related
Macro To Find A Specific Word And Insert Cell
I have a large spreadsheet with about 18000 rows or data and about 60 columns. I need to a macro to find a specific word in a column like "charge" and anytime that word is found in the column to insert a blank cell in front of it.

Currently the sheet looks like this:Fee AChargeFeeChargeFee AChargeFeeChargeFee AChargeFee AChargeNeed to get it to look like this: (so I can sort and subtotal)Fee AChargeFee AChargeFee AChargeFee ACharge

a macro to the for whichever column I choose.

View Replies!   View Related
Find And Replace Macro Based On Specific Cells
After much fretting over this (and many other Excel issues), I've decided to seek out some assistance. I've included an example with columns, because I'm not very fluent in Excel terminology.

View Replies!   View Related
Macro To Open File, Copy Paste As Values And Save As Excel Html
I have a folder containing 40 single sheet excel workbooks and I would like to automate following tasks:

- Open each excel file (need to open the file so as to update it since it gets the data from another workbook through =formulas)

- Copy paste as values

- Save this as excel html in the same folder as original excel files (keeping the original file name)

- Close (original excel file should not be changed ie formulas should remain in place, only the html file will contain values)

- Since there will always be xHtml files with same name need the macro to replace the excisting file

My abilities with excel are limited to functions, no VBA knowledge other than finding ready codes and pasting them in the module.

Since this routine is to be run almost daily the macro should run all files, instead of one by one.

I just hope that I am not asking too much for excel to handle and I hope that explanation is clear.

View Replies!   View Related
Macro To Find Required Data Than Copy/paste In Specific Cell Address
I want to copy some data from internet and paste it in to worksheet location Range A1:A30. the data look like following information.

LocationKuwaitP.O.Box:4819 SafatAddress:ShuwaikhTel:-22253580Fax:-24343645Category:CONSTRUCTION COMPANIES

the above information have following header ie. Location, P.O.Box, Address, Tel, Fax and Category.

every time one of the header inforamtion is missing ie. Address or P.O.Box, Or Fax, due to this problem every time cell address of information aganist header is change. supose in first copy/paste Tel inforamtion is in A15, but second time it may be on A13, and 3rd time it may be on A17 and so on.

I want to make a macro which search header ("Location", "P.O.Box", "Address", "Tel", "Fax", "Category" ) from a Range A1:A30. than down two steps to copy header information (75325412) and paste in fixed cell location C1:C5 (C1=Name, C2=Location, C3: P.O.Box, C4=Address, C5=Tel, C6=Fax, C7=Category) in same sheet. and repeat the steps to copy information aganist header from all header in Range A1:A30.

View Replies!   View Related
Vba Editor Come Up Everytime I Run Specific Macro
i have a sheet with lots of macros.

for some reason whenever i run this one specific macro the vba editor screen comes up.. there is no errors. dont understand why it keep coming up.

also when i run this specific macro the first time it works really quickly.. and the next time i run it, the code takes a lot longer execute any ideas why?

View Replies!   View Related
Assistance With Creating A Specific Macro Using VBA Code
I was wondering if someone could help me generate a Macro to do the following:

I have a sheet with the following characteristics.

Column A, rows 8-15 contain headers
Column A, rows 17-24 contain headers
Column A, rows 26-40 contain headers
Column B to CV, row 6 may or may not contain an "X"
Column B to CV, rows 8-15 & 17-24 & 26-40 may or may not contain the various data

I need a macro which does the following on the press of a button located somewhere on the sheet:
Generate a .TXT file in the folder C:Test with the name "Test_YYYY_MM_DD_HH_MM_SS_Full.txt" which uses the system time and date to fill in the values In the TXT file the following data should be created: ....

View Replies!   View Related
Macro To Find The Last Date Of A File
I need to write a macro that will find the most recent date of file. For instance, I may have the following:

A B C
BIBLIO.MDB367411204/30/1998 22:27:36 CDT
BIBLIO.MDB361062407/25/1996 01:00:00 CDT
BIBLIO.MDB361062404/24/1998 01:00:00 CDT

Where Biblio is the filename, then the size of the file and the dates. I want to do this for a very long list and get only the most recent date used and delete the others.

I have a list of several thousand files and I want the macro to be able to go all the way down the list.

I am using Excel 2007.

View Replies!   View Related
VBA Goal Seek Macro To Calculate Specific Range Only
Is there a way to use goal seek in VBA that won't trigger a full model calculation?

I would like to (somehow) perform goal seek, but only recalculate range("A1:B50") say on a single worksheet (where both the input value and the final calculated value is included in that range), as opposed to calculating the whole model inbetween each iteration.

Also, all dependencies between the input value and the final calculated result are contained in that range.

View Replies!   View Related
Use Wildcard "?" And VBA To Open A File
I'm trying to open a file using VBA, part of the filename changes each week. For example, Atlanta_9EM_weekly.rep will change to Atlanta_8RH_weekly.rep. the code I'm using is: fname=Atlanta_" & "???" & "_weekly.rep. It appears that the program is looking for file named: Atlanta_???_weekly.

View Replies!   View Related
Check To See If File Is Open, And If It Is Open, Activate It
I have two workbooks. One is a no-nonsense form interface that my bosses will use to enter safety information. I'll call this workbook "Form". This file is stored locally on each of their computers. The other workbook is stored on a common drive. I'll call it "Master".

When my bosses fill out the Form and click "Submit", the Master file is opened, and certain cells are populated based on information entered in the Form. This is the code I am using to make this happen:

View Replies!   View Related
Get Open File Name To Open Multiple Files
I'm doing the following:

Dim myname As String
myname = Application. GetOpenFilename

I use it to attempt to open first a QuickBooks file and then an excel workbook.
The Quickbooks file opens fine. The Excel workbook never appears to open but
I do get the full file pathname to my excel workbook returned which I then
parse off to get just the workbook name.

Why would it behave this way? I open both these files manually all the time.

Also, I'm trying to use a filter as follows:

myname = Application.GetOpenFilename("*.QBW")

I get a compile error saying that I'm calling the function wrong.

View Replies!   View Related
VBA Macro To Tell Who Last Saved A File
Macro (saved on the workbook not in the Personal macro file) which should give the following info (lets say on sheet 1, starting A1, A2) who last saved that file and the date.

View Replies!   View Related
Date Function- Open A File, Make An Exact Copy And Save It Under A New File Name
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 Replies!   View Related
Open .xls File From .bat DOS File & Save As .csv
What I would like to do is to open an excel workbook from a bat file in dos with one arguement.

so something like

start c:lacklist.xls 2315t

Once the blacklist.xls is opened I would like to have the macro to save the excel file as a csv file ignoring all the prompts and use the argurement 2315t as the directory to save it to on the j drive ie save to j:2315tlacklist.csv. Then close the workbook and continue with the rest of the commands in the bat file.

I'm guessing I have to use GetCommandLineA to read in the arguement but I can't find anything on the net that gives a clear explanation of how to use it.

View Replies!   View Related
Open A Closed File To Archive Some Data From A Current File.
I have the need to be able to open a closed file to archive some data from a
current file.

what coding i need to be able to kick the "open file" bit off?

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved