Solved: ThisWorkbook.Path

[R1C1]

My workbook opens with:
Private Sub Workbook_Open()
Application.Caption = ThisWorkbook.Path
End Sub

When I run a macro on it containing:

Workbooks.Open Filename:=ThisWorkbook.Path & "\Master.xls"

I get a Master.xls could not be found, even though it is in ThisWorkbook.Path

Any help would be greatly appreciated.

Thanks,

R1C1

[Bob Phillips]

My workbook opens with:
Private Sub Workbook_Open()
Application.Caption = ThisWorkbook.Path
End Sub

When I run a macro on it containing:

Workbooks.Open Filename:=ThisWorkbook.Path & "\Master.xls"

I get a Master.xls could not be found, even though it is in ThisWorkbook.Path

Where are you running the 2nd bit of code from? Are you sure ThisWorkbook.Path in that workbook is the same, or even any value at all?

[R1C1]

Thanks for your reply xld.

I am so new at this that I don't quite understand what you mean about "any value at all".

Here is what I am trying to do. I have an equipment worksheet. I run a macro on it adding columns for my controls guy to enter his controls info. When he is complete I want to have a macro that compiles the info and dumps it into a Master.xls file in the same directory. I have everything fine tuned except the path to the Master.xls. I thought by setting thisworkbook.path when the file opens I would be able to show a path to Master.xls as thisworkbook.path &"\Master.xls". But this does not work. Absolute path works great but forces a save-as since this forces the master.xls location to be permanent. I have a standard project folder, each containing a Master.xls file.

Am I way offline with this?

Thank you very much for the help!

R1C1

[lucas]

Could you post the sub with this code:
[vba]
Workbooks.Open Filename:=ThisWorkbook.Path & "\Master.xls"

[/vba]
so we can see how you are using the line of code?

[R1C1]

Thank you for your reply lucas. All help is greatly appreciated. I am sure I am using it wrong, maybe you can straighten this newbie out, lol.

R1C1

[VBA]Sub TransferToMasterDeviceList()
'
' TransferToMasterDeviceList Macro
'
'
Application.ScreenUpdating = False
ActiveWorkbook.Save
Columns("T:Z").Select
Selection.Copy
Columns("AI:AO").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("AI:AO").Select
Selection.Copy
Workbooks.Open Filename:=ThisWorkbook.Path & "\Master.xls"
Columns("A:G").Select
ActiveSheet.Paste
On Error Resume Next ' In case there are no blanks
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub[/VBA]

[lucas]

This works fine for me as I understand it. I think you have a spelling error. the name in the code must match exactly the name of the master.xls....if thats not the problem then something else is causing the problem

see attached

[R1C1]

Thank you lucas,

I just realized my mistake. The module containing the macro has to be located within the current workbook. I presently have the macro located in Personal.xls and when the thisworkbook.path statement is run it is looking in the folder that contains Personal.xls instead of the folder of the currently open workbook. I think I need to set the code to the macro in each file at the same time I am setting the workbook_open procedure. I am currently using:\

[VBA]Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, _
"Application.Caption = ThisWorkbook.Path"
End With[/VBA]

to set the Workbook_Open procedure. How would I go about adding the macro code to the workbook at the same time?

It would be greatly appreciated.

R1C1

[lucas]

Hey R1C1,
That makes more sense. I don't have time to look at this tonight, sorry but I would start by changing your call from thisworkbook.path to activeworkbook.path and you should be able to call it from your personal.xls as you have been trying to do.

That is your TransferToMasterDeviceList macro can be in your personal and should run as you desire on the open workbook and copy to the master in the same directory as the open workbook if you change this line:
[VBA]
Workbooks.Open Filename:=ThisWorkbook.Path & "\Master.xls"
to
Workbooks.Open Filename:=Activeworkbook.Path & "\Master.xls"

[/VBA]
I think....if you have a chance to test it before I get back to it let me know how it goes.

[R1C1]

Cooooool beans lucas, U da man. This works much better, allowed me to remove the procedure that I was using in another macro to set "thisworkbookpath" on the workbook. Now I do not have to install any procedures or modules into the active spreadsheet, thus lowering file size and ease of use.

Thanks to you lucas, and all others for their input. I just recently found this site and this VBA newbie says, "I'll be baaaack"!

Mark this one solved.

[lucas]

Glad to help neighbor (your in texas right, okie here)

I will mark this solved.

[R1C1]

In Ohio at present but from Texas. You know what they say: "You can take me out of Texas but you can't take the Texas out of me!"

Thanks again,

R1C1

[SamT]

@ BOB72120

Your question has been moved to http://www.vbaexpress.com/forum/show...ter-Copy-Paste