Accessing multiple files for 1 report

[PC509]

I know I should be using Access, or SQL for this, but since most of the files and monthly report are on Excel... Made by the state in the state format...

I have 30-31 separate daily sheets, plus about 10-12 other sheets. All separate files, named after the date (4-1-05, 4-2-05, etc.). Is there a script to manage these to input certain cells for each month to one report? Or should I take the day off and make an Access database?

It would be easier to use Excel, since my co-workers all know those sheets, and if I moved to Access, it would look better, and probably more user friendly... But, my co-workers are a bit computer illiterate and would hate me if I were to change things around.

[Killian]

Hi there

This is do-able in Excel and if you are dealing with relatively small amounts of fixed data from each day to go into the monthly report then it's probably the best way. If you need to be more flexible with the data you retrieve, or want to produce multiple custom reports, then putting the daily data into a db like Access would be preferable.
Here is a code snippet that processes all the files in sequence for a given month from a defined target directory. If you want the ability to select the month/year, change the data retrieved, etc, then you'll need to add a userfrom to get that input. I've attached an example of the same code with some user interaction.
The open-getdata-close cycle for each workbook may be quite slow (another reason why a db might be better) so I added a simple status/progess indicator.

Hope this helps

Const SOURCE_FLDR As String = "C:\Documents and Settings\Killian\Desktop\DailyData"

Sub MonthReport()
Dim d As Long, m As Long
Dim dWB As Workbook
Dim TargetFilePath As String
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject") 'to find the files
Application.ScreenUpdating = False
m = 4 'for month 4
For d = 1 To 31 'do for each day
TargetFilePath = SOURCE_FLDR & m & "-" & d & "-05.xls" 'construct file path
If fs.FileExists(TargetFilePath) = True Then 'see if file exists
Set dWB = Workbooks.Open(TargetFilePath) 'open workbook
'get some data and write to monthly report
ThisWorkbook.Sheets(1).Cells(d, 1).Value = m & "-" & d & "-05"
ThisWorkbook.Sheets(1).Cells(d, 2).Value = dWB.Sheets(1).Cells(1, 1).Value
'close daily wb without saving
dWB.Saved = True
dWB.Close
End If
Next d 'loop back for next day
End Sub

[lucas]

I yoinked it but would like to retrieve more than just cell A1 from the target books.

ThisWorkbook.Sheets(1).Cells(d, 2).Value = dWB.Sheets(1).Cells(1, 1).Value

how would you script this to get several rows?
how would you script this to get an entire sheet?

have tried this:

ThisWorkbook.Sheets(1).Cells(d, 2).Value = dWB.Sheets(1).Cells(1, 1, 1, 7).Value

but it stops after the first target book.

I also changed this line

ThisWorkbook.Sheets(1).Cells(d, 1).Value = m & "-" & d & "-05"

to

ThisWorkbook.Sheets(1).Cells(d, 1).Value = cboM.Value & "-" & d & "-05"

[Anne Troy]

I yoinked it

See what you started?

[Killian]

This part is where the action is for each file

'get some data and write to monthly report
            ThisWorkbook.Sheets(1).Cells(d, 1).Value = m & "-" & d & "-05" 
            ThisWorkbook.Sheets(1).Cells(d, 2).Value = dWB.Sheets(1).Cells(1, 1).Value

I'm not sure what you want to do so maybe you should describe it in more detail. My example writes some data in one row, so there'd be one row per day in the Monthly report. If you wanted several rows copied, you'd have to keep track of where you are writing them to:

dWB.Sheets(1).Rows("1:7").Copy
            ThisWorkbook.Sheets(1).Rows(1 + ((d - 1) * 7)).Insert Shift:=xlDown

Copying and pasting data will normally result in an annoying message so you'll have to switch that off

'put this at the start of your procedure

Application.DisplayAlerts = False

'and this at the end

Application.DisplayAlerts = True[/VBA]

If you want to add a sheet for each day:

'get some data and write to monthly report
            Set MyNewSheet = ThisWorkbook.Sheets.Add
            MyNewSheet.Name = m & "-" & d & "-" & "05"

then add some data to that

Yoink away!

[lucas]

I think I have it now. thanks for the insights.

was wondering why on my version of excel (2000) that this piece of code returns a 0 in my results instead of the month.

ThisWorkbook.Sheets(1).Cells(d, 1).Value = m & "-" & d & "-05"

I get a date like 0/1/05
if I change it to this code:

ThisWorkbook.Sheets(1).Cells(d, 1).Value = cboM.Value & "-" & d & "-05"

I get the correct date readout: 4/1/05

could be a difference in versions?

[Killian]

no probs lucas...

With the month value, I think you're missing a small difference in the code samples. The variable m in the code I posted first was initialized before the loop

m = 4 'for month 4

In the code behind the form, the month value is taken from the combobox

cboM.Value

[lucas]

I'ts working fine now. Thanks for your time.