I'm trying to take data from 74 Excel files, 8 worksheets and import it into
an Access database. I'm fairly fluent in Access, and I can accomplish this,
but the import process is extremely slow (5 hours) due to the fact all the
Excel files are linked. If I use Copy/Paste Special/Values, the links are
broken and the import time drops to about an hour, which is acceptable.

I'm looking for an automated means in Excel to loop through all 74 files, 8
worksheets per file, and apply a Copy/Paste Special/Values command within
each worksheet. Help?

Sub ProcessWorkbooks()
Dim bk as Workbook
Dim sPath as String
Dim v as Variant, i as Long
Dim sh as Worksheet
spath = "C:\MyFolder\"
v = Array("Book1.xls", ... , "Book8.xls")
for i = lbound(v) to ubound(v)
set bk = workbooks.Open( sPath & v(i))
for each sh in bk
sh.UsedRange.formula = sh.UsedRange.Value
next
bk.Close Savechanges:=False
Next
End sub

--
Regards,
Tom Ogilvy


"Kirk P." wrote:

I'm trying to take data from 74 Excel files, 8 worksheets and import it into
an Access database. I'm fairly fluent in Access, and I can accomplish this,
but the import process is extremely slow (5 hours) due to the fact all the
Excel files are linked. If I use Copy/Paste Special/Values, the links are
broken and the import time drops to about an hour, which is acceptable.

I'm looking for an automated means in Excel to loop through all 74 files, 8
worksheets per file, and apply a Copy/Paste Special/Values command within
each worksheet. Help?

I'm hitting a run-time error 438 Object doesn't support this property or
method at the line:

for each sh in bk

Also, the source files are linked, so it asks me if I want to update the
links first. Can this be defaulted to "No" - i.e. Don't Update?



"Tom Ogilvy" wrote:

Sub ProcessWorkbooks()
Dim bk as Workbook
Dim sPath as String
Dim v as Variant, i as Long
Dim sh as Worksheet
spath = "C:\MyFolder\"
v = Array("Book1.xls", ... , "Book8.xls")
for i = lbound(v) to ubound(v)
set bk = workbooks.Open( sPath & v(i))
for each sh in bk
sh.UsedRange.formula = sh.UsedRange.Value
next
bk.Close Savechanges:=False
Next
End sub

--
Regards,
Tom Ogilvy


"Kirk P." wrote:

I'm trying to take data from 74 Excel files, 8 worksheets and import it into
an Access database. I'm fairly fluent in Access, and I can accomplish this,
but the import process is extremely slow (5 hours) due to the fact all the
Excel files are linked. If I use Copy/Paste Special/Values, the links are
broken and the import time drops to about an hour, which is acceptable.

I'm looking for an automated means in Excel to loop through all 74 files, 8
worksheets per file, and apply a Copy/Paste Special/Values command within
each worksheet. Help?

Sub ProcessWorkbooks()
Dim bk as Workbook
Dim sPath as String
Dim v as Variant, i as Long
Dim sh as Worksheet
spath = "C:\MyFolder\"
v = Array("Book1.xls", ... , "Book8.xls")
for i = lbound(v) to ubound(v)
set bk = workbooks.Open( sPath & v(i), UpdateLinks:=0)
for each sh in bk.Worksheets
sh.UsedRange.formula = sh.UsedRange.Value
next
bk.Close Savechanges:=False
Next
End sub

--
Regards,
Tom Ogilvy



"Kirk P." wrote:

I'm hitting a run-time error 438 Object doesn't support this property or
method at the line:

for each sh in bk

Also, the source files are linked, so it asks me if I want to update the
links first. Can this be defaulted to "No" - i.e. Don't Update?



"Tom Ogilvy" wrote:

Sub ProcessWorkbooks()
Dim bk as Workbook
Dim sPath as String
Dim v as Variant, i as Long
Dim sh as Worksheet
spath = "C:\MyFolder\"
v = Array("Book1.xls", ... , "Book8.xls")
for i = lbound(v) to ubound(v)
set bk = workbooks.Open( sPath & v(i))
for each sh in bk
sh.UsedRange.formula = sh.UsedRange.Value
next
bk.Close Savechanges:=False
Next
End sub

--
Regards,
Tom Ogilvy


"Kirk P." wrote:

I'm trying to take data from 74 Excel files, 8 worksheets and import it into
an Access database. I'm fairly fluent in Access, and I can accomplish this,
but the import process is extremely slow (5 hours) due to the fact all the
Excel files are linked. If I use Copy/Paste Special/Values, the links are
broken and the import time drops to about an hour, which is acceptable.

I'm looking for an automated means in Excel to loop through all 74 files, 8
worksheets per file, and apply a Copy/Paste Special/Values command within
each worksheet. Help?

Works great Tom. Thanks for the help!

"Tom Ogilvy" wrote:

Sub ProcessWorkbooks()
Dim bk as Workbook
Dim sPath as String
Dim v as Variant, i as Long
Dim sh as Worksheet
spath = "C:\MyFolder\"
v = Array("Book1.xls", ... , "Book8.xls")
for i = lbound(v) to ubound(v)
set bk = workbooks.Open( sPath & v(i), UpdateLinks:=0)
for each sh in bk.Worksheets
sh.UsedRange.formula = sh.UsedRange.Value
next
bk.Close Savechanges:=False
Next
End sub

--
Regards,
Tom Ogilvy



"Kirk P." wrote:

I'm hitting a run-time error 438 Object doesn't support this property or
method at the line:

for each sh in bk

Also, the source files are linked, so it asks me if I want to update the
links first. Can this be defaulted to "No" - i.e. Don't Update?



"Tom Ogilvy" wrote:

Sub ProcessWorkbooks()
Dim bk as Workbook
Dim sPath as String
Dim v as Variant, i as Long
Dim sh as Worksheet
spath = "C:\MyFolder\"
v = Array("Book1.xls", ... , "Book8.xls")
for i = lbound(v) to ubound(v)
set bk = workbooks.Open( sPath & v(i))
for each sh in bk
sh.UsedRange.formula = sh.UsedRange.Value
next
bk.Close Savechanges:=False
Next
End sub

--
Regards,
Tom Ogilvy


"Kirk P." wrote:

I'm trying to take data from 74 Excel files, 8 worksheets and import it into
an Access database. I'm fairly fluent in Access, and I can accomplish this,
but the import process is extremely slow (5 hours) due to the fact all the
Excel files are linked. If I use Copy/Paste Special/Values, the links are
broken and the import time drops to about an hour, which is acceptable.

I'm looking for an automated means in Excel to loop through all 74 files, 8
worksheets per file, and apply a Copy/Paste Special/Values command within
each worksheet. Help?