Pulling data from external files

[Ade]

Hi all,

I have 12 external files (one per month) and need a quick way to pull these into a reporting book. I've used the following code...

Workbooks.Open FileName:= _
"J:\Manfin\MIS\New Reporting\MIS2\P200301.xls"
Columns("A:B").Select
Selection.Copy
Windows("MainReportingBook.xls").Activate
Sheets("P200301").Select
Range("A1").Select
ActiveSheet.Paste
Windows("P200301.xls").Activate
Call OpenClipboard(0&): Call EmptyClipboard: Call CloseClipboard
ActiveWindow.Close

However with 10000++ rows of data in each file, this is very very slow (approx 5 mins to update all 12 months).

As a complete newbie to vb, I started by recording then amending the code - but now I need some help!

Any quick fix ideas would be greatly appreciated. I found some code on ozgrid.com to extract data without opening the file - but couldn't get it to work - my fault as I wasn't sure which bits to overwrite with my own filenames etc etc.

Thanks in advance!

Ade

 

[Ade]

I forgot to mention - that code is obviously repeated 12 times (once per month!).

Cheers

 

[Legacy 98055]

Hi.
What version of Office are you using?

You gave one example of where the imported data will go.

Sheets("P200301").Select
Range("A1").Select

Where will the other 22 remaining ranges be places?

Are there headers in your source workbooks columns? If so, which row. Which row does your data begin listing in the source books?

Tom

 

[Ade]

Hi,

Thanks for the reply.

Excel 97

Data files are called eg. P200301 (for january), P200302 (for february) etc and the data is to be placed on a sheet of the same name within the reporting file.

Data files contain purely data, no headers, subtotals etc. It's data that has been dumped out by a separate application.

Hope this helps,

Rgds

Ade

 

[goblin]

A macro that is run once a month, and it takes 5 minutes? I don't think it is worth your time to optimise this greatly. Unless this macro is run by someone impatient that will grumble, grumble, grumble.

Places that I would consider when optimising:

You are updating once a month, so start with last months master file and only add the new month to the file. That way you only have to do one copy, cutting the time needed by a factor of 12 (seems like you have seperate sheets for each month).

Consider dropping all those selections. You don't need to select a range to copy it. Just copy it.

Avoid switching windows if you don't need to (especially if you don't have enough memory in you box).

Avoid updating the screen if you don't have anything to show. If you are running a batch job like this, you don't have anything to show. To make things user friendly, update the statusbar instead

Application.ScreenUpdating = False
dim main as workbook, wb as workbook
dim str as string

str = "J:\Manfin\MIS\New Reporting\MIS2\P200301.xls" 
set main = Workbooks("MainReportingBook.xls")

'for each file in filecollection
   Application.Statusbar = "Copying " & file
   set wb = Workbooks.Open(file,,true)
   Columns("A:B").Copy _ 
     destination:= main.Sheets("P200301").Range("A1")
   wb.Close
   set wb = Nothing
' next

Application.ScreenUpdating = true
Application.Statusbar = false

Hope this helps
Goblin

 

[Ade]

Goblin,

Thanks for the code, I'll give it a try this am and let you know how I get on. Regarding the frequency of updates - unfortunately it's not just a once per month process, as the source data comes from suppliers at irregular times, the download and report updating could feasibly be done up to say 20 times per month. And unfortunately, all the data is liable to change. It probably won't, but the facility needs to be there just in case.

I need to make this file as simlpe as possible for the users to use - ie. "Load Data" button rather than asking them to decide which data to load.

Again, thanks for the code, will try it shortly.

Rgds

 

[Ade]

Strange.....

When I run the code (from Goblin) just for one month, it runs very quickly, but when I then copy that and multiply it for 12 months - it takes forever - much longer than 12x. Any ideas gurus?

Cheers!

 

[tusharm]

Goblin's code is designed for only one file. What changes did you make to use it with multiple files? And, if you share the code, please format it for readability. Use the CODE button at the top of the form used for posting messages.

 

[Jaamie]

Time Stamp

Ade,

As a separate thought, would it help to keep a time stamp for each workbook that comes in via:

TimeNow = FileDateTime(Filename)

Then only update those workbooks that have a later date than the last update run.

Jim..

 

[Ade]

Hi,

I've amended the code form Goblin as show below, apart from the very first time that I ran the initial bit of code (when it seemed to finish in about 2 seconds), P200301 takes about 35 seconds. When I run the full code as shown below it then takes about 1min30secs for P200302, and then about 1min for P200303 - very strange.

Sub DataImport2()

Application.ScreenUpdating = False
Dim main As Workbook, wb As Workbook
Dim str As String

Set main = Workbooks("MainReportingBook(Ade2).xls")

'P200301
   str = "J:\Manfin\MIS\New Reporting\MIS2\P200301.xls"
   Application.StatusBar = "Copying " & str
   Set wb = Workbooks.Open(str, , True)
   main.Sheets("P200301").Range("A1:B20000").Value = Range("A1:B20000").Value
   wb.Close
   Set wb = Nothing
'P200302
   str = "J:\Manfin\MIS\New Reporting\MIS2\P200302.xls"
   Application.StatusBar = "Copying " & str
   Set wb = Workbooks.Open(str, , True)
   main.Sheets("P200302").Range("A1:B20000").Value = Range("A1:B20000").Value
   wb.Close
   Set wb = Nothing
'P200303
   str = "J:\Manfin\MIS\New Reporting\MIS2\P200303.xls"
   Application.StatusBar = "Copying " & str
   Set wb = Workbooks.Open(str, , True)
   main.Sheets("P200303").Range("A1:B20000").Value = Range("A1:B20000").Value
   wb.Close
   Set wb = Nothing

Application.ScreenUpdating = True
Application.StatusBar = False


End Sub

I tried amending the code to see if pasting values only helped at all (hope I did it correctly!)

Thanks in advance...