Solved: Application Fails If Workbook Name Changes

[Carpiem]

Good Morning,

I am looking for another option if available:

Because the data comes from some other source, it needs to be Cleaned & Trimmed before a VlookUp is done on a column.

The way I run "CleanTrim" is fine until the "workbook.name" is changed.

I have no control of what name this workbook would be saved as... a number of people will be using this workbook.

The 2 pieces of code are as below:


[vba]
Sub LookUpGroup()
Dim LastRow As Long

Application.ScreenUpdating = False

'clean GroupCodes column
Columns("A:A").Select
Application.Run "'SalesTargets.xls'!Clean_Trim" ‘ This is the line I am trying to workaround.

Columns("B:B").Insert Shift:=xlToRight
Range("B1").Value = "Group"
Columns("B:B").HorizontalAlignment = xlLeft

Range("B2").Activate
Range("B2").Formula = "=VLOOKUP($A$1:$A$60000,GroupCodes!$A:$C,3,0)"
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown

With [A65536].End(xlUp).Offset(1).Resize(, [IV1].End(xlToLeft).Column)
.Formula = "=SUM(R2C:R[-1]C)"
.Value = .Value
.NumberFormat = "#,##0.00;[Red]#,##0.00"
.Font.Bold = True
.Interior.ColorIndex = 15
'In case Formulas are needed, comment out avobe code line.
End With
Sheets("Sales").Range("A65536").End(xlUp).Offset(0, 0).Value = ""
Sheets("Sales").Range("B65536").End(xlUp).Offset(0, 0).Value = "NetSales"

'Format Upper Table Borders
Dim r As Range
Set r = Range([B1], [B1].End(xlDown).End(xlToRight))
Borders r, xlThin
Set r = Nothing

'Lookup Formula as Values
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Copy
Range("B1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

Columns("C:I").ColumnWidth = 11
Columns("A:B").EntireColumn.AutoFit

'FillTable

Range("A2").Select

Application.ScreenUpdating = True
End Sub

----------------------------------------------------------------------------------------

Sub CleanTrim()
Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CleanTrimRg
oCell = Func.Clean(Func.Trim(oCell))
Next

End Sub
[/vba]

Thank you.... Carpiem

[lucas]

Could you browse to look for the file, maybe something like:
[VBA]
Dim fn As Variant, f As Integer
fn = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select One Or More Files To Open", , True)
If TypeName(fn) = "Boolean" Then Exit Sub
For f = 1 To UBound(fn)
Debug.Print "Selected file #" & f & ": " & fn(f)
Workbooks.Open fn(f)
MsgBox ActiveWorkbook.Name, , "Active Workbook Name:"
ActiveWorkbook.Close False
' close the active workbook without saving any changes
Next f
[/VBA]

[Carpiem]

Hi Lucas,

I think I picked up some of what you are suggesting.

So I could add code to set a permanent Directory, where the file would be opened from.

That would be an option.

But these workbooks travel between offices in different locations and there is bound to be grief.

Thank you... Carpiem

[lucas]

Let us know how it works out.

[Norie]

Where is CleanTrim actually located?

Is it in the workbook that might be renamed?

Do you actually need to specify the workbook here?
[vba]
Application.Run "'SalesTargets.xls'!Clean_Trim"[/vba]
Can't you just use this?
[vba]
CleanTrim[/vba]

[Carpiem]

Lucas,

Will do.

Carpiem

[Carpiem]

Hello Norie,

Yes I was using Call CleanTrim it worked fine. Just amended it to CleanTrim it works as well so I will stick with it.

What is the difference between them?

Thank you... Carpiem

[Norie]

Carpiem

There's no real difference for your code, but there may be circumstances where it does matter.

[lucas]

From post #3

Hi Lucas,

I think I picked up some of what you are suggesting.

So I could add code to set a permanent Directory, where the file would be opened from.

That would be an option.

But these workbooks travel between offices in different locations and there is bound to be grief.

Thank you... Carpiem

I think you misunderstood the code I posted before this post. The code or something similar to it will open a browse for file dialog box from which you can browse to and select the file no matter where it is located.....hope that makes sense.