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?
|
Similar Threads
