Search Folder And Subfolders, Open Workbook Macro
Jun 5, 2009
i am trying to open a get a code working that will enable me to enter a file name in a cell of the current workbook open, then search through a root directory and subfolders, find the file and open it. the code below i giving me grief. i am using 2007 excel.
With Application.FindFile
.NewSearch
.LookIn = "J:IsolationDataBaseIsolationProcedures"
.SearchSubFolders = True
.Filename = Range("N2").Value
.MatchTextExactly = True
View 9 Replies
ADVERTISEMENT
Apr 8, 2014
Would like a macro that will do the following:
- Go into a specified folder (ie. C:data) which contains multiple workbooks
- search each workbook for a column titled SCIN
- copy the cell labelled SCIN and 60 cells below it to a new workbook (summary workbook)
- repeat the process in all the workbooks until all of the SCIN columns have been located and copied into the summary workbook
View 14 Replies
View Related
Dec 20, 2007
I have a macro that is designed to paste a row of formulas into each Subtotal line on a 30,000+ line spreadsheet. I have specified the formulas that I want to paste in the named range "formulas". The macro below worked fine for about 3000 lines, then threw the error Run-Time Error 1004: Paste Method of Worksheet Class Failed on teh line ActiveSheet.Paste. I thought it had just bogged down my computer, so I added teh ScreenUpdating and Calculation lines, but they made no difference. I also tried reducing the range to just a couple thousand lines, which also made no difference. I also have found some suggestions on this forum and others for this error.
In addition to it working fine for 3000 lines, then failing, there are a couple other weird things. One is that on the line in the spreadsheet that threw the error, the formulas were pasted in up to column AX, leaving columns AY through CR blank. The second odd thing is that this exact same macro still works on my sample data spreadsheet (which only has around 50 records).
Sub FormatTotalRows()
Dim rCell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Workbooks("latest.xls").Activate
Range("formulas").Select
Selection.Copy
For Each rCell In Range("A3040:A5000")
If Len(rCell) > 0 Then
rCell.Activate
ActiveCell.Offset(0, 2).Select
ActiveSheet.Paste
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
View 2 Replies
View Related
Oct 5, 2007
I don't seem to be able to combine the looping through a set of workbooks with the IsFileOpen function posted by microsoft.
Basically this code works if the foundfile(j) is closed, but if it is already open I cannot activate the foundfile(j) and make it do things.
I'm not posting the ISFileOpen function because already present inside the forum and the rest of the code because it's too long...
With Application.FileSearch
.NewSearch
' . ScreenUpdating = False
.LookIn = MyPath(40)
'* represents wildcard characters
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then 'Workbook exists
For j = 1 To .FoundFiles.Count
If IsFileOpen((.FoundFiles(j))) Then
'neither of these three options work
View 4 Replies
View Related
May 9, 2013
I have a problem with getting the list of files in subfolders. this code is writing the names of files in a folder to column "A". But it is just "target" folder. But i need to get the name of all files not just under the target folder but also subfolders of target and even subfolders of subfolders of target
VB:
Dim FSO As Object
Dim SourceFolder As Object
Dim FileItem As Object [code]....
View 9 Replies
View Related
Sep 23, 2009
I am totally new to both EXcel 2007 and Programming however i would really like to create a main folder using coloum A and then a subfolder within the main folder just created using coloum B.
I have looked on line and found some code but when i put/assign the code to a button it never appears to work.
View 12 Replies
View Related
Apr 25, 2013
I tried to search for threads about opening files from multiple subfolders but the following codes does not open files. Could someone please check and edit the code that I have below:
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
[Code] ........
View 7 Replies
View Related
May 12, 2014
I found some code that works well to index a folder and all sub-folders for file names.
The issue is that the folder used is coded in an excel spreadsheet (cell C7) in the below code and whether to look in sub-folders (cell C8 - True or False).
Code:
Sub ListFiles()
iRow = 11
Call ListMyFiles(Range("C7"), Range("C8"))
[Code]....
I have tried to incorporate the browse for folder and select if sub-folders are used, but it isn't working.
View 1 Replies
View Related
Aug 5, 2009
I'm trying to make a macro that will search a folder and move all files from 2008 into another folder. So far it can do this, but what i need is for it to search subfolders also. At this point it moves all files from the main folder, but nothing from the subfolders.
Option Explicit
' Reference site [url]
Sub Copy_and_Rename_To_New_Folder()
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim PathExists As Boolean
Dim objFile As File
Dim SrcFolder As String
Dim DestFolder As String
Dim x, Counter As Integer, Overwrite As String, strNewFileName As String
Dim strName As String, strMid As String, strExt As String
Dim LastModYear
View 5 Replies
View Related
Nov 6, 2011
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
View 3 Replies
View Related
Feb 13, 2008
I would like to write a mocro which open the search files in a folder window. The window will get parametrs through the macro and should show the results.
I tried to record it from file menu, but it didn't work.
View 9 Replies
View Related
Jul 19, 2012
Please look at the code below...need this to open and run FIXId macro, save and close then loop through all files in the specified folder.
Code:
Sub CorrectID()
Dim Wb As Workbook, sFile As String, sPath As String
Dim itm As Variant
Dim strFileNames As String
sPath = "C:UsersXXXDocumentsFlash Repots2012"
sFile = Dir("C:UsersXXXDocumentsFlash Repots2012" & "*.xlsx")
[Code] ....
View 7 Replies
View Related
Oct 4, 2008
need a macro that will open the following folder
c: my documentskatyexcel
View 9 Replies
View Related
Jun 9, 2008
I have a Userform with a textbox in it. The user is asked to enter a serial number and press Enter. When the Enter key is pressed, I would like to search for that serial nember in "SourceWorkbook.xls", Sheet1, in column A (or B). If the Serial number is found it should be placed in "DestinationWorkbook.xls", Sheet1 Cell A1 etc. If no serial number is found I would like to display a message about it it. The workbooks are already opened.
View 2 Replies
View Related
Jul 16, 2014
The macro works when I have my user name in the path but when I try to insert an environment it does not work.
View 3 Replies
View Related
Oct 19, 2012
I just need a macro to open any excel file may be 2003, or 2007 from a folder.
I did tried Dir but not working. I want the macro in 2007.
View 4 Replies
View Related
Oct 11, 2011
Im 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]....
View 1 Replies
View Related
Sep 9, 2013
Its been a while since I did this on 2003 and needing a pointer on 2007.
Just needing a simple macro that opens the relevant folder from a hardcoded path to allow the user to manually select the file to be used.
This file will then be used to copy from and paste to another file.
View 8 Replies
View Related
Sep 27, 2006
I did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
View 3 Replies
View Related
Aug 4, 2009
I'm trying to figure out how to run the following macro on every workbook in a folder.
Sub SumsSheets()
Dim ws As Worksheet, LR As Long, i As Integer
For Each ws In ThisWorkbook.Worksheets
View 9 Replies
View Related
Dec 7, 2012
Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
View 2 Replies
View Related
Apr 8, 2013
Is there a way to run a macro in an open workbook when the workbook becomes activated or deactivated? So if I have workbook A open with a macro in it and workbook B open as well, can I have the macro run on workbook A when that becomes the workbook I see on my screen?
View 2 Replies
View Related
Apr 1, 2014
I have this code which works but the first 2 entries that the macro lists in cell A1 is . and cell A2 is ..
[Code] .....
View 3 Replies
View Related
Sep 14, 2009
I have a code that will write all filenames in a selected folder and all subfolders in a list. The problem is that when i open a folder with about 5000 files in only 187 are visible in the list. You could see that the program writes all filenames but it writes everyting in the first 187 columns over and over again. Here is the
View 5 Replies
View Related
Jan 9, 2013
I'm using this piece of code to go through Folders and their Subfolders. It works quite well in going through the first folder and its sub's but does not seem to generate a full list.
I am looking for specific files in these folders, and depending in what folder I place the file, it may or may not detect it. I require the code to go at least 3 levels deep (sometimes more).
FolderName = "C: est folder"
ProcessFiles FolderName, "*.xls"
Code:
'~~> This function was taken from
'~~> http://www.vbaexpress.com/kb/getarticle.php?kb_id=245
Sub ProcessFiles(strFolder As String, strFilePattern As String)
Dim strFileName As String, strFolders() As String
Dim i As Long, iFolderCount As Long
[Code] ......
View 3 Replies
View Related
Apr 6, 2013
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
View 7 Replies
View Related
Apr 21, 2014
I have up to 50 workbooks in one folder with data in a specific range. I also have one workbook which includes additional data, including conditional formatting and dropdowns. I need to copy the desired range from the first workbook in the source folder to the second workbook, then save the latter to my destination folder, using the same name as the first. I need to repeat this process for all workbooks in the source folder.
View 2 Replies
View Related
Aug 10, 2007
I've been working on a macro that opens me up to a certain path so I can select a file.
This is the path sofar:
TheFile = "\datawhse
ootLAW81LAWSONprintRBLEVINS2anrvwfins1"
My code takes me there, but I want to go a step further if it is possible.
\datawhse
ootLAW81LAWSONprintRBLEVINS2anrvwfins1THE NEWEST CREATED FOLDER"
View 9 Replies
View Related
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
Feb 8, 2013
My problem is to create a macro that will list all folders, subfolders and files in an order in excel. So that I know which folders and files belong to which main folder, more like a hierarchy.
For example,
Sourcefolder
Subfolder 1
File1
I have code that list all files in folders and subfolders, but it does not put them in a hierarchy fashion.
View 8 Replies
View Related
