For a macro i'm writing I need to search for a directory.
Users enter a projectnumber, this is being used for a search string.
But how can I search for folders/directories in Windows?
I've got the startfolder: 'C:Projects'
Then the project which the user is searching for can be in several subdirectories.
I got a directory with several xls-files.
I want in an new excel file to search automatically for all strings (11 till 48) in all files this particular directory.
All the strings (11-48) are being placed on this new file in worksheet 1.
I am trying to find code that locates a workbook (file) in a specific directory, based on a keyword and stores the workbook location in a string to be opened later.
For example, find a workbook containing the word "ancaster" in it's file name (actual file name is "ancaster_summary_2009") in file path:
"C:My Documents" and store the filename and path in a string called "ancasterBook"
Need macro to search approx 1000+ xls files in folder/directory for common text string "see reference" and then output the file number which is located in cell A1 to new spreadsheet for each file the text "see reference" is found.
View 2 Replies View RelatedHere is a project I can’t seem to do on my own if you can point me in the right direction I would be grateful! here is the best suedo code to describe the needs of the script:
In the open workbook named MASTER, on Worksheet named -MASTER- ( Let’s refer to this all as just MASTER)
(In production, the name of this workbook will be an account #_ date, and the worksheet will be a date)
For each numeric value in row 6 (we’ll call these values AD###) of MASTER
'*****Part one of routine*****
Search directory "H:AccountingAdvertising Accounts" for workbook named AD### AX.xls
(Note the AX suffix of the file name)
If match is found open workbook and proceed to Part two
If match is not found, goto Private Sub AD_MISSING
'*****Part two of routine*****
The script collects all the files in directory and sub-directories and list them in ascending format, I want them to get in transpose format. like for example: Root folder has many sub directories and in them a sub directory XYZ has 5 excel files, it will get the sub directory name in Col A and transpose all .xls files.
Col A | Col B________|Col C_________ |Col D___________|Col E________|
XYZ__|C:/root/test.xls|C:/root/Sales.xls |C:/root/Report.xls |C:/root/sam.xls|
[URL]
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 RelatedThe command Application.GetOpenFilename will open a explorer to be able to choose a file, what is the command to open a explorer but only to choose a directory?. Is there such a command?.
View 7 Replies View RelatedIm am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
The Kill function works OK but the ThisWorkbook.SaveAs part does not work for some reason.
Please note that there are 2-spaces between the words: P3 CATS
1. Is it possible to save additional worksheets (right now it is only copying one worksheet)?
2. How can I hard-code the directory path so the file goes to that one locatin (i.e. the "master directory")?
Also, is it possible to save userforms out of this workbook (i.e. some of the userforms contain information that is useful...it would be great to have them in the "output" file that gets saved to the "master directory"). Thanks!
Private Sub SAPfile_Click()
myfile = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls")
Worksheets("Output-SAP Plan").Copy
ActiveWorkbook.SaveAs myfile
ActiveWorkbook.Close
MsgBox "SAP file created."
End Sub
I've been looking for code to print Excel files from a directory with multiple worksheets. I found something close on this forum, but it prints 2 copies which is fine for pdf, but not for hard copy. I would like just 1 copy.
Sub PrintPDF_PLOTDIR()
Dim PATH As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application. ScreenUpdating = False
PATH = "C:DATA\_PLOTDIR"
FileName = Dir(PATH & "*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=PATH & "" & FileName)
Application.ActivePrinter = "Adobe PDF on Ne00:"
For Each WS In Wkb.Worksheets
ActiveWorkbook.printout Copies:=1, Collate:=True
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
I'm from Bavaria, Germany. Right now, I am doing an internship for my studies. my problem: I need a search procedure which shows an Input Box where you can enter a word to search for. It should work like the original Excel search (Ctrl-F), but with a simpler design, like with my own Text "Enter your Query" and a Button "Submit Query" / "Quit search". Is there the possibility to Highlight the Search Target? The problem hereby is that this highlighting should not be permanent. That means the user sees the target for which he searched for, the cell highlighted in a different color, etc. But as soon as the user clicks onto another cell, etc., the highlighting vanishes. If there is no fitting match, there should be a MessageBox like "Sorry the Target xyz cannot be found"
View 9 Replies View RelatedWhat i have so far is:
-3 Sheets (Sheet1 - Search, Sheet2 - Data, Sheet3 - Result)
-Sheet1 has a userform ready to link up - this opens up when workbook and worksheet is activated.
-Sheet2 has some example data to search - the search is for column 1 (see attachment)
-Sheet3 has a template ready for the data to be pasted to.
I would like the search result is column 1 to copy the data from columns 1 to 7 to sheet3.
I want the template/layout to remain after the pasting of results. (everything protected from editing, if possible).
Once finished (ie go back to sheet1, i want the data from sheet3 to be cleared.)
I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.
I can get it to work to search one cell (as below):
=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)
(In this case the respone is No Change as Cell G10 contains "Same")
However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:
=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)
Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.
Imagine this:
* Main Category #1
Sub Title #1: "Value"
Sub Title #2: "Value"
Sub Title #3: "Value"
Sub Title #4: "Value"
* Main Category #2
Sub Title #1: "Value"
Sub Title #2: "Value"
Sub Title #3: "Value"
Sub Title #4: "Value"
Sub Title #5: "Value"
Sub Title #6: "Value"
Sub Title #7: "Value"
Sub Title #8: "Value"
Sub Title #9: "Value".................
Main categories and subtitles are in the same column. "Value"s are in the column to the right.
I want to make a list in the columns to the right. This list will only show something if the "value" is not zero. It will show in the first column right from the original list, the main category to which the subtitle belongs to and the subtitle. Something like this (imagine subtitle #2 from cat #1 and #2 and #3 from cat #2 are not zero):
I want to have a excel file with VBA code and it should operate like the following:
It should check the subdirectory's in a certain directory and put this on a excel sheet.
Every time when you start the sheet it should compare the sheet with the sub directory's and make al the changes in the list.
In the end the excel sheet should be a representation of the directory.
I can't get it to work when something is changed or a directory is added or deleted.
[Code] ....
An already existing file will be changed by my macro and at the end I want the user to save the file with a different name in a different directory with the option to change the filename or directory. Therefore I use the excel-standard-dialog "save as". But I want the dialog to start in another path as the path the original-file is stored in. But also I use the ChDrive and ChDir Parameters the dialog even starts in the original-file-path. I think this is standard for the dialog and I should maybe use a parameter in the show-arguement? The code is below.
Option Explicit
Sub SaveMyFile()
Dim xFileName, xAnswer
ChDrive "J"
ChDir "J:myfolder"
xFileName = "testme.xls"
xAnswer = Application.Dialogs(xlDialogSaveAs).Show(xFileName)
End Sub
I had posted earlier and felt I had solved my problem, but I have not. What I am trying to do is use code to change directory when the save menu is activated.
I want to be able to open from one location and then it defaults to another. I have tried "chdir" , but I can't seem to get it to work. Even when I change the DefaultFilePath, the "save as" location defaults to the location where the file was opened from. I want to default all "saves" in a specific location without using the menu to select.
Is there a dialog box like the one used in GetOpenFilename that can be used to select a directory?
I'm trying to select a directory/folder which will change each month but the macro selects the files from inside the folder once it's specified. I don't need the name of the current path; the path name needs to be able to be selected.
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 RelatedI have been limited lately to only working on this project at work because I have added a few lines here and there throughout the workbook that make a reference to the directory the file is located in. for example:
View 2 Replies View RelatedI have multiple VBA modules that all refer to directories on the local machine and across the network.
I am currently defining the directories in each module.
How can I define them once and then refer to them in any module?
Set Word_Template_Name = "Sanitary Survey Template.dotx"
Set Excel_Template_Name = "Sanitary Survey Blank.xlsm"
Set WQCDdir = "c:CO Sanitary Survey"
Set WQCD_Source_Dir_File = "...wqcProgramsSDWEngineeringTemplates"
This would ensure that any directory changes could be made once rather than 5 times.
Here is the code:
Code:
Option Explicit
Option Compare Text 'for Case-Sensitive matching change Text to Binary
Sub List_Matches()
Dim sPattern As String, sPath As String, sJob As String
Dim sMainDir As String, sCommonSub As String
Dim c As Range, lRow As Long
Dim d As Range
Range("B:B").Clear
lRow = Cells(Rows.Count, "E").End(xlUp).Row
If lRow < 8 Then Exit Sub
[Code] ...........
The red is the part that I tried to manipulate to add the new directory, the problem I can see is that the new directory contains folders with the following name "WO#____" as i can see the # sign is probably throwing the program off because it can't open up the link to that folder in order to look for files. The program as it stands still works fine with looking up the first directory. I also know we had created a function to solve the sign problem for directory 1 file names. How can i use that function to directory 2.
I am trying to automate some of the work I used to do as I have moved to another position, specifically trying to automate file management. I am using this code to create new files for each client
Code:
Public Sub CheckDir(fold1 As String)
If Dir(fold1, vbDirectory) = "" Then
MkDir fold1
Else
End If
End Sub
which works great, but you still have to do one client at a time. Basically I'm using this code to create new client files, but each year we will need to add a directory and sub directories for every client in a certain directory. What I would like to do is code a macro that would add say a "2013" folder with all appropriate sub directories to every client folder within the folder that contains all of the client folders The folder hierarchy should look like this
Code:
'F:Clients
'F:ClientsClientName
'F:ClientsClientName2013
'F:ClientsClientName2013RandomOtherFolders
I assume it would use a For/Each, but I'm not sure how to go about it.
How can i loop through each excel file in a directory.
View 1 Replies View RelatedI am trying to use VB to save a workbook into the same directory but with a file name that references the folder it is stored in...if that makes sense!
Here is where I have got to so far but fails on save,
Sub Rename()
Application.DisplayAlerts = False
mdy = Month(Now()) & "." & Day(Now()) & "." & Year(Now())
fold = ThisWorkbook.Path
FName = fold & "-" & mdy & "-(New).xls"
SaveName = fold & "" & FName
ActiveWorkbook.SaveAs (SaveName)
Application.DisplayAlerts = True
End Sub
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.
