Copying data from another excel workbook created to another file automatically.

[cbs81]

I have an interesting question; I have got a cash reconciliation template that is done each month by our clients? This template is saved in a root directory... for example, C:\cashreconciliation\bus operator cash reconciliation template.xls

Now when this file is opened, the operator selects from the parameters sheet the name of the operator, month and then year. You can see this from the code below that is shown after my question 2, there is a button on this worksheet which saves a copy of this template and saves it to another file within the cash reconciliation folder and names it

Application.ActiveWorkbook.Path & "\Cash Reconciliation " & _ oprtr$ & " " & mnth$ & " " & yr$

In other words:

C:\cashreconciliation\Cash reconciliation bribie march 2007



This is a new file created.

Now my interesting question, I would like to do 2 things from these automatically created cash recs which contain data.


1) I have a separate ?Revenue report template? excel file similar to this cash rec template as described above. Saved in the same root as above.. that is, C:\cashreconciliation\Revenue report template. This also allows the user to select the operator, month and year from a drop down list. This new file is saved using as a new file name using a similar principal as above. This file is saved automatically in the same path as the cashrec

C:\cashreconciliation\Revenue report bribie march 2007.xls


Now I have these two files, I would like the revenue template file to automatically (or by a click of a button) retrieve certain cells in the cashrec ONLY IF THE OPERATOR AND THE MONTH AND THE YEAR OF BOTH OF THE FILES match..

For example.. lets say there is a cash reconciliation file name called:

Cash reconciliation bribie march 2007 (created from the template file named: bus operator cash reconciliation template.xls)



And another file name called:



Revenue report bribie march 2007 (created from the template file named: revenue report template.xls)





Now because the ?bribie march 2007 last three words? exist in these two separte files and they match I need some code that takes a cells value in the Cash reconciliation bribie march 2007 file in cell for example


C5 in sheet named ?cash summary? which equals $100 and transfers this value into F9 in sheet named ?submission? in the Revenue report bribie march 2007 file.

Other transfers for example would be:

C7 in sheet named ?cash summary? in the Cash reconciliation bribie march 2007 file and transfer them to F10 in sheet named ?submission? in the Revenue report bribie march 2007 file.

C9 in sheet named ?cash summary? in the Cash reconciliation bribie march 2007 file and transfer them to F11 in sheet named ?submission? in the Revenue report bribie march 2007 file.


C11 in sheet named ?cash summary? in the Cash reconciliation bribie march 2007 file and transfer them to F13 in sheet named ?submission? in the Revenue report bribie march 2007 file.


I would like a button on the Revenue report bribie march 2007 file to update all these fields ONLY if a corresponding Cash reconciliation bribie march 2007 file exists? there needs to be a match of the selected operator, and the month and the year for there to be all these above transfers.




I do hope you have a solution to this?






Well here is my Question 2)


A bit more tricky..


I am designing an analytical spreadsheet template which will be used as a management reporting tool. This is saved in the :

C:\cashreconciliation\management report template.xls

When opened this file allows the user to ONLY select the operator, AND year and NOT THE month from separate drop down lists. This new file is saved as a new file name using a similar principal as above. This file is saved automatically in the same path as the cashrec and for example the new file may be called the following depending on the parameters selected.

C:\cashreconciliation\management report bribie 2007.xls



Now what I would like this file to do is IF the files in the folder containing cash reconciliations contain the same operator, in this instance ?bribie? and the same year ?2007? .. for example say these files existed:


C:\cashreconciliation\Cash reconciliation bribie march 2007
C:\cashreconciliation\Cash reconciliation bribie april 2007
C:\cashreconciliation\Cash reconciliation bribie may 2007
C:\cashreconciliation\Cash reconciliation bribie june 2007
C:\cashreconciliation\Cash reconciliation bribie july 2007


Then I would like the file that is created: ?management report bribie 2007.xls? to find all these Months if the operator is the same and if the year is the same then do the following.

in cell A1 on sheet 1 on the ?management report bribie 2007.xls? file I would like it to go and find all cash reconciliation files where it matches ?BRIBIE? and ?2007??

then, from this I would like cell D4, in each of these C:\cashreconciliation\Cash reconciliation bribie xxxx 2007 to flow through to my ?management report bribie 2007 file and be identified by month.

For example in cell b5, I would like the ?management report bribie 2007? to say March 2007 and on c5 I want a code that will go and find that file named Cash reconciliation bribie march 2007, look in the summary sheet for this file and copy cell g5 and paste it in cell c5 on the ?management report bribie 2007?.



In this same workbook ?management report bribie 2007? in cell b6 I would like it to say April 2007 and on c6 I want a code that will go and find that file named Cash reconciliation bribie march 2007, look in the summary sheet for this file and copy cell g5 and paste it in cell c6 on the ?management report bribie 2007?.



Similarly for all the other months that exist for this operator and year..

I would all this function done by a click a button


CAN I PLEEEEEEESE GET SOME HELP

THANKYOU SOOO MUCH FOR YOUR EXPERTISE


Here is the code for question 1





Private Sub CommandButton1_Click()
Dim wbpath$, oprtr$, mnth$, yr$
Dim Sht As Worksheet
Dim sPath As String
' name new workbook according to file path to which original was saved and add Operator, Month, and Year to name
Application.ScreenUpdating = False
If Range("f24") = "-" Then
MsgBox "Operator name must be entered"
Exit Sub
End If
If Range("f26") = "-" Then
MsgBox "Month must be entered"
Exit Sub
End If
If Range("f27") = "-" Then
MsgBox "Year must be entered"
Exit Sub
End If
With Sheets("cover sheet")
oprtr$ = .Range("f24")
mnth$ = .Range("f26")
yr$ = .Range("f27")
End With


With Worksheets("Cover Sheet")
.Unprotect Password:="cbs"
Sheets("Cover Sheet").Shapes("CommandButton1").Delete
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Cover Sheet" Then Sht.Visible = True
Next
Application.Run "rename_sheets"
Application.Run "hidefirstlast"

'***************************

sPath = Application.ActiveWorkbook.Path & "\Cash Reconciliation " & _
oprtr$ & " " & mnth$ & " " & yr$
If Not FileExists(sPath) Then
Application.DisplayAlerts = False
ThisWorkbook.SaveAs sPath
Application.ScreenUpdating = True
Else
MsgBox "File exists"
End If

'***************************

If Range("f26") = "April" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "June" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "September" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "November" Then
Sheet36.Visible = False
Sheet1.Range("40:40").EntireRow.Hidden = True
End If
If Range("f26") = "February" Then
Sheet36.Visible = False
Sheet35.Visible = False
Sheet1.Range("39:40").EntireRow.Hidden = True
End If
End With
End Sub

Function FileExists(Path As String) As Boolean
Dim sfile As String
sfile = Dir(Path, vbNormal)
FileExists = sfile <> ""
End Function

[mdmackillop]

Hi cbs
Your use of the word "template" is confusing the issue. A template is an xlt file used to create a new workbook (xls) based upon it. Are you actually using any templates, or are you just using workbooks which you are saving with new names.

[cbs81]

Hi Mdmackillop,

You are correct, I am just using a workbook, xls file, which is named: xxxxx template.xls to save it with a new name in the same location that this xxxxx template.xls is located..

I hope you can work your magic and come with the best solution for my problems..

Thankyou soo much in advance..

CBS

[mdmackillop]

Question 1
I've not created the files etc. so no testing.
This should get the datsa from the files as specified, but may need a bit debugging. Step through the code and check the variable values if you have ant problems. There may be an extra/missing space in the file name or path

Regards
MD
[vba]
Option Explicit
Option Compare Text

Sub ProcessFiles()
Dim MyPath As String, MyName As String, SheetName As String
Dim MyFile As String
Dim i As Long
Dim MyArray(3, 1)

MyArray(0, 0) = "$C$5"
MyArray(0, 1) = "F9"
MyArray(1, 0) = "$C$7"
MyArray(1, 1) = "F10"
MyArray(2, 0) = "$C$9"
MyArray(2, 1) = "F11"
MyArray(3, 0) = "$C$11"
MyArray(3, 1) = "F13"

For i = 0 To 3
MyFile = Split(ActiveWorkbook.Name, "Report ")(1)
MyPath = "C:\cashreconciliation\"
MyName = "bus operator cash reconciliation " & MyFile
SheetName = "Cash Summary"
Sheets("Submission").Range(MyArray(i, 1)) = GetData(MyPath, MyName, SheetName, MyArray(i, 0))
Next
End Sub

Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
Debug.Print Data
GetData = ExecuteExcel4Macro(Data)
End Function
[/vba]

[mdmackillop]

Question 2
I really don't follow what results you want in what cells, however this variation of the previous code reads the data from the specified sheet. You should be able to adjust names and references to suit.

[VBA]Option Explicit

Sub ProcessFiles2()
Dim MyPath As String, MyName As String, SheetName As String
Dim MyFile1 As String, MyFile2 As String, Str As String
Dim i As Long, Chk As String


For i = 1 To 12
MyFile1 = Split(ActiveWorkbook.Name, " ")(2)
MyFile2 = Split(ActiveWorkbook.Name, " ")(3)

MyPath = "C:\cashreconciliation\"
MyName = "Cash Reconciliation " & MyFile1 & " " & MonthName(i) & " " & MyFile2
Chk = Dir(MyPath & MyName)
If Len(Chk) = 0 Then Exit Sub
SheetName = "Cash Summary"
Str = MonthName(i) & " " & MyFile2
Sheets("Report").Range("A" & i) = Left(Str, Len(Str) - 4)
Sheets("Report").Range("A" & i).NumberFormat = "mmmm yyyy"
Sheets("Report").Range("B" & i) = GetData(MyPath, MyName, SheetName, "$C$1")
Next
End Sub

Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
Debug.Print Data
GetData = ExecuteExcel4Macro(Data)
End Function
[/VBA]

[Brandtrock]

cbs81,

Just a couple general thoughts:

Please use the VBA tags when posting code. It makes it much easier to read. The Green and White VBA button wraps the tags around your code.

I would like a button on the Revenue report bribie march 2007 file to update all these fields ONLY if a corresponding Cash reconciliation bribie march 2007 file exists… there needs to be a match of the selected operator, and the month and the year for there to be all these above transfers.

Placing a button in the template that creates the Revenue report will keep that button in the file when it gets saved with its new name. Once there, coding it to check for a file named "Cash reconciliation" & RIGHT(filename, LEN(filename)-15) will allow you to test whether the correct file exists. If it does, then copy the relevant cells from the Cash reconciliation file and paste to the Revenue report file.

Both the checking for an existing file piece and the copy paste piece have examples in the KB, go there to search first. If you still get stuck, post back with the specific problem (error message, line that the debugger takes you to, etc.) to get the best reply the quickest.

Question 2 is a bit more complex, but the KB does have entries that will open a set of files in a directory. Play around with the examples and see what you can come up with. Again, if stuck, post back with specifics to get an answer.


Regards,