PDA

View Full Version : Trying to only retrieve files that i do not already have



Lukeh
02-04-2017, 04:45 PM
Hello!
I currently have a loop that grabs all of the files in a specific folder (then does some processing of the file and adds the info I need to my database). I have a table (database) built that does have a column that contains the file name. how do I alter my code so that it will go into the folder and only retrieve the files that I do not already have in the database?
Any help would be greatly appreciated!!
Thanks! :)
Here is the module:



Sub AllFiles()
'
'Update DataBase Macro
'
Sheets("DataBase").Select
Rows("5:172").Select
Selection.Delete Shift:=xlUp
Range("A5").Select
Sheets("Probe Table").Select

Dim folderPath As String
Dim filename As String
Dim wb As Workbook

folderPath = "S:\High Level\Lab\Shared\Pressman Probe\2017 Fines Testing"

If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"

filename = Dir(folderPath & "*.csv")
Do While filename <> ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderPath & filename)


'Paste probe table into workbook
Range("A1:K1169").Select
Application.CutCopyMode = False
Selection.Copy
Windows("PressMAN Probe Report.xlsm").Activate
Sheets("Probe Table").Select
Range("B3").Select
Selection.ClearComments
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Add File Name to Table

Sheets("DataBase").Select
Cells(1, 1).Value = (filename)
Set wb = Workbooks.Open(folderPath & filename)
ActiveWindow.Close

'Process Table and Add to DataBAse
Sheets("Press Table").Select
Range("k3:L49").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-12
Range("Y3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Y3:Y49").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DataBase").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Press Table").Select
Range("Z3:Z49").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DataBase").Select
Range("AW1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Sheets("Probe Table").Select
Range("Q7").Select
Selection.Copy
Sheets("DataBase").Select
Range("CV1").PasteSpecial xlPasteValues

Sheets("Press Table").Select
Range("P1").Select
Selection.Copy
Sheets("DataBase").Select
Range("CW1").PasteSpecial xlPasteValues

Sheets("Press Table").Select
Range("Y3:Z46").Select
Selection.ClearContents

Sheets("Report").Select
Range("X3").Select
Selection.Copy
Sheets("DataBase").Select
Range("CR1").PasteSpecial xlPasteValues

Sheets("Report").Select
Range("X4").Select
Selection.Copy
Sheets("DataBase").Select
Range("CS1").PasteSpecial xlPasteValues

Sheets("Report").Select
Range("X5").Select
Selection.Copy
Sheets("DataBase").Select
Range("CT1").PasteSpecial xlPasteValues

Sheets("Report").Select
Range("X6").Select
Selection.Copy
Sheets("DataBase").Select
Range("CU1").PasteSpecial xlPasteValues


Sheets("Frame Distance").Select
Range("C2").Select
Selection.Copy
Sheets("DataBase").Select
Range("CX1").PasteSpecial xlPasteValues




'Copies top line to bottom of database.
Sheets("DataBase").Select
Range("A1:CX1").Select
Selection.Copy
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.PasteSpecial (xlPasteValuesAndNumberFormats)
Sheets("Report").Select



filename = Dir
Loop
Application.ScreenUpdating = False
End Sub

jolivanes
02-04-2017, 09:55 PM
Stay away from selecting unless it is absolutely required which it seldom is.
Clean up your code like some of the following.
As far as I am concerned, explain in words what you want to do.
Having a page full of code that does not work is no help at all and everyone has different ways of solving problems so they likely won't need your code if you explain concisely what is required.
This

Sheets("DataBase").Select
Rows("5:172").Select
Selection.Delete Shift:=xlUp
Range("A5").Select
should be shortened to

Sheets("DataBase").Rows("5:172").Delete Shift:=xlUp


and this

Sheets("Report").Select
Range("X3").Select
Selection.Copy
Sheets("DataBase").Select
Range("CR1").PasteSpecial xlPasteValues

Sheets("Report").Select
Range("X4").Select
Selection.Copy
Sheets("DataBase").Select
Range("CS1").PasteSpecial xlPasteValues

Sheets("Report").Select
Range("X5").Select
Selection.Copy
Sheets("DataBase").Select
Range("CT1").PasteSpecial xlPasteValues

Sheets("Report").Select
Range("X6").Select
Selection.Copy
Sheets("DataBase").Select
Range("CU1").PasteSpecial xlPasteValues


to this

Sheets("DataBase").Range("CR1:CU1").Value = Application.Transpose(Sheets("Report").Range("X3:X6").Value)


Re:Trying to only retrieve files that i do not already have
Can you elaborate on this.

Lukeh
02-05-2017, 01:30 PM
Thank you for the reply, and the advice!
This code is a loop that goes into the selected folder and loops through each file in the folder. Once the file is opened it copies data from the file and pastes it into my workbook. once the data is into my workbook it is all processed down to 103 values and those values are then pasted into the database (transposed). It works great! the only issue I have is that each time I run this macro it reopens each file (again) and processes the data and then gets into the new files. I am looking for a way to alter this code so that it will recognize which files I have already opened and processed and only work on new files that do not already exist on my database. I do have the filenames on my database table to compare the files in the folder with.
Thanks!

macropod
02-05-2017, 01:43 PM
This thread is a duplicate of: http://www.vbaexpress.com/forum/showthread.php?58485-Trying-to-only-retrieve-files-that-i-do-not-already-have
Kindly don't start multiple threads on the same topic.

Thread closed. You may continue the discussion in your other thread.