Way to only import new files?
 

Below is code that I found on a site by Ron De Bruin. It seems to wor
well for what I am asking it to, but I am curious if there is a way t
speed it up. The folder that it is pulling information from has severa
hundred files and it takes a great deal of time. I'm not sure if perhap
I do not have the right code for this type of process or if there i
anything I can do other than sit and wait for it to update. Or I wa
wondering if there is a way to modify it to only update new files an
paste the information at the next available blank line.... Help i
appreciated. Thanks!

Sub Example1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim rnum As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim Cnum As Integer
Dim cell As Range


SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings...."
ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
'clear all cells on the first sheet
'basebook.Worksheets(1).Cells.Clear

rnum = 2

Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

Cnum = 1
For Each cell In mybook.Worksheets(1).Range("D4,I4,C6,D6")
basebook.Worksheets(1).Cells(rnum, Cnum).Value = cell.Value
Cnum = Cnum + 1
Next cell

mybook.Close False
rnum = rnum + 1
FNames = Dir()
Loop

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = False
End Su

--
shikamikamoomo
-----------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...fo&userid=2101
View this thread: http://www.excelforum.com/showthread.php?threadid=52574

Way to only import new files?
 

Hi shikamikamoomoo

I will make a example for you (also for the site)
Tomorrow or Saterday I post back (Busy on this moment)

--
Regards Ron de Bruin
http://www.rondebruin.nl


"shikamikamoomoo" <shikamikamoomoo.254sgc_1143131702.7107@excelfor um-nospam.com wrote in message
news:shikamikamoomoo.254sgc_1143131702.7107@excelf orum-nospam.com...

Below is code that I found on a site by Ron De Bruin. It seems to work
well for what I am asking it to, but I am curious if there is a way to
speed it up. The folder that it is pulling information from has several
hundred files and it takes a great deal of time. I'm not sure if perhaps
I do not have the right code for this type of process or if there is
anything I can do other than sit and wait for it to update. Or I was
wondering if there is a way to modify it to only update new files and
paste the information at the next available blank line.... Help is
appreciated. Thanks!

Sub Example1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim rnum As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim Cnum As Integer
Dim cell As Range


SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings...."
ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
'clear all cells on the first sheet
'basebook.Worksheets(1).Cells.Clear

rnum = 2

Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

Cnum = 1
For Each cell In mybook.Worksheets(1).Range("D4,I4,C6,D6")
basebook.Worksheets(1).Cells(rnum, Cnum).Value = cell.Value
Cnum = Cnum + 1
Next cell

mybook.Close False
rnum = rnum + 1
FNames = Dir()
Loop

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = False
End Sub


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Thank you so much!!! Greatly appreciated. :)


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Hi shikamikamoomoo

Here is one to test for you
Will finish it tomorrow and add it to the webpage

Copy the function also in the module

Sub Test_More_Areas()
Dim basebook As Workbook
Dim mybook As Workbook
Dim rnum As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim Cnum As Integer
Dim cell As Range

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook

Do While FNames < ""

If IsError(Application.Match(FNames, _
basebook.Worksheets(1).Columns("A"), 0)) Then
rnum = LastRow(basebook.Worksheets(1)) + 1
Set mybook = Workbooks.Open(FNames)

' This will add the workbook name in column A if you want
basebook.Worksheets(1).Cells(rnum, "A").Value = mybook.Name

' Copy the cell values from each cell in one row starting in column B
Cnum = 2
For Each cell In mybook.Worksheets(1).Range("A2,A3,C2,C3,E2,E3")
basebook.Worksheets(1).Cells(rnum, Cnum).Value = cell.Value
Cnum = Cnum + 1
Next cell
mybook.Close False
End If
FNames = Dir()
Loop

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

--
Regards Ron de Bruin
http://www.rondebruin.nl


"shikamikamoomoo" <shikamikamoomoo.25579p_1143150903.7079@excelfor um-nospam.com wrote in message
news:shikamikamoomoo.25579p_1143150903.7079@excelf orum-nospam.com...

Thank you so much!!! Greatly appreciated. :)


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

hmmm...perhaps I am not copying this into the right location. I copied
the entire code into worksheet 1 and then the Function section into
module 1....is this right? It seems like it does something....but I'm
not sure what. It does not copy anything into the file.


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Copy both in a normal module(not a sheet module)

Change the path to yours

--
Regards Ron de Bruin
http://www.rondebruin.nl


"shikamikamoomoo" <shikamikamoomoo.25763m_1143242701.496@excelforu m-nospam.com wrote in message
news:shikamikamoomoo.25763m_1143242701.496@excelfo rum-nospam.com...

hmmm...perhaps I am not copying this into the right location. I copied
the entire code into worksheet 1 and then the Function section into
module 1....is this right? It seems like it does something....but I'm
not sure what. It does not copy anything into the file.


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

I have update the site
http://www.rondebruin.nl/copy3.htm#new


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi shikamikamoomoo

I will make a example for you (also for the site)
Tomorrow or Saterday I post back (Busy on this moment)

--
Regards Ron de Bruin
http://www.rondebruin.nl


"shikamikamoomoo" <shikamikamoomoo.254sgc_1143131702.7107@excelfor um-nospam.com wrote in message
news:shikamikamoomoo.254sgc_1143131702.7107@excelf orum-nospam.com...

Below is code that I found on a site by Ron De Bruin. It seems to work
well for what I am asking it to, but I am curious if there is a way to
speed it up. The folder that it is pulling information from has several
hundred files and it takes a great deal of time. I'm not sure if perhaps
I do not have the right code for this type of process or if there is
anything I can do other than sit and wait for it to update. Or I was
wondering if there is a way to modify it to only update new files and
paste the information at the next available blank line.... Help is
appreciated. Thanks!

Sub Example1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim rnum As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim Cnum As Integer
Dim cell As Range


SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings...."
ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
'clear all cells on the first sheet
'basebook.Worksheets(1).Cells.Clear

rnum = 2

Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

Cnum = 1
For Each cell In mybook.Worksheets(1).Range("D4,I4,C6,D6")
basebook.Worksheets(1).Cells(rnum, Cnum).Value = cell.Value
Cnum = Cnum + 1
Next cell

mybook.Close False
rnum = rnum + 1
FNames = Dir()
Loop

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = False
End Sub


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

I'm not sure what I am doing wrong, but I cannot get this to copy
anything over. I've tried copying the entire code including the
Function into Module 1. But when I run it nothing happens. Do I need
this code plus the original? I changed the path to the right location
and I do not get any errors.


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Send me your test file with the code private and I will see where you went wrong

--
Regards Ron de Bruin
http://www.rondebruin.nl


"shikamikamoomoo" <shikamikamoomoo.259maq_1143357023.483@excelforu m-nospam.com wrote in message
news:shikamikamoomoo.259maq_1143357023.483@excelfo rum-nospam.com...

I'm not sure what I am doing wrong, but I cannot get this to copy
anything over. I've tried copying the entire code including the
Function into Module 1. But when I run it nothing happens. Do I need
this code plus the original? I changed the path to the right location
and I do not get any errors.


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Sent a copy of the file....thanks for your help :)


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Nothing in my Inbox ?

--
Regards Ron de Bruin
http://www.rondebruin.nl


"shikamikamoomoo" <shikamikamoomoo.25c7tc_1143478203.0041@excelfor um-nospam.com wrote in message
news:shikamikamoomoo.25c7tc_1143478203.0041@excelf orum-nospam.com...

Sent a copy of the file....thanks for your help :)


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

I sent it to the address on your site and it bounced back saying that
the server refused it. Spam block?


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Strange

Zip it and try again

--
Regards Ron de Bruin
http://www.rondebruin.nl


"shikamikamoomoo" <shikamikamoomoo.25ccwo_1143484810.4529@excelfor um-nospam.com wrote in message
news:shikamikamoomoo.25ccwo_1143484810.4529@excelf orum-nospam.com...

I sent it to the address on your site and it bounced back saying that
the server refused it. Spam block?


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Sent it again, this time the file is zipped. :)


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Have you used the correct address



--
Regards Ron de Bruin
http://www.rondebruin.nl


"shikamikamoomoo" <shikamikamoomoo.25cpem_1143501005.5216@excelfor um-nospam.com wrote in message
news:shikamikamoomoo.25cpem_1143501005.5216@excelf orum-nospam.com...

Sent it again, this time the file is zipped. :)


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Yes I'm sure that is the address that I used, same one that is on your
Excel Help site. I sent it again straight from here so this time it is
definately the address that I am using.


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

The file will be coming from (in case it is
filtered in with junk mail)


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

I send you a private mail


--
Regards Ron de Bruin
http://www.rondebruin.nl


"shikamikamoomoo" <shikamikamoomoo.25e1bo_1143563110.3167@excelfor um-nospam.com wrote in message
news:shikamikamoomoo.25e1bo_1143563110.3167@excelf orum-nospam.com...

The file will be coming from (in case it is
filtered in with junk mail)


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Replied to the email.....let me know if you still do not hear anything.
If this doesn't work, I'll try to send it from a different email address
later this evening.


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Nothing

How big is the file ?

--
Regards Ron de Bruin
http://www.rondebruin.nl


"shikamikamoomoo" <shikamikamoomoo.25e3vb_1143566406.597@excelforu m-nospam.com wrote in message
news:shikamikamoomoo.25e3vb_1143566406.597@excelfo rum-nospam.com...

Replied to the email.....let me know if you still do not hear anything.
If this doesn't work, I'll try to send it from a different email address
later this evening.


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

52.3 KB -

I will send it through my other email later this evening....other than
that I'm not sure what to do.


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

Very strange

I hope I see it <g
Late in the evening here so maybe I reply tomorrow

--
Regards Ron de Bruin
http://www.rondebruin.nl


"shikamikamoomoo" <shikamikamoomoo.25eb1n_1143575702.9278@excelfor um-nospam.com wrote in message
news:shikamikamoomoo.25eb1n_1143575702.9278@excelf orum-nospam.com...

52.3 KB -

I will send it through my other email later this evening....other than
that I'm not sure what to do.


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746

Way to only import new files?
 

My apologies, I did not get a chance to send that off last night. I'm
assuming you still have not received the previous emails that I sent (I
haven't seen a bounce back message yet). We'll have to put this on hold
one more day and I'll send it off tonight. Thanks for your patience.


--
shikamikamoomoo
------------------------------------------------------------------------
shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018
View this thread: http://www.excelforum.com/showthread...hreadid=525746