VBA Code to Switch Between Workbooks and Get the Filename as Text without the Path

[UncleBajubjubs]

Hello, I currently have a workbook which another worker will enter information into, and which I will then pull information out of into a workbook I use. The workbook they have is a form in which they will fill in all necessary information about a project. I get the workbook from then, open it up, and my workbook copies all the data from it. My only issue is that some of them use an older version of the form, which has a cell's number format as 'text' when it should be 'number'. If I copy this, it messes up the data in my program, so I'm trying to fix the cell prior to copying the data.

I use
Dim filename As Variant
filename = Application.GetOpenFilename()

to open up their workbook to get the data, and I tried

Windows(filename).Activate
Range("P20").Select
Selection.NumberFormat = "General"

but it tells me Subscript out of range on that first line, as the filename contains the entire path of the file (C/User/Documents...etc.). Is there an easy way to remove the file path so that it is just the file name? Unfortunately I can't just have it be a set name, because the others working with it will name it whatever project they are working on.

This is my first post so I tried to have as much information as possible, thank you!

 

[MrKowz]

Welcome to the forums!

Couple ways to approach this. To answer your direct question, try using filename = activeworkbook.name

A more elegant approach would be to create a workbook variable and use that to reference that workbook. This way, you can easily reference that workbook in later parts of the code.

For example:

Public Sub foo()
Dim sWB     As Workbook
    
Set sWB = ActiveWorkbook

sWB.Range("P20").NumberFormat = "General"
End Sub

 

[UncleBajubjubs]

Welcome to the forums!

Couple ways to approach this. To answer your direct question, try using filename = activeworkbook.name

A more elegant approach would be to create a workbook variable and use that to reference that workbook. This way, you can easily reference that workbook in later parts of the code.

For example:

Public Sub foo()
Dim sWB     As Workbook
    
Set sWB = ActiveWorkbook

sWB.Range("P20").NumberFormat = "General"
End Sub

Hello, Thank you for the reply, I still have the issue though, as the activeworkbook will be the one I have opened, not the one that contains the data I need. I am not sure how to switch the other one to being the activeowrkbook.
Thank you!

 

[MrKowz]

Ahh, I believe I misunderstood your question. Are you using VBA to open this other workbook? Or are you opening the workbook and then running code from a different workbook?

 

[UncleBajubjubs]

So I am using a workbook called"Data Program". The other workers will fill out a workbook called "Form". I open up both Data Program and Form. I then run a macro in Data Program which has me select the file to pull data from. All of that works, and it pulls the data from Form into Data Program. The issue is that some of the workers are using an old version of Form in which a cell incorrectly has its number format set as "text" when it needs to be "general". I am trying to use the macro in "Data Program" to modify the cell in "Form". Were the file always named "Form", I would use

Windows("Form").Activate

to switch to Form and change it. However, the workers rename it match the project they are working on, and I will not be the only one using Data Program, so it needs a better solution than manually renaming the file each time. I've tried using

filename = Application.GetOpenFilename()

but that includes the full file path, so I can't use that in combination with

Windows().Activate

Thank you again!

 

[MrKowz]

I think I understand now. You're using the code to open a dialogue box which will open the file (since it isn't always named "Form"), and you need a way to parse out the workbook name.... or at least reference the workbook. That correct?

If so, lets go about it a slightly different route.

Try:

Public Sub foo()
Dim formWB      As Workbook
Dim filename    As String
filename = Application.GetOpenFilename()
Set formWB = Workbooks.Open(filename)
End Sub

Now, whenever you need to reference the Form workbook, you can use "formWB" in place of "Workbooks(name)."

For example, instead of Workbooks(name).Activate, you can use formWB.activate. Or instead of Workbooks(name).Sheets("Sheet1").Range("A1").Copy, you can use formWB.Sheets("Sheet1").Range("A1").Copy

 

[UncleBajubjubs]

That all works now, thank you!
Unfortunately, excel now correctly goes to cell in the workbook, but gives me

Unable to set the numberformat property of the range class.

I have

rfqwb.Activate
Call UnProtectWorkbook



Range("P20:AA20").Select


Selection.NumberFormat = "General"

And that last line is where the code breaks. Any clues?

 

[MrKowz]

Only thing I can assume is that the worksheet which contains the range you want to adjust the numberformat on is protected. Make sure to unprotect the worksheet (I see you have an unprotectworkbook sub you're calling, which might only be hitting it at the workbook level)

 

[UncleBajubjubs]

That seems to have fixed it, thank you!

 

[MrKowz]

Great! Thanks for the feedback. Have a good one!