I have the following code, is it possible to direct to a default directory that the file might be in? So when I open the dialog box it will automatically redirect a directory that is stored in VBA.
View 4 RepliesWhen I use the GetOpenFilename() method, it seems to default to the particular users My Documents. Is there a way to force it to default elsewhere like a share drive, or the users desktop?
In the same line of thought, if I have a file named TestFile.xls. and I want to try to open it from the users desktop if they have it, how can I do this, since their desktop location is different than mine?
I have had my server removed that I had my default file location, when I go in to change any of my options the first thing it does is go look for this file location and brings back an error stating it cannot find the server. I cannot find anywhere else that I could change it.
View 9 Replies View RelatedWhen someone right clicks on a cell and chooses "Hyperlink" current folder is being displayed as a default.
is there a way to change this for a particular workbook through VBA or API calls ?
I would like to always have "c:" as the default folder no matter where the workbook is stored.
On my user form, the cursor blinks in the second text box down.
How do I change it to be blinking in the first (top) text box? This is the first box that data will be entered.
Is this an option in the properties box of the textbox?
I'm using Excel 2010. When I go into the Excel Options, to the Save option and try to type in a specific network drive in the Default File Location: box, I click OK and then it doesn't save the changes. I close Excel down all the way and then restart a new Excel session and it keeps going back to "LibrariesDocuments..."
It doesn't seem to be just related to Excel...having the same issue in Word and Access 2010 as well.
Is there any way of making the default directory location for application.getopenfilename work properly with shared locations?
Presently I use ChDrive then Chdir but I cant use ChDrive if workbook is opened from a link in email for example. Please see below
I have a VBA routine that asks the user to load an existing file on startup.
I want the window for the file selection to open at the same folder every time. (the folder is on a shared drive)
The workbook to run the code will always be located on this same share at a fixed location.
My present code builds the path to the desired opening folder by collecting the drive letter from the current working directory (which has to be done as everybody maps there shares to different letters here, its not guaranteed to be the same! )
then i ChDrive to this letter.
Then I ChDir to letter plus myKnowndirPath
This works fine, the window always opens at my required folder. But only as long as the user has navigated to the folder containing the spreadsheet and then ran it from there. Because that way a drive letter is available.
However i found that if i sent a link to the workbook or its home folder. And the user opens the link or a windows explorer from that link and then runs file from there.
Then it stops working beacause there is no local drive letter in the working directory path. i.e. working directory shows as
'network \shareddrive
ootafolderanother arget
instead of something like
J:afolderanother arget
I use an image analysis program ImagePro which can call Excel within it's macro language (which seems to be visual basic). I looked up on this site how the change the drive (ChDrive command). But still when the Excel section (after With oExcel) executes the default file location in the browser is in My Documents on the C: drive.
Prior to this code Excel has been launched by this ImagePro macro, and a file Cumberland Template has been opened from the C: drive. Now I would like to do a Save As, but have Excel start at the I: drive location.
I have a spreadsheet that from a button I want to run a macro that will input todays date, the value in cell A1 as the filename into a default dialog box that is at a default file path. I have been trying to do this for several hours and can not completely get it done.
View 9 Replies View Relatedabout the " getopenfilename" command.
1 - What exactly it can do?
2 - How can I work with it (like, to use the path of a file opened by it)?
Is it possible to disable The "Look In:" field of the GetOpenFilename dialogue?
What I would like to do is to keep users from selecting folders other than the CurrentDirectory settings and if possible to keep the user from deleting,copying and pasteing to the files in the current dirrectory displayed. The code I have is:
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.
simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
I want a macro that run the application GetOpenFilename (or something that is similar) but when I push the OK button, i don't want to open the file, I just want the filename of the file I have browsed to.
View 9 Replies View RelatedI have searched for the Runtime Error 13 in the archives and tried a few things that was suggested such as: If I change the Variables Dim FName as a String for instance it then errors in the middle bold area "If IsArray(FName) Then" as a Compile Error - Expected Array. I tried deleting the Option Explicit but that didn't do anything.
If I comment out the If FName = "False" Then routine it works.. however *that* is in there because if the user hits Cancel on the Getopenfilename box, it will just continue with the rest of the process. If the user hits cancel, I want it to stop, but since this code is in a module, it will kick back to the calling Userform sub and thats how it continues to run. The label caption change is setting a label caption to "cancel" if cancel was selected on the get openfilename dialog to exit out of all the UserForm Subs, Is there a way to pass a variable from a module to a userform?
Option Explicit
Sub GetData_Example5()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant, N As Long
Dim rnum As Long, destrange As Range
Dim sh As Worksheet
Dim wsNew As Worksheet
SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _
MultiSelect:=True)
If FName = "False" Then
' They pressed Cancel
' Set Label Caption as Cancel so rest of routine can be Canceled
UserForm14.Label24.Caption = "Cancel".......................
The Macro asks the user to point at the location of a report, it then copies information out of that report and pastes it into a master sheet. The part I am having trouble with is closing the file that data has been copied from.
I have tried different things to close it such as
I have several buttons on my userforms that use the following ...
View 12 Replies View RelatedI am using the GetOpenFilename method to allow the user to select a file to open:
vaFiler = Application.GetOpenFilename _
(FileFilter:="Adobe PDF Files (*.pdf),*.pdf", _
title:=Description, MultiSelect:=False)
What I would like to do is 'pre-load' the filename the user can select. They will be picking a file to attach to a part as a datasheet. Almost every single datasheet in the library has the part nuber as part of it's filename, so to make it a little quicker (and more accurate), I want pre-load the filename selection window with *partnr*.pdf. If nothing shows up, the user can delete filename I have entered for them and see all files in that directory.
I copied below code from one of Andy Pope's thread answers. Thanks Andy.
Private Sub CommandButton1_Click()
Dim vntFile As Variant
vntFile = Application. GetOpenFilename("Graphics Files (*.bmp; *.gif; *.jpg; *.jpeg),*.bmp;*.gif;*.jpg;*.jpeg," & _
"All File (*.*), *.*", Title:="Select Picture")
If vntFile <> False Then
ActiveSheet. Cells(27, 1).Value = vntFile
Image1.Picture = LoadPicture(vntFile)
Image1.PictureSizeMode = fmPictureSizeModeStretch
End If
End Sub
I would like to force the box to open in Views - Preview instead of List or Details or whatever it is at. I tried the following (and a bunch of other things), but I could not get it to work
With FileDialog
.InitialView = msoFileDialogViewPreview
End With
I'm having problems with the multiselect argument of the getopenfilename function. I've used this dozens of times with no problems, but now it doesn't work for me. I tried copying and pasting code that works in one module,
What I'm using is
Sub OpenFiles()
Dim vFiles As Variant, iNumfiles As Integer
vFiles = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(vFiles) Then
For iNumfiles = LBound(vFiles) To UBound(vFiles)
MsgBox vFiles(iNumfiles)
Next iNumfiles
End If
End Sub
pretty simple, expect that when I select multiple files in the open dialog box, vfiles is a string containing one of the filenames, not an array containing them all! (by selecting, I simply highlight all the fiels I want and click open)
I'm writing a subroutine that asks the user to pick a file (using GetOpenFileName) and extracts some data from it, then closes the workbook it opened automatically.
Problem is that it seems I can only reference workbooks by index, which is a big problem if they have more than one workbook open. I don't want to have to force them to only keep 1 workbook open, that seems like bad practice.
importFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select field sheet")
If importFile = False Then Exit Sub
Workbooks.Open importFile ' Open the new workbook
importFile ends up returning something like, "C:Documents and SettingsMy DocumentsFieldBook.xls".....................
Need the code needed to call the following code from an insheet button.
View 2 Replies View RelatedI am opening the file through the GetOpenFilename method. I am facing an error in of the cases... Like say or example i try and open a file with the XYZ.xls...which is already open. the system generates a mess saying
"reopening will cause any changes you made to be discarded. do you want to reopen XYZ.xls?"
if i click yes...it works fine by reopening the file but when i click to No...Runtime error 1004 comes:
"Method open of object workbooks failed "
and its giving an error here in the Workbooks.Open Filename:=sFilename ......
I am trying to use Application.GetOpenFilename to search for a several (.jpg) files and list the file paths on a worksheet.
View 2 Replies View RelatedHow can I force Excel to enter the time as PM instead of AM in a cell without typing PM or using military time? Is there a custom format that will do this?
View 9 Replies View RelatedI had set up a long time ago a macro enabled sheet in the XLSTART folder and Template folder. It used to work where any file I opened, if I clicked new sheet, the data from that file would be in sheet2. Now for some reason it's not showing up anymore and just a blank sheet appears.
View 2 Replies View RelatedHow can I set the value of a cell to give the current date when that cell is clicked or given focus. I want A5 to show as blank, nothing there, until I click the cell, or somehow select the cell, and then have it show the the current date. If I want that date I hit tab or enter and it will record it.
If I don't want to record anything i can arrow past it and it will not record anything. If I want to change it I double click or click again(If I'm in the cell already) and I can type over or just put in the date I want.
However If the user records a date in that cell and goes back to that cell do not record the default current date on tab or enter it most be double clicked in order to change it. This is a safety that they don't tab to it and past it and have it changed by mistake. Once a date is recorded they have to double click to change it.
This is not for just one cell but for the range of cells in colum A starting on A5.
I need an assistance in setting up a VBA for the default printer in my office. The issue is I want my spreadsheet to be set up to wherever I open the spreadsheet at any computers in the facility. I want the sheet to be printing out directly at the printer in my office only. Is there a way to do this?
View 9 Replies View RelatedNetworkdays on default takes saturday as a holiday. Is there a way I can undo this and have only sunday as a holiday while calculating net working days.
View 9 Replies View RelatedI want the combobox cmbBottle to show the second item in its list when the userform shows. The code below achieves this. However, after executing cmbbottle.listindex=1, the program jumps out of the initialization and goes to Private Sub cmbBottle_change() and then executes others subs called under it. I don't want this.
Is there a way to set the combobox to a default, startup display, without triggering the cmbBottle_change event?
(I have looked under the Properties window of combobox in the form view mode and can't see how to set it's value there.)
Private Sub UserForm_Initialize() ...
As per title, I have both Excel 2003 & 2007 installed - I need to set 2003 as the default for opening xls files when double-clicking but can't seem to override 2007 which wants to hog all the action. I've been through the File Types menu and selected the 2003 version of Excel as the default, but it doesn't make the slightest difference.