change cell value on open workbook vba

i have a macro run on a seperate workbook which opens up another workbook.

how can i go about referencing the open workbook to run a command.

i.e i want to change the cell values of A5 on worksheet: "Avg Daily Vol Tab" to the filename (minus the file extension). then save.

on the open workbook.
eastsidemarketAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken ButtersCommented:
to reference any open workbook you can use the name of the workbook in your macro code like this:

Dim wb as workbook

set wb = workbooks("Name of Workbook here")

now the wb variable contains a reference to your other workbook, and you can use it to access the sheets of the workbook, and then the cells of the sheet.
0
eastsidemarketAuthor Commented:
what if the name of the workbook varies, but i have it called from a variable in a different sub.

how can i grab it from there?
0
FaustulusCommented:
This code will do the job.
Private Sub test()

    Dim Sp() As String
    
    With ActiveWorkbook
        Sp = Split(.Name, ".")
        .Sheets("Avg Daily Vol Tab").Range("A5").Value = _
                Left(.Name, Len(.Name) - (Len(Sp(UBound(Sp))) + 1))
    End With
End Sub

Open in new window

If you need this snippet to go into any existing code you may already have I can help you integrate it. Point is the reference to the "ActiveWorkbook". It would be suitable in this context where the newly opened workbook is automatically active,
            Workbooks.Open Fn
            With ActiveWorkbook
                Sp = Split(.Name, ".")
                .Sheets("Avg Daily Vol Tab").Range("A5").Value = _
                        Left(.Name, Len(.Name) - (Len(Sp(UBound(Sp))) + 1))
            End With
            CloseThisWorkbook

Open in new window

Place the declaration Dim Sp() As String among other Dim statements at the top of the procedure.
Make sure the procedure CloseThisWorkbook saves the file.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

FaustulusCommented:
Coming to think of it, this would also work,
            With Workbooks.Open(Fn)
                Sp = Split(.Name, ".")
                .Sheets("Avg Daily Vol Tab").Range("A5").Value = _
                        Left(.Name, Len(.Name) - (Len(Sp(UBound(Sp))) + 1))
            End With
            CloseThisWorkbook

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eastsidemarketAuthor Commented:
Faustulus-

I have the following and highlights (Fn) saying variable not defined. Looks like it can't pull that variable in from the CopyWorkbook sub:

basically my Button calls STARTREPORT, which will run CopyWorkbook, then once the sheet should work on a few items, i.e. the below..

Sub STARTREPORT()
CopyWorkbook
With Workbooks.Open(Fn)
                Sp = Split(.Name, ".")
                .Sheets("Avg Daily Vol Tab").Range("A5").Value = _
                        Left(.Name, Len(.Name) - (Len(Sp(UBound(Sp))) + 1))
            End With
            'CloseThisWorkbook
End Sub

Open in new window

0
Ken ButtersCommented:
Fn must be the name of the workbook.

you can declare it as a string and set a value to it... or you could pass it as a parameter...

Dim Fn as string

Fn = "Name of your workbook"
0
FaustulusCommented:
You might integrate the action into the CopyWorkbook routine. Right after the workbook is created there it will be the active one,
If the CopyWorkbook sub has the line Workbooks.Open Fn just replace it with the code I gave you or add the new code right below it. The new code doesn't need to be in its own procedure. The one you have will do fine:
Open the workbook
Insert the file name in A5
Close the workbook (Save)
All straight forward.
It's late for me now. See you tomorrow.
0
eastsidemarketAuthor Commented:
thanks guys.

here is where i am now. getting error on "UBound" Compile error - expected array

Sub CopyWorkbook()

    ' modify path as required
'    Const PathName As String = "H:\Reports"
    Const PathName As String = "H:\Futures\Macros\F&O Report"
    
    Dim SelFiles() As String
    Dim Fn As String                        ' File name
    Dim sp As String
    
    If GetSelectedFiles(PathName, SelFiles) Then
        Fn = NewFileName(SelFiles(0))
        If Len(Fn) Then
            Fn = WithSeparator(PathName) & Fn & ".xlsx"
            FileCopy SelFiles(0), Fn
            Workbooks.Open Fn
            With Workbooks.Open(Fn)
                sp = Split(.Name, ".")
                .Sheets("Avg Daily Vol Tab").Range("A5").Value = _
                        Left(.Name, Len(.Name) - (Len(sp(UBound(sp))) + 1))
            End With
            'CloseThisWorkbook
        Else
            MsgBox "No valid file name was supplied.", _
                   vbCritical, "Can't rename"
        End If
    End If
End Sub

Open in new window

0
Ken ButtersCommented:
sp should be an array... but I notice you have it Dim'd as a string.

Try changing from :

Dim sp as String

to :

Dim sp as String()
0
FaustulusCommented:
You should declare Sp as array, such,
Dim Sp() As String

Remember, declare variables using caps and smalls.
Type variables using lower case only.
If VB doesn't change the case as you type you have entered the name incorrectly.
Guaranteed to work against headache. :-)
0
Ken ButtersCommented:
yes... I put the parens in the wrong place... Faustulus has it right..

Dim Sp() as String
0
eastsidemarketAuthor Commented:
thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.