VBA help, creating a macro!!

[mccaco]

Hi I want to create a button on an Excel spreadsheet which will go into a folder, open up the most recent excel file. Add together several cells in that file (B12,B13,B14,I8,I13) then display the answer in the spreadsheet which contains the button (in cell J3).

I've already started the code and i've managed to open up the most recent file in the folder. As I am new to VBA I am struggling to finish off the second half of the code.

I will be grateful for anyones help.

 

[Joe4]

Welcome to the Board!

Why don't you paste the code that you have so far, and we'll see if we can build off of that?

 

[mccaco]

Sub LastModifiedFile()
Dim dirName As String, fName As String, fileTime As Date, FileName As String, latestFile As String
dirName = "P:\New Issues\"
fName = Dir(dirName & "*.*")
latestFile = fName
fileTime = FileDateTime(dirName & fName)
While fName <> ""
    If FileDateTime(dirName & fName) > fileTime Then
        latestFile = fName
        fileTime = FileDateTime(dirName & fName)
    End If
    fName = Dir()
Wend
If latestFile = "" Then
    MsgBox "There are no files in the directory"
Else
    Workbooks.Open "P:\New Issues\" & latestFile
End If
End Sub

heres what I've got so far

 

[Joe4]

OK, try this:

Sub LastModifiedFile()


    Dim dirName As String
    Dim fName As String
    Dim fileTime As Date
    Dim FileName As String
    Dim latestFile As String
    Dim wbSource As Workbook
    Dim wbFile As Workbook
    Dim mySum As Double


'   Capture current macro workbook object reference
    Set wbSource = ActiveWorkbook


    dirName = "P:\New Issues\"
    fName = Dir(dirName & "*.*")


    While fName <> ""
        If FileDateTime(dirName & fName) > fileTime Then
            latestFile = fName
            fileTime = FileDateTime(dirName & fName)
        End If
        fName = Dir()
    Wend
    
    If latestFile = "" Then
        MsgBox "There are no files in the directory"
    Else
        Workbooks.Open dirName & latestFile
'       Capture workbook object reference of file just opened
        Set wbFile = ActiveWorkbook
'       Sum up values in cells
        mySum = Range("B12") + Range("B13") + Range("B14") + Range("I8") + Range("I13")
'       Close workbook
        wbFile.Close
'       Activate original macro workbook
        wbSource.Activate
'       Place sum in cell J3
        Range("J3") = mySum
    End If


End Sub

 

[mccaco]

Thank you, It worked on my test spreadsheet. But when I tried to add a button into the final spreadsheet, the Insert control toolbox and Design Mode cannot be selected. I have changed the macro security to enable all macros, and it has made know difference, even when I reopen it as a .xlsm file rather than .xlsx, it stills makes no difference. I was wondering if it was because it was a shared file.

 

[Joe4]

and Design Mode cannot be selected

Not sure what you mean by this.
Are you saying that you are unable to Assign the Macro to your button?

Check the following:
- Did you place the VBA code in the correct workbook?
- Did you place the VBA code in a Standard Module in the VB Editor, or a Object Module (like SheetX or ThisWorkbook)?
- When assigning the macro to your Control button, when the "Assign Macro" box pops-up, are you seeing the Macro listed there?
If not, what do you have selected in the "Macros In" option in that box?

 

[Joe4]

I was wondering if it was because it was a shared file

Could be. Shared workbooks are limiting and sometimes problematic. As such, many experienced programmers (such as myself) avoid them, and rarely (if ever) use them.

You can find posts like this around the internet: http://blog.contextures.com/archives/2012/07/24/shared-workbook-limits-in-excel-2010/

Note, this link here mentions like in trying to use VBA in shared workbooks, sometimes it works better to save you VBA code in an XLA (MS Excel VBA Does Not Work in Shared / MultiUserEditing Mode). I have never done that myself, but if you really want to use a shared workbook, you may want to look into it.

 

[mccaco]

I've managed to get it working as I have switched off the shared workbook aspect. But I have just discovered that every time I change a figure (increase or decrease) in a cell which is linked to either B12,B13,B14,I8 or I13 that the figure displayed in J3 becomes 201320132013.

 

[Joe4]

The value in J3 shouldn't change once it is is set, because it is not linked to the other workbook (the macro is dropping in the hard-coded value).
How are your values in B12, B13, B14, I8, and I13 formatted? As number or text?
What is the formula in those cells?
Are the cells they are linked formatted as number or text?

You could try changing the code to:

mySum = Range("B12").Value + Range("B13").Value + Range("B14").Value + Range("I8").Value + Range("I13").Value

and see if that makes a difference.

 

[mccaco]

Thank you, the last suggestion worked. I want to change what the macro does so that instead of adding together the cells and displaying the value in the other workbook, it will copy a table in sheet 2 from the workbook just opened by the macro, and paste it into sheet 3 of the workbook which contains the button then closes the opened wb. I've read through loads of internet forums and guides but having started attempting VBA code for the first time at the start of the week, I am finding it difficult to write anything which doesnt have a walkthrough for me to follow. How would I go about changing the existing code?

The table being copied will constantly be having more rows added to it so is there a way when the button is pressed that the macro can automatically adjust the table range to incorporate the newly added data.

Thank you for giving up your time to help me to this stage, I'm incredibly grateful.