Is there a way to use the open dialog box (like as if you where opening another workbook) and then with the selected item inster the path and file name into a cell.
View 2 RepliesFile open dialog box, Get the path to textbox? ...
View 7 Replies View RelatedIs it possible to set the starting path for the open dialog - using expression. GetOpenFilename so that the contents of the correct folder are visible immediately?
View 3 Replies View RelatedI am trying to white a macro that takes a file path from a cell value and opens that file. When there is no file path in the cell, it prompts a dialog box. This part is pretty much working. However, I would like the file path selected from the dialog box to be added to the cell in the original workbook.
Sub Reports()
Dim path1 As String
path1 = Range("B20").Value
On Error Resume Next
'checks if there is already a file path'
Workbooks.Open filename:= _
path1, Updatelinks:=1
'if no file path then opens dialog box'
If Error.Number <> 0 Then
Application.Dialogs(xlDialogOpen).Show Arg1:="*.*"
End If
End Sub
I am trying to add a simple function to button in Excel.
First I need to select cell G24 as that where the inserted file should be placed on the worksheet. Then on the Add Object window to open but the tab 'Create from file selected.
Below is only part of the solution
Sub ShowInsertObj()
Application.Dialogs(xlDialogInsertObject).Show
End Sub
Also is it possible to detect that file has been placed on the page ? In cell G24?
I am wanting users to attach a required zip file. I then need to check if they have done so.
I am looking for code that when cmdfile is click, it will open up the 'open file' dialog box, and go to a specified directory on the pc, and list ALL files in that folder, but not open it. I want the user to select the file manually.
I found the following code on the forums, but don't know if it is relavent in my case
Const myDir As String = "C:NAVIGATIONPERSONAL
ussNLN"
I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls
So opening x.xls will automatically open y.xls
I've tried this in the workbook code area but it doesn't do anything.
[Code] .....
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
Explored a few posts on my topic, but didn't quite find what I needed.
I need to open the Insert Picture dialog with it pointed at C:My DocumentsTemp. (Users will be required to save their images files to this location only).
Attempted to run a macro to get the code, but the dialog box forces you to select a file. I only need to get some low skilled users to the folder and let them select the image to insert.
I have an application that requires users to download a file from a location that is then imported at their execution into the application. I've done my best to counsel users to download the file to their desktop (for ease of locating) and to stick with a standard filename so that there are no errors. Some of these people just don't take direction well, will save the file wherever, or save the name inappropratly, or worse, open up multiple copies of the same file corrupting the name: ie: FileName.xls, FileName.xls(1), FileName1.
View 5 Replies View RelatedHow can I change this;
Code:
Sub GetData_Example4()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant
SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:Data"
[Code] ....
So that it still prompts the user for the path but limits their selection to only Excel files and only those named 'Roles'?
I'm trying to use vba open a new workbook and then edit it. But i can not select cell in the new workbook. but it can be activated!
it can open the file (fname) and activate the sheets (sName), but will flage an error on range select.
Workbooks.Open fileName:=fPath & "" & fName
Workbooks(fName).Sheets(sName).Activate
Range("A8").Select
1 I need to open a csv file in order to use data in it at location "C:BarkingEMCIN" to input into an xls spreadsheet. I then need to use the info from the xls spreadsheet along with other info input by users to produce another .csv and it be deposited at "C:BarkingEMCOUT"
The name of the file at "C:BarkingEMCIN" will change everyday or even multiple times a day and be in the format "AVA_DA_260208_BPL_EDF_001.CSV" "AVA DA " will remain the same "260208" will be todays date the next 2 will be interchangeable depending on requirements and use BPL EDF and SSE will be interchangeable depending on requirements and 001 will be the version whcih will be changable. _ underscores will be used as seperators
They will then be save in the format "NOM_DA_260208_BPL_EDF_001.CSV" using the same prinipals as before but automatically saving as the next available when needed with a prompt to tell the user what it will be
I would like to have a macro button that will open the file location (path were the excel is stored) in MS explorer.
I found something like:
Sub ExplorePath()
Shell Environ("windir") & "Explorer.exe " & ActiveDocument.Path, vbMaximizedFocus
End Sub
But that give the error that an object is needed. Would that also work for MS Word?
I've seen many posts advocating the use of BrowseFolder.zip located at
[url]
And some others saying to use the FileDialog object (if you use Excel 2002, or older(?))
I have to admit both are above my prior experience level, but I think I can do it if I understand what their function, requirements, and output are.
All I need is a dialog box that preferably lets me select the default directory, and then allows the user to select the target directory.
I have a file which is opened every so often. This file when opened it presents a userform in which you browse two files which then get loaded into the document and some charts/pivots are updated with the information from the loaded files.
When you click on the browse button it opens the browser dialog box in the libraries path. I am wondering if it's possible to have the dialog box open in the last directory path used.
For example:
A week ago I opened the file from the path C:UsersIntiDocumentsProjectsTea Project
What I would like to happen is that when I open the file today and I click the "Browse" button (which opens the dialog box to find files in your computer) for the dialog box to open automatically in the path C:UsersIntiDocumentsProjectsTea Project
Then if tomorrow the file is opened from the path C:UsersIntiPicturesProjectsTea2Project
Then a week from now when you browse for the file the dialog box opens automatically in the C:UsersIntiPicturesProjectsTea2Project path
This is what I have right now and it always opens the dialog box on C:UsersIntiDocuments
[Code] .....
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
is it possible to configure Excel in order to save the Hyperlink path for a file with absolute path and non relative?
I notice that the hyperlink is ....pdf ry.pdf
if I change the position of the file excel there is a problem!
I would like to save es. d:invoichepdf ry.pdf
I have a variable ("DestFile") that defines a path to a file (used in saving the file)...
I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...
How would I go about trimming the "DestFile" address to get a "DestFldr" address?...
An example of "DestFile" might be;
S:BryanFor KenGulf ConstructionST0609014-t.xls
(the file name length may vary)
What code can I use to consistently trim it back to;
S:BryanFor KenGulf Construction
as the "DestFldr" variable?
I am trying to insert a link that once clicked will open a file (word, excel, powerpoint, etc. ) that has been saved on my computer.
View 3 Replies View RelatedI want to run a macro to separate File Name and Path from the given complete path
For Example
Code:
In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf
I need it separeted like
Code:
In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1
Is there any way to do this through a macro
I have an Excel sheet that contains a few thousand folder paths in the first column. The first few cells in the column look similar to below.
C:UsersNameDesktopFolder Copy12010360
C:UsersNameDesktopFolder Copy12010361
C:UsersNameDesktopFolder Copy12010362
C:UsersNameDesktopFolder Copy12010363
C:UsersNameDesktopFolder Copy12010364
C:UsersNameDesktopFolder Copy12010365
I am trying to create a macro that will copy any folders that exist in any of the paths listed in the first column. The folders should be copied to the path of the open workbook containing the macro. Below is the macro I have currently, much of which was taken from information I found in this thread [URL]....
VB:
Sub wrapper3()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("Air").Cells(x, 1) <> ""
v = InStrRev(Sheets("Air").Cells(x, 1), "")
dest = ActiveWorkbook.Path & Mid(Sheets("Air").Cells(x, 1), v, 99)
[Code]...
This code seems to work fine if all of the folders exist to be copied. My problem is that some do not and it is creating a "Path Not Found" error for which I need a fix. If the folder doesn't exist at the path nothing should be copied and the next path can be evaluated.
I am successfully opening a .csv file using a variable value stored in a named range in my Main file (the variable includes the directory and path).
I copy data from the .csv file to the Main file then I need to close the .csv file without saving but I want to do that by using the
Windows("xxxx").Activate
command where "xxxx" is the namedrange in my Main file which stores the .csv filename (without the directory and path prefix).
I can use the
ActiveWindow.ActivatePrevious
command but if I have another workbook open, this one closes instead of the .csv file I opened from the macro.
I realise this is probably very basic and I've searched the forums but can't find any identical postings.
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies View RelatedI have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another.
e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like.
I think this is possible but cant find anything on it ........
View 9 Replies View RelatedI have a report that is collated from several other workbooks. The source workbooks follow the same naming convention throughout the year and so I have managed to concatenate the file path to bring through the text of the filepath and cell reference however cannot work out how to get the value of the cell. For example, I have the file path in A1, file name in B1 and sheet and cell referernce in C1.
I am using {=(A1&B1&C1)} which gives me R:CONFIDENTIALREPORT FOLDER[REPORT 24.09.07.xls]Sheet1!$D$1. To make matters slightly more difficult, the source reports would be closed. Is there an easy way or am I going to have to do this in VBA.
I attached a file with macro7 that prompts the user for text that will get rotated and inserted into the top left corner. The text is in the form "batch xxxx." I need a carriage return between the word "batch" and the numeric string. I guess the input box should have two lines instead of one.
the code does some other stuff which is why it takes a while to execute ....
Quick VBA question: does anyone know how to let VBA open the Insert symbol dialog box?
View 9 Replies View RelatedI would like a macro to print 2 sheets in a workbook. I can write the macro that will select the sheets I want, but I don't want it to just go and print because depending on if the user wants it printed in color, b&w, etc.
they have to choose a different color. What code do I use to open the print dialog box?