Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Search macro for greater than 100000 on multiple CSV files
Hi,
i am trying to create a macro, which will run through a folder of different csv files and search for values of 100000 and then copy the entire row into a new workbook, which ideally will get updated every day. is this possible? as the files have different names which is one area i struggle with and i struggle with copying the data into a standard workbook (which can be in another folder if need be) thanks Dirk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search macro for greater than 100000 on multiple CSV files
Dirkmiller wrote:
i am trying to create a macro, which will run through a folder of different csv files and search for values of 100000 and then copy the entire row into a new workbook, which ideally will get updated every day. is this possible? as the files have different names which is one area i struggle with and i struggle with copying the data into a standard workbook (which can be in another folder if need be) As with everything else I post, this is horribly inefficient and probably not the best way to do this... but it works. (For me, anyway.) Sub findInFiles() Dim tmp As String Workbooks.Add Const VALUETOFIND = "100000" backdir = CurDir$ ChDrive "X" 'only needed if the CSVs are on a different drive ChDir "X:\directory\containing\the\CSV\files" curfile = Dir$("*.csv") While Len(curfile) Open curfile For Binary As 1 tmp = Space$(LOF(1)) Get #1, 1, tmp Close 1 'check if VALUETOFIND is in the file If InStr(tmp, "," & VALUETOFIND & ",") Or _ InStr(tmp, vbNewLine & VALUETOFIND & ",") Or _ InStr(tmp, "," & VALUETOFIND & vbNewLine) Or _ (Left(tmp, Len(VALUETOFIND) + 1) = VALUETOFIND & ",") Or _ (Right(tmp, Len(VALUETOFIND) + 1) = "," & VALUETOFIND) Then arr = Split(tmp, vbNewLine) 'find the line(s) containing VALUETOFIND For i = 0 To UBound(arr) If InStr(arr(i), "," & VALUETOFIND & ",") Or _ (Left(arr(i), Len(VALUETOFIND) + 1) = VALUETOFIND & ",") Or _ (Right(arr(i), Len(VALUETOFIND) + 1) = "," & VALUETOFIND) Then importarr = Split(arr(i), ",") nextrow = Cells.SpecialCells(xlCellTypeLastCell).Row + 1 For j = 0 To UBound(importarr) 'up to you to determine what nextrow is Cells(nextrow, j + 1).Value = importarr(j) Next End If Next End If curfile = Dir$ Wend 'save it fname = Application.GetSaveAsFilename If fname Then ActiveWorkbook.SaveAs fname ChDrive backdir ChDir backdir End Sub -- Without Ultra Port Guard 2000, hackers can steal your children! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IS THERE A WAY TO SEARCH FOR AND CALCULATE MULTIPLE EXCEL FILES? | Excel Programming | |||
Data search and extraction from multiple text files query | Excel Programming | |||
Search text in multiple files in multiple directories | Excel Programming | |||
Search for text in multiple excel files | Excel Programming | |||
Import multiple files macro can't find files | Excel Programming |