Edit Filename Path?

Cyberseeker · #1 03-27-2017, 02:22 AM

I have a macro that I haven't used for a long time, and I would like to get it up and going again. It comes to a grinding halt where my code reads,

Code:

Workbooks.Open FileName:=fileloc

I receive a message, Run-time error '1004': and it tells me my file could not be found. Yes, Ive changed my file location, and its now up in the sky (onedrive) . Thats where I want it, but my question is: how do I edit my Filename? I dont even know where to find it, what less edit it. Alternatively, is it better to write the path directly into the macro?

Any help appreciated.

Cyberseeker · #2 03-27-2017, 02:27 PM

Here is my error message. Fixing the path is probably easy but Im at a loss.

excelmacroerror.PNG

Logit · #3 03-27-2017, 03:28 PM

Probably need to see all of your code in that macro rather than just one line.

However ... two probable causes:

#1 - The file is no longer where you remember it being and Excel can't find it.

#2 - Part of your code is missing.

Regarding #2 ... example code:

Code:

Sub ImportWorksheet() 
    ' This macro will import a file into this workbook 
    Sheets("Sheet1").Select 
    PathName = Range("D3").Value  '<--- could easily be the actual path instead of the path written to a cell
    Filename = Range("D4").Value  '<-- could be the actual filename instead
    TabName = Range("D5").Value 
    ControlFile = ActiveWorkbook.Name 
    Workbooks.Open Filename:=PathName & Filename 
    ActiveSheet.Name = TabName 
    Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1) 
    Windows(Filename).Activate 
    ActiveWorkbook.Close SaveChanges:=False 
    Windows(ControlFile).Activate 
End Sub

Note there is a reference made to Filename and Pathname so Excel knows
what is meant by Filename:=PathName & Filename

Does you code provide the reference for fileloc ?

NoSparks · #4 03-27-2017, 04:40 PM

Isn't your My Documents folder actually like this ?

C:\Users\Cyberseeker\Documents\

Cyberseeker · #5 03-27-2017, 05:04 PM

Since I got Windows 10 and shunted my files around, it ended up like this:

Desktop\Cyberseeker\Onedrive\5L2F\Pricelist.xls

My problem is that I dont know where to edit my original code?

Logit · #6 03-27-2017, 05:55 PM

The line highlighted is : Workbooks.Open FileName:=fileloc

fileloc is a variable name that is referenced somewhere up above that line.

It most likely looks like : set fileloc = "and the old file path here"

It should look like : set fileloc = "C:\Users\Cyberseeker\Documents\Pricelist.xlsx "

If you can't locate that line (set fileloc = "and the old file path here"), post the entire macro code here so we can review it.

Cyberseeker · #7 03-28-2017, 01:03 AM

I found the path I was looking for. It was on a different sheet to where the macro button was. I changed it accordingly, and my PC now finds the path as expected. However, my notebook doesn't yet. Strange,

because they are the same file on my Onedrive.

Thanks for the help. Im learning.

Logit · #8 03-28-2017, 07:23 AM

You are welcome. Cheers

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word does not show filename during edit	es35	Word	5	10-29-2016 12:12 AM
use full path for filename	ruggb	Word	1	08-21-2015 05:10 AM
How to: Have two templates cross-reference each other independent of filename path	HighSierra	Word	6	05-06-2015 07:04 PM
Change old path to new path (batch)	NobodysPerfect	Word VBA	2	08-14-2014 10:09 PM
MarkAsFinal - 'Edit Anyway' edit popup issue	GovindRS	PowerPoint	1	06-20-2011 06:43 AM