Consulting

Results 1 to 6 of 6

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

  1. #1
    VBAX Regular
    Joined
    Jan 2007
    Posts
    42
    Location

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

    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

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Regular
    Joined
    Jan 2007
    Posts
    42
    Location
    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

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    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]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    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]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    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.

    Quote Originally Posted by cbs81
    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,
    Brandtrock




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •