[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1210
  • Last Modified:

Excel macro opening from wrong location.

My macro always points to wherever I opened the spreadsheet from.  How can I get it to point to a specific path?

Thank you
0
cas_three
Asked:
cas_three
  • 3
  • 2
  • 2
  • +3
4 Solutions
 
Martin LissRetired ProgrammerCommented:
Can you show the macro please?
0
 
Shanan212Commented:
define a string

eg:

Dim FilePath as string

FilePath = "C:\My Folder\Folder\"

^change the path as you wish

then incorporate this file path with your macro call...

Eg:

Workbooks.Open Filename:= FilePath + "test.xlsx"
0
 
Patrick MatthewsCommented:
Seeing the code would help a lot.  In the meantime, you can use the ChDir command to change the current directory.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cas_threeAuthor Commented:
Ok I just realized I phrased this all wrong, I am really sorry.

So in an excel spreadsheet there is a button, that button is assigned a macro.  However, when I right click that button and choose Assign Macro, to see where the macro is pointing, it's the wrong location.  It's from where I opened the spreadsheet from.

I hope that makes some sense.
0
 
Shanan212Commented:
The fastest way to do this is

Start recording macro

Select your object. Then assign the correct macro.

It will look something like this (considering the macro name is 'clearall' and the object name is 'Picture 7' and change the 'worksheet1' as you wish)

    sheets("worksheet1").select
    ActiveSheet.Shapes.Range(Array("Picture 7")).Select
    Selection.OnAction = "clearall"

Then put the above code in the specific location:

Press Alt + F11
Right click on 'ThisWorkbook' on the panel on left hand side
Select 'View Code'

Copy paste the following

Private Sub Workbook_Open()
    sheets("worksheet1").select
    ActiveSheet.Shapes.Range(Array("Picture 7")).Select
    Selection.OnAction = "clearall"
End sub

Open in new window

0
 
NorieCommented:
What do you mean by where the spreadsheet was opened from?

Does it point at the folder that the file is in?
0
 
dlmilleCommented:
Make sure your macros are stored as PUBLIC macros in the same workbook.  Then, when you assign the button to the macro, select ThisWorkbook, then select the macro.  Do that for all your buttons.  Save the workbook.

When you open it back up, please advise if the references are correct.

Dave
0
 
cas_threeAuthor Commented:
Ok I'm not a macro guru at all, extreme novice.

When I open the .xls file, there are buttons that point to macros on a different spreadsheet, or should be pointing to macros on a different spreadsheet.

When I open the .xls file and click on of the buttons, I consistently have to right click the button, choose Assign Macro and point to the spreadsheet with the macro I need, for instance I point it too \\server\share\spreadsheetname!macroname.

It changes the \\server\share to wherever I open the .xls file from.

I need the .xls file to always point the macro to \\\\server\share\spreadsheetname!macroname without having to right click, choose Assign Macro and manually point to the location every time I open the spreadsheet.

Again, really hope that makes sense.
0
 
dlmilleCommented:
Can you upload a sample of your workbook with the macros?  The data can be cleaned up as the focus is on the buttons and macro assignments.

Dave
0
 
cas_threeAuthor Commented:
I actually got this to work by adding:

filetoopen = Application.GetOpenFilename("Path to Files(*.xls), *.xls")
 MsgBox CurDir

Thank you everyone!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now