Open A File Without The Directory Name

Jul 26, 2007

Can i open a workbook without knowing the directory? more importantly without showing the directory?

I want open the file "EstimatingSheet" where ever it is.

View 9 Replies


File Open Dialogue Box To Open In (Directory Of Tool File)

May 31, 2013

I have a tool file which contains Excel VBA codes. When I click button it asks to select a file. I want that anytime I click the button the dialogue box should open in the folder containg the tool.,

View 1 Replies View Related

Open File From Directory & Name Stored In 2 Cells

Aug 29, 2007

I have numerous sub directories, C:/Jobs/12500 – 12599, C:/Jobs/12600 – 12699 etc, within these directories exist spreadsheets with file names like 12675.xls (in this case it’s in the “12600 – 12699” directory). In my open spreadsheet, in cell A1, there is a job number, say 12675. I want to find and open the existing 12675.xls spreadsheet. If the spreadsheet does not exist, a template (Costing.xlt), in a fixed directory, is opened and then saved, in the correct sub directory i.e. C:/Jobs/12600 – 12699/12675.xls. I am now using the following code, but the directory is fixed. How can it be modified to work with variable directories?

On Error Resume Next
Workbooks.Open Filename:="C:Spreadsheets" & Range("A1") & ".xls"
If Err <> 0 Then
Workbooks.Open Filename:="C:SpreadsheetsCosting.xlt"
ActiveWorkbook.SaveAs "C:Spreadsheets" & Range("A1") & ".xls"
End If
On Error Goto 0

View 3 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:

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

Prompt User For Directory And Filename; Then Save File In Directory With File Name

Jun 10, 2009

I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.

View 3 Replies View Related

Open File Macro: Same Directory As Macro Workbook

Nov 17, 2006

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 3 Replies View Related

Open Directory Using Button Click VBA?

Apr 25, 2012

I'he a button in Excel Userform and i want to open directory using button cliick and put selected directory in textbox1 e.g. i've selected D:Susheel then it will reflect in textbox1

View 3 Replies View Related

Browse Directory, Open Specified Filenames Then Close

May 5, 2006

I have done a search in the forum, and there are many results on browsing directory and opening files, but they are not what I have in mind.

What I have is different folders for different months (e.g. “January”, “February’, “March” and so on), and in each folder I have the same file names (e.g., in “January” folder, I have files called A, B, C. In “February”, I also have files A, B, C but these are different from the files in other folders)

Basically I need a macro which can open a specific list of files.

So first step, the user should be able to browse the directory, then select a folder (no need to open, not sure if it makes any difference though). I need the user to be able to select different folders because the macro should work for the different months in different years.

For this, I have found this
Function GetFolderPath() As String
Dim oShell As Object
Set oShell = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select folder", 0, "c:\")
If Not oShell Is Nothing Then
GetFolderPath = oShell.Items.Item.Path
GetFolderPath = vbNullString
End If
Set oShell = Nothing
End Function...........

View 9 Replies View Related

Copy File Names From A Directory Into An File

Mar 11, 2007

Is it possible w/ some vba code to copy the names of files, either from a search or a specific directory to an Excel Worksheet?

View 9 Replies View Related

Macro That Saves Current Open Workbook To Directory Specified?

Jan 10, 2012

I would like a macro that saves the current open workbook to a directory specified in a cell on the open work sheet.

View 4 Replies View Related

Open All Excel Files In Directory - Code Doesn't Work For One Specific Path

Feb 8, 2014

I have my code here:

Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")

[Code] ....

GetFolder = sitem
Set fldr = Nothing
End Function

My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.

View 6 Replies View Related

SaveAs To Same Directory But Different File Name

Feb 4, 2009

By Code I am trying to SaveAs the present Workbook : Only in the same directory as the present Workbook- but with a different file name. The new file name would be :"the Filename of the Active Workbook" + " ("D9") of the Sheet("ADMIN")". This macro will only be run from the Sheet("Admin").

View 4 Replies View Related

Looping Through Each File In Directory?

Mar 5, 2013

How can i loop through each excel file in a directory.

View 1 Replies View Related

File List From Directory

Jul 1, 2013

I have a spreadsheet, where the corresponding invoice number is located in column I.

All the necessary invoices have been scanned in as pdf's in folder Y:FinanceSalesInvoices.

The question is, can a code be written to say in column Z, that a hyperlink be created to that particular invoice. For example, in cell I2, it would have Invoice1, then in cell Z2, it would have a hyperlink of Y:FinanceSalesInvoicesInvoice1.pdf.

View 1 Replies View Related

Find File In Directory

Jul 18, 2008

I have a workbook that is produced by some other application that names the file "FAMMToday_#.xls"

Where # increases from 1 to 2 to 3 to 4 to 5 and so forth until all the analyst are done with adjustments. so the directory can look like but doesnt always go to five and nor is it limited to five.

Is there a way to pull the latest file?

View 9 Replies View Related

To Get File’s Directory (Address)

Dec 7, 2008

If I know the name of the file how can I get that file’s directory (Address) with macro or function?

View 9 Replies View Related

Copy Selected File To Specified Directory

Jul 23, 2013

Basically what I want to do is to create a button which when pressed opens a file picker (I think its called this way) and the user should find and select a pdf file. And when he does that he clicks "OK" and this file is copied to specified folder.

View 7 Replies View Related

Can't Control Directory File Is Written To

Mar 1, 2014

The script below writes a ".csv" file to the same directory that contains the workbook the macro, "foo" is run from. I'd like to change the script to always write the file to a separate directory which is: "c:etcupload".

[Code] .....

View 1 Replies View Related

Extract Newest File From Directory

Jul 21, 2008

I have an excel spreadsheet that has a list of pathways to different folders (ex. Q:TechnicalY001) in each row. I want a macro that will search each of the folders I list in a range of cells and return the most recent file by the Date in the filename:

Example: Cell A1 contains folder path Q:TechnicalY001
Cell A2 contains folder path Q:TechnicalY002

The macro would return the most recent file in Y001 and dump the filename into cell B1. Then it would return the most recent file in Y002 and dump the filename into B2.

After I have all of the most recent filenames, I want to be able to search each of those files for data in certain cells and then dump that data into the spreadsheet. The cells are always going to be the same in each file.

View 14 Replies View Related

Copy Most Recent File To Another Directory

Sep 11, 2009

I've got this code that will copy data into my worksheet from the most recent .IRP file generated on my C:/ drive. Along with copying the data to my worksheet, I'd like to have a copy of the IRP file copied to a folder on my L:/Drive.

In the code section I've commented out the code that allows the data from the most recent file to be copied to my worksheet. I found code that supposedly will copy a file to a new directory. I want to combine this into my code to allow both to happen. Here's my attempt.

View 5 Replies View Related

Macro To Erase File On A Directory

Mar 9, 2013

The code bellow allows me to save a copy of the invoice by way of doPDF using invoice number as the name of the pdf to be saved.


Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= "E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _


As there is the need to, should a quotation or an invoice be wrong, to change it. In other words I have code to cancel the invoice as bellow.


Dim sResponse As String
Dim rFound As Range
sResponse = InputBox("Enter record you want to change." & vbNewLine _& "ATTENTION!

This will change the current record number and will erase record data from saved records sheet.")

If sResponse = "" Then --cancelled or OK with no entry: do nothing or add message
Set rFound = Sheet1.Columns("L").Find(What:=sResponse, LookIn:=xlValues,


The code above simply removes a line on sheet1 to which yet another macro retrieved some info from an invoice such as date, invoice, customer and total.

Now I just realized that when I change an invoice, I will be again when finished, creating another pdf file that already exists.

How can I have the second macro here changed so that it will go to ""E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _" and delete this pdf file?

View 9 Replies View Related

Change File Directory Macro?

Nov 11, 2013

I have a 2 columns.

A: Current location Ex(C:UsersDesktoplabelsA02FAA.jpg)

B: Future location Ex(C:UsersDesktopoxes98300SA02FAA.jpg)

I want to move jpgs from location A to B

is there a way to execute this with a macro?

View 5 Replies View Related

Opening A File From Any Active Directory

May 31, 2006

The file that this code is located in is often sent to others to use which means I have to modify the directory and path each time I send this file in order for the VB code to work.

Workbooks.OpenText Filename:= _
"C:My documents-2Monthend Supplemental ReportsDeficiency ReportscurrentC37.txt"

Is there a way to have the file opened from any active directory it happens to reside in?

View 9 Replies View Related

VBA Code That Opens A File From A Directory

Jun 4, 2006

In the following code, What do I need to add to allow the selected file to open when I click on it? I have a drop down menu that lists my .xls of now when I choose a file nothing opens.

Sub Auto_Close()
On Error Resume Next
Application. CommandBars("Patient List").Delete
On Error Goto 0
End Sub


Sub Auto_Open()

Dim cb As CommandBar
Dim ctrl As CommandBarControl

On Error Resume Next
Application.CommandBars("Patient List").Delete
On Error Goto 0

View 3 Replies View Related

Copy Directory If File Exists

Oct 11, 2006

Is it possible to search a directory looking for a particular file, then if found, copy the directory and all its contents to another location?

it should be noted that not all of the files will be *.xls.

View 3 Replies View Related

List All Files In Directory In An Excel File

Apr 2, 2009

In my search I found several example similar to what I need, but nothing I could adapt to filt my needs, (at least not wim my limited skills).

Here what I need to do:

I have multilple Excel files in a directory. (M:/Archived PO Responses/Domestic). On a daily basis, these files are processed via VBA, and deleted after processing. What I need to do is, prior to processing and deleting these files, create an ongoing log of the filenames in that directory.


The macro would open an Excel file named "Processed Orders.xls", which is stored on the network drive "M:". It would then append all of the filenames in the directory mentioned above to a sheet named "Processed Orders" in that workbook, below any filenames that already exist.

View 8 Replies View Related

VBA - Code To Save File In New Directory Not Working

Oct 29, 2012

I have some code that copies a worksheet and saves into a new workbook and saves into a specified folder and renames the new workbook.

It is all working fine however it is saving to my desktop and not the specified directory. I know there is a lot similar out there and i have exhausted my google skills trying to find the similar format to the one i am after.


Sub MonthlyReset()
Dim sourceSheet As String
Dim clearR As Range
Dim newFile As String


View 2 Replies View Related

Loop Through Directory And Return Path File And A Value

Mar 15, 2014

I need a macro to loop through a dir and the sub directories to find xlsm files, when it finds one open, go to the sheet named data, look at c1 and if the value is between 12.1 and 13.4 then i need it to write the file path, filename to a1 in my excell sheet and then write the value from c1 in the original file to A2 of line in my excel sheet.

I have hobbled pieces of code together without any good results.

View 2 Replies View Related

Macro (VBA) To Save File To Web Directory (SharePoint)

Jun 17, 2014

I have recorded the below code that functions fine from my computer. My issue is that I need to export/import this macro to other workstations who may have the save to location mapped to a different drive letter. Is there a way to replace the drive letter with the URL (SharePoint)?

ChDir "E:3. CRQsRemedy Dumps"
ActiveWorkbook.SaveAs Filename:="E:3. CRQsRemedy DumpsToday_CRQ_8Dump.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Save To Directory: \server_1Dir_1Dir_2FolderToday_CRQ_8Dump.xls
Permissions are in place.

View 1 Replies View Related

Copy File Names ,date From A Directory

Feb 20, 2009

I need to copy the names and date modified from all pdf files in a open directory. The directory name will change month to month. I will copy the names into an excel workbook that will be opened as well called Shipped starting in sheet1 cell P2

View 9 Replies View Related

Copyrights 2005-15, All rights reserved