Free Microsoft Excel 2013 Quick Reference

Error 76 -- Path not found

Hey everyone. I have a macro in my program that is supposed to Zip up all of the temp files and save them into a folder on a different drive. Well, my company wants it to automatically archive all yearly quotes in a folder...So I tried to do this with the Year() method on the 4th line...The folder is named 2010 on the drive, and the year function is supposed to be in "YYYY" format...do you guys see any reason that I'm getting an Error 76 -- Path not found?

On Error GoTo NoQuoteDirectory
  'Place this quote into the proper year...  Dim x As Date
  Dim today As String
  today = Year(x)
  
  TargetDirectory = "Q:" + today + "" + Directory
  ChDir TargetDirectory
On Error GoTo 0
Exit Sub
'Error handler if Q: drive can't be found (not on network)
NoQDrive:
  ChDrive "C"
  ChDir "C:"
  Exit Sub
'Error handler if quote subdirectory can't be found
NoQuoteDirectory:
  CreateDirectory = MsgBox("Quote directory cannot be found - create new directory?", vbYesNo, "QUOTE DIRECTORY")
  If CreateDirectory = vbNo Then
    ChDir "Q:"
    Exit Sub
  Else
    MkDir TargetDirectory   'Make new quote subdirectory
    ChDir TargetDirectory
    Exit Sub
  End If
End Sub


Post your answer or comment

comments powered by Disqus
Hi,

I an trying to write a macro to copy the last row from one workboo1 and paste it in workbook2. After pasting it needs to rename the second workbook in a new folder.
Folling is the code.I am experiencing some problems like.
error 76 Path not found.
When i am trying to create a new folder.First i need to check if that folder exists or not.if it does it needs to save this workbook2 in that folder,otherwise create the new folder with that name and then save it.
Please help.

Sub Transferdata()
Workbooks.Open Filename:="C:TestmacroSweweeklstatusrep.xls"


Set c = Workbooks("Sweweeklstatusrep").Sheets("sheet1") 'This is the workbook you want to copy the data
from.
Set d = Workbooks("SweAorSummary.xls").Sheets("sheet1") 'This is the destination workbook

Dim LastRow
Dim NewRow

'column B in both workbooks to find the last row. 
LastRow = c.Range("B65536").End(xlUp).Row
NewRow = d.Range("B65536").End(xlUp).Row


'The destination workbook has to find the last row, and then paste the data below it.
'That is why +1 is used. Otherwise, you would write over the existing data.
'd.Rows(NewRow + 1).Value = c.Rows(LastRow).Value
d.Range("B" & NewRow + 1, "E" & NewRow + 1).Value = c.Range("B" & LastRow,
"E" & LastRow).Value

'Acitvate the workbook you just copied the new line to.
Workbooks("SweAorSummary.xls").Sheets("sheet1").Activate

'Find the last row of your new line and get the date.
Dim GetDate
Dim MyDate
'Dim sFileName As String
 Dim sPath As String

GetDate = d.Range("E65536").End(xlUp).Row
MyDate = d.Range("E" & GetDate).Value

 'Const sPath As String = "C:TestmacroMyDate"
 'sFileName = Range("A1").Value & Range("a2").Value
 sPath As String
 sPath = "C:Testmacro" & MyDate
 If Len(Dir(sPath)) = 0 Then
 MkDir sPath
    ChDir sPath

'I want this workbook to be saved in the new folder.
Workbooks("SweAorSummary.xls").SaveAs Filename:="AOR Summary" & MyDate & ".xls",
FileFormat:=xlWorkbookNormal
End If
    
End Sub


Hi All,

I am getting the following run time error when trying to use MoveFolder from the scripting runtime library:

run-time error ‘76’
path not found

The source and destination paths are picked up out of cells and I think the spaces in the name or the length of the path names may be causing the problem.

The source folder looks something like this (contains 50 characters):
K:Example123 Folder1AB CD ESubfold,XX abcdefgh

The destination folder looks something like this (contains 60 characters):
K:Example123 Folder1XX New123AB CD ESubfold,XX abcdefgh

The code is pretty basic:


	VB:
	
) 
    Dim FSO As Scripting.FileSystemObject 
     
     
    Set FSO = New Scripting.FileSystemObject 
     
     'some code here removes the trailing backslashes on the
     'end of the pathnames
     
    FSO.MoveFolder Sourcepath, Destinationpath 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have double checked the source path name exists. I have also tried doing it manually with the path names being entered in as text but i keep getting the error.

Any ideas?
Brad

Hi

Please excuse my ignorance.

I have a sheet which was working fine, but now the userform wont initialize.

It is listed under Forms and the property window has the correct name receiptEntry

Sub dataentry()
receiptEntry.Show
blok = False
End Sub

The sheet works fine until this sub is called then I get the 'Path Not Found' Error with receiptEntry.Show Highlighted

I also get run time error '76' Path not found if I try to F5 through the form code.

Please help

Thanks Duncan

Hi All

Hopefully fairly straight forward this. Can't see the wood for the trees today!

I've followed a couple of the FSO threads and come up with the code below however I keep getting a 'Runtime Error 76 - Path not found'.


	VB:
	
 scan_station() 
     
    Dim FSO As New FileSystemObject 
    Dim dest As String 
    Dim dname As String 
     
    dest = "L:eBISScan Station" 
    dname = "Scans(" & Date & ").csv" 
     
    FSO.copyfile "H:todaysscans.csv", [COLOR="Red"]dest & dname[/COLOR] 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It hangs at the area coloured red. However if I hard code the full path and name as I've done with the source information it works fine.

TIA,

Steve

Im useing the below code to assign a file path to a variable. The variable is then called upon furhter in the code, however the filepath is not found. Whats confusing me is that the roll over of the variable sting shows the correct filepath, but when the macro attempts to open the folder useing this defined string I get a run-time error as below.
Any ideas?

Run-time Error '76'
Path Not Found

Dim DrawdownPath As String

Sub AssignPathsToVariables(strDate As String, Month As String)

Year_Month_Path = "20" & Mid(strDate, 7, 2) & "" & Format(strDate, "mm_mmm") &
"" & strDate & ""
DrawdownPath = "vs1alpfc1bkcPMUMIS ReportsDrawdown ReportResults" & Year_Month_Path

End Sub




Sub Drawdown()
'
' Drawdown Macro

ChDir _    ''''Error Here'''''
        "DrawDownPath"    Workbooks.Open Filename:= _
        "DrawDownPathFiLePaTh.xls"
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select


I am hoping that someome here can help me out with a problem I am
having.

We distribute our Excel sheets across the nation via a Lotus Notes
database document. In the Excel sheets, when launched, I have some code
to see if a directory exists on the Workbook_Open and the
Workbook_BeforeClose events:

If Len(Dir("MyPathMyDB.mdb")) = 0 Then Exit Sub

On my server it works as intended.

When the user at another plant opens the document on a different
server, they get a Path Not Found error. The plants other than out
Corporate facility shouldn't have access to this. Why is this? Is there
a solution?

Hi,

I am new to Excel VBA. I bought a book about VBA in Excel and tried to study the code which came with the book. However, when I try to run the macro, an error message occurred :" Run-time error '53': File not found".

I then pressed the debug button and it went to the red line below:

	VB:
	
 Arbitrage_Reset() 
     
    Dim ArbPrj As New ArbCls 
    [COLOR="Red"]ArbPrj.prjOpen ActiveWorkbook.FullName, ActiveSheet.name[/COLOR] 
    ArbPrj.Reset 
    ArbPrj.prjClose 
    ActiveSheet.Cells(1, 1).Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
where the path of ActiveWorkbook.FullName is "c:vba2000.xls". I tried to replaced ActiveWorkbook.FullName with the full path name, but problem still persisted.

One strange thing is that I am using Office 2000 and Windows XP Simplified Chinese version. I ran the macro on another machine with Windows XP Japanese version + Office 2000, the same problem appeared. However, on a very old machine with Windows 2000 English version + Office 2000, the macro ran faultlessly. I asked a friend who was using Office 2003+ Windows XP Italian version to test the macro for me, he didn't have any problem.

Does the problem have anything to do with the Asian OS system? I tried to format the HD and reinstall Win XP Chinese version, but the problem is still there.

Hi guys,

Could someone please help me fix this error ?

Path not found

 here is my program if that helps: 

Option Explicit
Sub CreateFolders()
Dim Ws1 As Worksheet
Dim Path As String
Dim cell As Range
Dim BrowseFolder As FileDialog
Dim FolderName As Variant
Set Ws1 = ThisWorkbook.Sheets("SL1-9")
Set BrowseFolder = Application.FileDialog(msoFileDialogFolderPicker)
With BrowseFolder
.InitialFileName = "C:Desktop"
.Title = "Browse Folders"
.Show
If .Show = -1 Then
    FolderName = .SelectedItems(1)
Else
    FolderName = ""
End If
End With
MsgBox FolderName
If DirExists(FolderName) = False Then
MkDir FolderName
'SetAttributes("FolderName", vbReadOnly, False)
End If
For Each cell In Ws1.Range("C:C")
    If cell.Value <> "" Then
        If DirExists(FolderName & cell.Value & "" & cell.Offset(0, 7).Value) = False Then _
        MkDir FolderName & "" & cell.Value & "" & cell.Offset(0, 7).Value
    End If
Next cell
End Sub
Public Function DirExists(ByVal DirName As String) As Boolean
Dim Dummy As String
On Error Resume Next
If Right(DirName, 1) <> "" Then DirName = DirName & ""
Dummy = Dir$(DirName & "*.*", vbDirectory)
DirExists = Not (Dummy = "")
End Function
Thanks a lot, Boris

Hello,

This thread was born out of my frustration and exhaustion of trying to find out why my macro (which is supposed to loop through a series of text files) would not work. Even worse, it would work once and then stop working. The constant error message was Runtime error '53': File not found. Online searching for a quick fix, repairing my registry, re-installing Office, and trying the same file on multiple different computers did not resolve the issue. As a last ditch attempt, I tried something desperate... I saved my code in text file as unicode so that it would only save the letters, numbers, etc without anything else and opened a fresh new workbook and pasted it. All of a sudden, the problems went away. I tried this new file on multiple computers (all running Office 2007 under Windows XP) and it worked just fine.

If I stopped here then it would be a happy ending, but unfortunately I ran the macro on several computers running Office 2007 running under Windows Vista, and sure enough the problem re-appeared: Runtime error '53': File not found.

My question is: Has anyone had this problem with Vista before when trying to loop through text files, or any othe file type for that matter? I know the files are there because I see them and I can open them, and if I replace the loop with the file path or use Application.GetOpenFilename("Text Files,*.txt"), it works and so the macro can see and open the files, but it just can't do it with the loop.

Any suggestions? (And sorry for the long post, but if I can help just one person to not to have to go through the misery that I went through, then it will have been worth it)

abousetta

P.S. attached is the new file that works under XP, but not vista and a text file that contains the needed material

I'm using Office 2007 under Windows 7. I have a button on an Excel worksheet which shells to the Snipping Tool to allow the user to capture part of the screen:-

	VB:
	
Shell "c:windowssystem32snippingtool.exe", vbNormalFocus 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This returns a Run-time error 53 File not found. The same happens if I run that command from the Immediate window (or in fact from MS Access). However if I put Notepad.exe in the Shell command, Notepad starts without a hitch.

SnippingTool.exe is definitely in C:WindowsSystem32 and it runs okay from the Start Run... button. It also works okay under Vista.

Why should Notepad run but not the Snipping Tool? Has anyone else come across this? Can anyone with Windows 7 confirm their system exhibits the same behaviour?

TIA,
R.

We are currently using an Access database which was written by an outside company. (which no longer exsist )

We have been using it daily for months, but we now get the following error.

Run-time error '53'

File not found.

This is where it stops.


	VB:
	
If (Right(cmboFilesAvailable, 3)) = "zip" Then 
    DoCmd.Hourglass True 
    unZipCommand = Driveletter & ":DeckingRLSD_Interfacecopyme.bat " & _ 
    Driveletter & " " & _ 
    Left(cmboFilesAvailable, Len(cmboFilesAvailable) - 4) 
    Shell (unZipCommand) 
     
     
    PauseTime = 20 ' Set max duration to wait for the unzip to complete.
    Start = Timer ' Set start time.
    Do While (Timer < Start + PauseTime) And (Dir(Source) = "") 
        DoEvents ' Yield to other processes.
    Loop 
    Finish = Timer ' Set end time.
    TotalTime = Finish - Start ' Calculate total time.
    DoCmd.Hourglass False 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The Shell (unZipCommand) gets highlighted when the error message gets displayed.

Also (not sure if this is related), but the file size has grown to nearly 80mb, i was told the way it was designed this file should not really grow in size. (our old backup has stayed at 4mb)

We also have several older versions of this file. I have tried most of them and get the same error.

The files that i am trying get to do exsist in the right locations.

Daz

hi all,
I wrote a program in Excel that calls a form to load when an action happens on a spreadsheet page, the code I used to call the form from is


	VB:
	
sqe_portal.UserForm_Initialize 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
however since upgrading to MSoffice 2003 i get the runtime error '53' file not found. the files still their...? any ideas?

Thanks.
Mathew.

I have some code that, although works fine in Excel 2003, does not in Excel 1997. I receive this error when I try running it:

COMPILE ERROR:
NAMED ARGUMENT NOT FOUND

	VB:
	
 HPVAL() 
    Dim r As Range, myStr As String 
    myStr = "HP" 
    Set r = Cells.Find(What:=myStr, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
    False, SearchFormat:=False) 
    If Not r Is Nothing Then 
        r = r.Value 
        While Not r Is Nothing 
            Set r = Cells.FindNext(r) 
            If Not r Is Nothing Then 
                r = r.Value 
            End If 
        Wend 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It looks like Excel is getting hung up on the "SearchFormat:=" portion of the code.

Help!

I am new to VBA and am creating a form for trouble ticket tracking. I have assigned a macro to a command button to call a form but when I click the button I get the run time error 424 object not found. Any help on what I am missing would be appreciated. Below is the code.

	VB:
	
 CountThru, TicketNum, PrintTicket 
 
Private Sub cmdCancel_Click() 
    Unload Me 
End Sub 
 
Private Sub cmdClearForm_Click() 
    Call UserForm_Initialize 
End Sub 
 
Private Sub cmdOK_Click() 
     
    Application.ScreenUpdating = False 
    ActiveWorkbook.Sheets("Trouble Ticket").Activate 
    Range("A4").Select 
     
    If IsEmpty(ActiveCell) = True Then 
        TicketNum = 1000 
    End If 
     
    CountThru = 0 
     
    Do 
        If IsEmpty(ActiveCell) = False Then 
            ActiveCell.Offset(1, 0).Select 
            CountThru = CountThru + 1 
        End If 
    Loop Until IsEmpty(ActiveCell) = True 
     
    TicketNum = 1000 + CountThru 
     
    ActiveCell.Value = TicketNum 
    ActiveCell.Offset(0, 1) = txtReceived.Value 
    ActiveCell.Offset(0, 2) = txtReceived.Value 
    ActiveCell.Offset(0, 3) = txtUser.Value 
    ActiveCell.Offset(0, 4) = txtSite.Value 
    ActiveCell.Offset(0, 5) = txtCall.Value 
    ActiveCell.Offset(0, 6) = txtPriority.Value 
    ActiveCell.Offset(0, 7) = txtDescription.Value 
    ActiveCell.Offset(0, 8) = "No" 
    ActiveCell.Offset(0, 9) = txtOwner.Value 
    ActiveCell.Offset(0, 10) = txtApplication.Value 
    ActiveCell.Offset(0, 11) = txtResolution.Value 
    ActiveCell.Offset(0, 12) = txtResolved.Value 
    ActiveCell.Offset(0, 13) = txtResolved.Value 
     
    PrintTicket = MsgBox("Do you want to print the ticket?", 4, "Laptop Check Out") 
    If PrintTicket = vbYes Then 
        Call Print_Ticket 
        Call UserForm_Initialize 
    Else 
        Call UserForm_Initialize 
    End If 
     
    Range("A4").Select 
    Application.ScreenUpdating = True 
End Sub 
 
Private Sub InitialReport_Click() 
     
End Sub 
Private Sub UserForm_Initialize() 
     
    txtCall.Value = "" 
    txtPriority.Value = "" 
    txtDescription.Value = "" 
    txtReceived.Value = "" 
    txtReceived.Value = "" 
    txtUser.Value = "" 
    txtSite.Value = "" 
    txtOwner.Value = "" 
    txtApplication.Value = "" 
     
End Sub 
 
 
Private Sub Print_Ticket() 
     
    ActiveWorkbook.Sheets("Print Ticket").Activate 
    ActiveSheet.PageSetup.PrintArea = "$A$1:$B$22" 
    Range("B4").Select 
     
    ActiveCell.Value = TicketNum 
    ActiveCell.Offset(1, 0) = txtReceived.Value 
    ActiveCell.Offset(2, 0) = txtReceived.Value 
     
    ActiveCell.Offset(4, 0) = txtOwner.Value 
    ActiveCell.Offset(7, 0) = txtUser.Value 
    ActiveCell.Offset(8, 0) = txtSite.Value 
    ActiveCell.Offset(9, 0) = txtCall.Value 
    ActiveCell.Offset(10, 0) = txtPriority.Value 
     
    ActiveCell.Offset(13, 0) = txtProblem.Value 
    ActiveCell.Offset(15, 0) = txtResolution.Value 
     
    ActiveSheet.PrintOut 
     
    Call Clear_Ticket 
     
    ActiveWorkbook.Sheets("Trouble Ticket").Activate 
     
End Sub 
 
Private Sub Clear_Ticket() 
     
    Range("B4").Select 
     
    ActiveCell.Value = "" 
    ActiveCell.Offset(1, 0) = "" 
    ActiveCell.Offset(2, 0) = "" 
     
    ActiveCell.Offset(4, 0) = "" 
    ActiveCell.Offset(7, 0) = "" 
    ActiveCell.Offset(8, 0) = "" 
    ActiveCell.Offset(9, 0) = "" 
    ActiveCell.Offset(10, 0) = "" 
     
    ActiveCell.Offset(13, 0).Value = "" 
    ActiveCell.Offset(15, 0).Value = "" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I meant to say I get a object not found error. When I click the command button and try and debug this is the code below

	VB:
	
 OpenNewTicket() 
    NewTicket.Show 
End Sub 
Sub OpenUpdateSelectorForm() 
    UpdateSelector.Show 
End Sub 
Sub OpenUpdateTicketForm() 
    UpdateTicket.Show 
End Sub 
Sub OpenCompileReportForm() 
    CompileReport.Show 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The form NewTicket is avialable so I am not sure why I get object not found.

Thanks

Hello,
The macro below used to work in excel, however, for some reason it now
gives me an error. The error reads:

Compile error: Named argument not found.
The Macro is as follows:
Sub First_Macro_Rate_Sheet_Comparison()
'
' First_Macro_Rate_Sheet_Comparison Macro
'
'

'
Cells.Replace What:="Cell", Replacement:=" - Cellular", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:=" - Cellularular", Replacement:=" - Cellular",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Mobile", Replacement:=" - Cellular",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Can anyone please help me in figuring out how to solve this error? The
program shows an error at SearchFormat, any ideas?

Thanks,
A.S.

Im trying to create a data form to upload details to spreadsheet. I have created the form in VBA but when I try and play it i get the error message "run time error 424 object not found." I cant seem to find the problem in the code and have stared at it for so long I feel a bit like an eskino staring at the snow. Any suggestions will be welcome below is the code

Private Sub cmdCancel_Click()


    Unload Me

End Sub



Private Sub cmdClearForm_Click()

    Call UserForm_Initialize

End Sub






Private Sub cmdOK_Click()

    ActiveWorkbook.Sheets("Sheet 1").Activate

    Range("A3").Select


    ActiveCell.Value = cboCSR.Value

    ActiveCell.Offset(0, 1) = txtDate.Value

    ActiveCell.Offset(0, 2) = txtCaseNo.Value

    ActiveCell.Offset(0, 3) = txtCallbackDate.Value

    ActiveCell.Offset(0, 4) = txtTimeWindow.Value
    
    ActiveCell.Offset(0, 5) = cboAssign.Value
    
    ActiveCell.Offset(0, 6) = txtContactNo.Value
    
    ActiveCell.Offset(0, 7) = txtOrangeNo.Value



        End If

    End If

    Range("A3").Select

End Sub


Private Sub UserForm_Initialize()

txtName.Value = ""

txtDate.Value = ""
    
    With cboCSR

        .AddItem "Andy"

        .AddItem "Ian"

        .AddItem "Vici"

        .AddItem "Marcia"

        .AddItem "Nina"

        .AddItem "Emma"

        .AddItem "Wendy"

        .AddItem "Dave"

        .AddItem "George"

        .AddItem "Nick"

        .AddItem "Alina"
        
        .AddItem "Janey"

        .AddItem "Debbie"

        .AddItem "Sharon"
        
        .AddItem "Mark M"

        .AddItem "Mark B"

        .AddItem "Kayley"

        .AddItem "Andrea"

        .AddItem "Gill"

        .AddItem "Suzanne"

        .AddItem "Linda"

        .AddItem "Sheena"

        .AddItem "Alison"

        .AddItem "Katie"

        .AddItem "Paula"
        
        .AddItem "Joe"

        .AddItem "Gary"

        .AddItem "Annie"
        
End With

    cboCSR.Value = ""
    
    txtCaseNo.Value = ""

    txtCallbackDate.Value = ""

    txtTimeWindow.Value = ""
    
   With cboAssign

        .AddItem "Andy"

        .AddItem "Ian"

        .AddItem "Vici"

        .AddItem "Marcia"

        .AddItem "Nina"

        .AddItem "Emma"

        .AddItem "Wendy"

        .AddItem "Dave"

        .AddItem "George"

        .AddItem "Nick"

        .AddItem "Alina"
        
        .AddItem "Janey"

        .AddItem "Debbie"

        .AddItem "Sharon"
        
        .AddItem "Mark M"

        .AddItem "Mark B"

        .AddItem "Kayley"

        .AddItem "Andrea"

        .AddItem "Gill"

        .AddItem "Suzanne"

        .AddItem "Linda"

        .AddItem "Sheena"

        .AddItem "Alison"

        .AddItem "Katie"

        .AddItem "Paula"
        
        .AddItem "Joe"

        .AddItem "Gary"

        .AddItem "Annie"
        
End With

    cboAssign.Value = ""
    
    txtContactNo.Value = ""

    txtOrangeNo.Value = ""
    
End Sub


I'm trying to save a spreadsheet using VBA which will also check that the directory exists and create it if not, all based on cell values in my spreadsheet. When I run my code it comes up with Run Time Error 76: Path not found.

I'm not sure what I doing wrong with this code, so anyhelp would be greatly appreciated.


	VB:
	
 
fname = ActiveSheet.Range("E2").Value 
dname = ActiveSheet.Range("G2").Value 
dname2 = ActiveSheet.Range("H2").Value 
Windows("Register Template.xls").Activate 
ChDir ("H:") 
If Len(Dir(dname2, vbDirectory)) = 0 Then 
    MkDir dname2 
End If 
ChDir dname2 
ActiveWorkbook.SaveAs Filename:=fname 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Is there a way of opening a file for output and using variables to name it?

For instance:


	VB:
	
 #nFileNum 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
when I run this is brings up a 'Run time error '76'' "Path not Found"

Hi all - I've been using these forums for a while now and appreciate all the good advice I've read so far! I'm truly impressed by the caliber of posts on this site.

I now have a question I cannot find the answer too.

For a daily report I generate at work, I would like to add a way to update the links in that sheet to another sheet that changes it's name daily. So far I've got the following and it's not working:

Sub links()

'finds directory to refer to
ChDir "O:SwapsfoEXOTICSMAD_REPORTS_" & Format(Range("AH1"), "yyyy") & "EXERCISE" & Format(Range("AH1"), "mm yyyy")

'updates link
ActiveWorkbook.ChangeLink Name:= _
"O:SwapsfoEXOTICSMAD_REPORTS_2010EXERCISESEP 10EX_Sep_28_2010.xls", _
NewName:="O:SwapsfoEXOTICSMAD_REPORTS_" & Format(Range("AH1"), "yyyy") & "EXERCISE" & Format(Range("AH1"), "mmm yyyy") & _
"EX_" & Format(Range("AH1"), "mmm_dd_yyyy") & ".xls"", Type:=xlExcelLinks"
End Sub

For the first part, I get a Run-time error 76, "Path not found". If I disable that first part with an apostrophe, the second part does not update the link..

Please advise.

Thanks in advance for the efforts!

Upon opening a particular spreadsheet I need to force open another spreadsheet containing lists used by data validation formulas which provide drop down lists for data entry. I only want to store one copy of the lists spreadsheet out in the company intranet. The Auto-Open macro gives me a Run Time Error '76' Path not found; the debugger hilites the ChDir statement where I recorded the file location. If I put the list spreadsheet on my hard drive it opens, although not so "quietly" as I'd like. (Apparently the record function does not know I responded "yes" to open the list spreadsheet as read only.) My questions are: 1. can the auto_open macro locate a file not stored locally but specified by URL? If so, how do I code it? 2. Can I stop the "open as read-only" prompt if I must have all users save a copy on their hard drive (a maintenance nightmare)? I don't know VBA but can modify simple things. Thanks in advance for any assistance.

I have a spreadsheet (Excel 2003), that I use as a "Document Transmittal" to document the file's I've sent to a client. I'm not that good at coding in VB and had some help developing the macro in the attached spreadsheet. My macro has tons of "Remarks" to help me figure out how this is all working. The spreadsheet is stored as either an XLT file on the users PC, or as a XLS file in the directory on our network.

The directory structure is always:T:Project Contracting09-058 - Project 101 - Contract
02 - External
03 - DocTrans03.1 - DocTrans - 01
03.2 - DocTrans - 02If the spreadsheet is in the directory, it's always in "02 - ...", and the documents that we transmit are always in the sub-directory under "03..."

The spreadsheet has 3 columns:Document Number (Column B)
Revision Number (Column C)
Document Name (Column D)We have a standard "File Naming Convention" so a drawing that details the assembly of a widget might look like:
09-058-DW-0001-R0A - Detail, Widget Assembly.PDF

where:
09-058 = Project Number (09 = Year, 058 = 58th contract for given year)
DW = File type "Drawing"
0001 = Drawing Number
R0A = Document Revision Number (can be numerical or alphabetical)
Detail, Widget Assembly = Document Name

My macro currently parses the file name such that a file name of "09-058-DW-0001-R0A - Detail, Widget Assembly", would get parsed out into the following colums:
Column B = 09-058-DW-0001
Column C = 0A
Column D = Detail, Widget Assembly

Questions 1:
The file revision portion of the file name will vary from "R0A", "R01", or even "R10". If the second character in my revision name, is a "0", I want to strip that out so I think in need an "If" statement that tests to see if the second character is "0" and if so, delete it, but not sure how to go about that. Suggestions would be appreciated.

Question 2:
How would I change my "BrowseForFolder" function so that if the file is opened on the users "C:" drive, it goes to our network, and if it's not on the network, opens up in one directory higher than where it's stored?

Question 3:
I'm getting a "Run-time error: '76':, Path not found", if I "Cancel" out of the "BrowsForFolder" function. I'm missing some error trapping but not sure how to sort that out.

Regards

Hi,

I have an Excel macro that is supposed to go through all the files in a
directory and change the extensions from DOC to DAT. It was working at
first, but now it seems that I keep getting the File Not Found error (Runtime
Error 53) when I try to run the macro, even though the file to be renamed is
explicitly there in the folder.

What am I missing here?

Thanks in advance!

Public Sub ChangeFileExtensions()
Dim strDir As String

strDir = ThisWorkbook.Path

Call ProcessFiles(Dir(strDir & "*.Doc"))

Do ' LOOP THRU ALL D06 IN JUNKFOLDER
Loop Until Not ProcessFiles(Dir)

End Sub

Function ProcessFiles(SourceFile)
On Error GoTo NoFiles
oldName = SourceFile
newName = Left(SourceFile, (Len(SourceFile) - 3)) & "DAT"

Name oldName As newName

Exit Function
NoFiles:
ProcessFiles = False

End Function

Hello Programmers!!!

I have a code (posted Below) that finds a file by Path & Name, and retrieves a number from a specific cell, then places it in the open workbook named results.

How do add that if the Path or filename isn't found then do nothing (or exit the sub). Or maybe for an indication place the Letters "NF" (for Not Found) in the cell where the number is suppose to go. I don't want any error messages or boxes to pop up; only quit the macro if the file/path name(s) are incorrect.

Code:

Public Sub GetValue()

Application.ScreenUpdating = False
Dim sFileName As String
Dim sPath As String

sPath = Range("AE4").Value
sFileName = Range("AE6").Value

If Not Right(sFileName, 4) <> ".xls" Then
sFileName = sFileName & ".xls"
End If

Workbooks.Open Filename:=sPath & sFileName

Sheets("Auto Place Sheet").Activate
Range("B371").Copy
Windows("Results.xls").Activate
Range("M29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Results.xls").ActivatePrevious
Application.CutCopyMode = False
ActiveWorkbook.Close
Application.ScreenUpdating = True

End Sub

Thanks,
EMoe

Hi,

i have a small problem (i guess the answer is kind of easy)... Using win98, Excel 2000 and VBA 6.0

Here is:

I'm doing a check on two files (they don't exist on the specifed locations)...

so for the first line, I'm getting an error "file not found"... and is handled by the error trap...
but when it goes on the second line... it bugs... and i'm getting an error message "error 53, file not found" which is not what i'm looking for ... of course...

should be handled by the error_trap too since it's the same error, isn't it ???

what's wrong ?

cheers,

good4u...

here is the code i'm using:

	VB:
	
 test2() 
    [b]On Error Goto Err_Trap:[/b] 
    If (GetAttr("c:xxxxxxxx.doc") And vbDirectory) = vbDirectory Then 
        Cells(1, 1).Select 
    End If 
    [b]here:[/b] 
    If (GetAttr("c:zzzzzzzzzzzzzzz.bmp") And vbDirectory) = vbDirectory Then 
        Cells(1, 2).Select 
    End If 
     
    [b]Err_Trap:[/b] 
    If Err.Number = "53" Then 
        Goto here 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



No luck finding an answer? You could always try Google.