GetOpenFilename Partial Filename Filter
Jun 18, 2006
I 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.
View 3 Replies
ADVERTISEMENT
Oct 7, 2008
I'm attempting to copy some files into an archive folder in the same main folder. These files appear weekly in a folder that contains numerous other files.
Each of these files has a filename such as:
RAH_MRL_TOP_40_(ddmmyy_TIME)
with time being the time that the report was run.
Every week the filename stays the same but the time and date changes. I was hoping to be able to simply use the "Filecopy" command and specify the source file with a "*" after the main file name. However this does not seem to work.
Is there a simple way to do this? (I dont want more than a few lines of coding really as I will be duplicating it for each file and then changing the file name as needed.
View 9 Replies
View Related
Nov 21, 2008
I am trying to set a file name as a string to use it as an attachment. The user inputs a number into a textbox. That number is used to locate the file in location. But the file name is different with each file. So im trying to do something like
"C:Desktop*TextBox1*.docx"
Whereas the actual file name is "C:DesktopMCJC RFI 1 MISSING ROOM NUMBERS.docx"
The location "C:Desktop" will always remain the same as will ".docx". I have everything else working just fine.
View 8 Replies
View Related
Jul 31, 2014
I'm currently creating E-mails containing a table of material numbers in the body of the message. The E-mail is generated with a macro from a table in excel. In some folder - say on the desktop - I have PDF files for each material Number. Each file is called 0012345_Product-Name.pdf where 0012345 is the material number (always 7 digits). I want the macro to look for the pdf for each material number found in the table and add it to the E-mail as an attachment. With constant, known file names this wouldn't be much of an issue. However, I'm having trouble getting the macro to only search for the first 7 digits of the filename and select it based on that.
Since we are talking up to several 100 PDF's, it would also be great to zip them, once selected - if at all possible, before attaching them to the E-mail
View 2 Replies
View Related
Apr 25, 2014
Is there possible filter a partial number in column?
i have many values in column, i want enter partial value in Texbox (ActiveX).
if i enter in textbox 6840.
Result
64801000
64802000
64803000
##############
if i enter in textbox 5480.
Result
54801002
54802002
54801000
54802000
54801001
54802001
54801005
###################
View 6 Replies
View Related
Apr 16, 2013
I am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
View 1 Replies
View Related
Jul 25, 2006
about 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)?
View 5 Replies
View Related
Nov 16, 2008
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:
View 4 Replies
View Related
May 20, 2014
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP
View 11 Replies
View Related
Sep 10, 2009
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 Replies
View Related
Jan 21, 2010
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 Related
Jan 6, 2007
I 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".......................
View 6 Replies
View Related
Oct 29, 2008
When 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?
View 12 Replies
View Related
Oct 6, 2009
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
View 2 Replies
View Related
Nov 29, 2009
I have several buttons on my userforms that use the following ...
View 12 Replies
View Related
Nov 4, 2006
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
View 9 Replies
View Related
Mar 27, 2008
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)
View 3 Replies
View Related
May 30, 2008
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".....................
View 3 Replies
View Related
Apr 4, 2009
Need the code needed to call the following code from an insheet button.
View 2 Replies
View Related
Sep 17, 2008
I 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 ......
View 9 Replies
View Related
Feb 13, 2010
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 Related
Jul 7, 2006
I am trying to display the Filename in a cell on my worksheet. However, when I enter the =Cell("filename"), it provides the full path (C:/Documents......Cost Summary.xls) How can I display only the filename without the path?
View 3 Replies
View Related
Feb 10, 2008
How can I FILTER a range and display the unique items, one below the other, WITHOUT blank cells - with only a FORMULA. What I came up with is shown in the attached WB. I would like to present the countries like in C11:C15.
View 5 Replies
View Related
Oct 5, 2013
I have a database in Excel 2013 and now I want that when a value (a person's name) is entered in a cell. That then the database sort of filters the list for me, so it's still possible to make changes in the entries.
[URL]
Picture above to specify the search, which I would therefore like to edit
Dashboard_Action Pool Team 7.2.xlsm
I have been all morning working on a simplified version of the tutorial from YouTube: Create your own Excel Search Pt. 4. But came back later so only then that I can not change the data:?
View 2 Replies
View Related
May 1, 2014
I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
[Code] .....
Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...
View 4 Replies
View Related
Feb 20, 2009
How to automatically filter can filter and blue-colored cells.
View 4 Replies
View Related
May 25, 2012
Writing code to do the following:
Save advanced filter settings
Remove filter (or simply set to be 'select all'
Run other code (I have this piece of course)
Put filter back on with same selections chosen as when it was removed
(Need this becuase the code in the middle does not work properly when the data is filtered)
View 1 Replies
View Related
Dec 23, 2013
I just got into the world of PowerPivot, Excel 2013 and Pivot Tables and am in the process of creating a Dashboard which I will then be uploading to SharePoint 2013.
On top of page I added the new timeline filter which I've linked to my pivot charts. Now what I would like to do, is create an additional pivot chart which looks at whatever date range has been used in the timeline filter and subtract 5 years from that. So, when I select a date range of November 2013 - December 2013 in timeline filter, the additional pivot chart will show the details for November 2008 - December 2013. This is where I get stuck.
I'm using two SSAS cubes which I'm combining together in PowerPivot and then display in Pivot Tables and Pivot Charts.
View 1 Replies
View Related
Oct 29, 2009
I have a date of birth in cell "B13". It's formated as xx/xx/xxxx. I'd like to copy this into another cell where I have annual holidays listed, but, I'd like to have it formatted as mm:dd - omit the year.
View 2 Replies
View Related
Nov 22, 2006
I have a cell that contains a string which is always formatted the same. "Dated between Date1 and Date2" I would like to put Date2 into a variable; however, I don't know how to get just that part of the cell.
View 9 Replies
View Related
