Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Userform Show Path/file Error 75


I have an excel 2003 template containing a userform that is displayed using the workbook_open event.

The userform causes excel to crash with file/path error 75. i have tried many suggestions given on ozgrid and several other forums with no joy.

When the userform crashes, the debugger stops on DataFormNew.show.

I have added stop commands in the initialise procedure to see what is going on and it appears that my userform has disappeared!

to upload the excel template and any other information required to get this sorted.
its size is 765 KB which exceeds the size limit to attach to the post.

here is the workbook_open macro:


Private Sub Workbook_Open()
'On Error GoTo MyError
'Call UnProtectAll

'Dim frm As UserForm
'For Each frm In UserForms
' Unload frm
'Next


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Crashes On UserForm Show By Next Button (Path/File Error)
When loading my workbook, my userform loads perfect, but when I go to hit the "next" button. It crashes with the error:

"Path/File access error."

The next button code, quite simple: ....

View Replies!   View Related
UserForm: File/Path Access Error & I/O Error
the spreadsheet needs to be copied to a directory called "C:downloads" as it contains a ODBC query to itself (In reality, this is a query to an External Oracle Database)

On loading, it should pop up a simple userform, with a combo and two command buttons, which when pressed takes you to a (hidden) tab that displays a pivottable.

All works well until I try to close /save when 60% of the time, Excel encounters problems and closes and will not load up the file the next time until either quit excel or disable macros. Messages include "file/path access error", "I/O Error" or get restarts excel.

On a casual run through, I expect you might report back that "All worked ok for me". Please can you give it a bit of a thrashing, comment out the userform show, save the file (frequently) becuase i assure you it will break ultimately!

This is a brand-new file and I've tried it on about 5 different PC running different versions of Excel and generally get the same result.

View Replies!   View Related
Path/File Error Showing UserForm
The attached spreadsheet is as simple as it gets - a single userform with a combo box that allows 1 of 4 choices. It gets loaded and displayed in the workbook_open() event - or at least it should - but 9/10 times i get the message "Path/File error" and debug crashes out on the userform.show line. The next time I try and open the file (despite not saving it) Excel crashes and closes! Even re-starting Excel does not allow the file to be opened!

If, however, I open the file with macros disabled, and go into the VBE and manually run the workbook_open() all works fine. I'm using Excel 2003 and I've tried it on other installations - sometimes it will work, other times not.

I've tried every combination of userform load, unload, hide & show with similar results.


View Replies!   View Related
File/Path Error
We have built quite a large Excel based program that contains quite a lot of coding. the program seems to work fine the majority of the time but every so often we recieve an error message that causes excel to not save, or to crash and shut down.

The message that comes up is "File/Path Error"

Now I've just done some hunting around online and some links I have found speak of declairing the document location correctly in the coding. I was wondering could this be the issue at hand?

Anytime the document needs to save, there is a cell with the directory path and file path declared as a variable (Actually as Dim String) within the coding. Is this the best way to declare a file path?

Also, the problem seems intermittent. We Compile the VBA coding before saving, we also use a Clean Project add-in within Excel. These processes usually help but every so often the problem comes back.

View Replies!   View Related
Trim Full File Name & Path To File Path Only
I have a variable ("DestFile") that defines a path to a file (used in saving the file)...

I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...

How would I go about trimming the "DestFile" address to get a "DestFldr" address?...

An example of "DestFile" might be;
S:BryanFor KenGulf ConstructionST0609014-t.xls
(the file name length may vary)
What code can I use to consistently trim it back to;
S:BryanFor KenGulf Construction
as the "DestFldr" variable?

View Replies!   View Related
UserForm Show Causes Error On 1 PC
I've created a userform on one computer which launchs when I open the excel workbook. The userform contains comboboxs and a picture. But when i saved it on another computer and tried to open it and it errors. (Run-Time error 9). (Still works fine on the computer that created it). All references are to this workbook only. Userform is contained within the Forms folder of this workbook(not personal.xls) and the userform name is PDM_SPLASH1. Also, if i go into VBA I can see the form, but it wont launch when i try to play in manually. (Run-Time error 9)

Private Sub Workbook_Open()
Worksheets("Launch Page").Select
Worksheets("Instructions").Visible = False
Worksheets("SelectDB2").Visible = False
Worksheets("REPALIGNMENT").Visible = False
Worksheets("PDM").Visible = False
Worksheets("TABLES").Visible = False
Worksheets("DISPENSING").Visible = False
Worksheets("DISPENSED").Visible = False
Worksheets("OpportunityAnalysis").Visible = False
Worksheets("ENGINE1").Visible = False
Worksheets("POTENTIALCONTRACTLOSS").Visible = False
PDM_SPLASH1.Show
End Sub

View Replies!   View Related
UserForm As Add-In (Getting Error 9 On .show)
I have a userform that is working perfectly, but when I try to make it an add-in I can't even get it to load. When loading the userform as an add-in I get the error "Run error nr. '9'.: The Index is out of the interval" (freely translated from Swedish to English). The code I'm using to load the userform is

View Replies!   View Related
Show Userform - Invalid Property Value Error
I created a form that my employees could use to log their work. When I toggle between the combobox and the qtytextbox I get an "invalid property value" error that debugs to the line of code that opens the userform. I have no idea how to fix this. The spreadsheet has a button that shows the userform. This code is:

Private Sub rectangle1_click()
If frmLCWork.Visible = False Then frmLCWork.Show
Exit Sub
End Sub

The userform has a combobox for employees to select the completed "task", a quantity textbox and then an "add to log" button. The form code is:

Option Explicit
Private Sub cmdAdd_Click()
Dim sStep As String
Dim Row As Long
'check for a task
If Trim(Me.cbxTask.Value) = "" Then
Me.cbxTask.SetFocus
MsgBox "Please specify which task you completed."
Exit Sub
End If....................

View Replies!   View Related
Show UserForm Upon Opening File/Workbook
I have a range named "start" and I have a form named "fMenu". I have used this same code in other spreadsheets to have the userform automatically become visible when the spreadsheet is opened and it works in other workbooks. Apparently I have been struck with the stupid stick this morning. I have a hotkey of "M" and when I use control M the form shows up and the macros invoked by each button on the form are working properly.

Sub open_menu()
Application.Goto "start"
fMain.Show
End Sub

View Replies!   View Related
Show Flash .swf File On Userform
Private Sub CommandButton1_Click()
Call ShockwaveFlash1.SetVariable("testVar", "Message B")
'This sets the _ value of the variable in the Flash Project.
End Sub

Private Sub ShockwaveFlash1_FSCommand(ByVal command As String, ByVal args As String)
Text1.Text = args
'this catches the action sent by Flash, and uses it in the TextBox.
End Sub

Private Sub UserForm_Activate()
Call ShockwaveFlash1.LoadMovie(0, CurDir + "/vbSample.swf")
'This loads the movie that was made into level 0 of the project.
End Sub

(I got from this link http://www.kirupa.com/developer/mx/flashvb.htm) As you can see I have adapted it to work in Excel 2007. Now - when i first run it - the flash file loads and shows in the userform. The only nag being that the .swf file doesnt pass the text into the textbox on the userform. But once i exit the Excel workbook and reload it - the .swf file doesnt show up at all no matter what i do.

View Replies!   View Related
Import CSV File Using Cell As File Name & Active Workbook Path
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.

Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With

With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................

View Replies!   View Related
Save File As Text Using Cells For Path & File Name
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names

pad$ = Application.DefaultFilePath
'controle = Dir(pad$ + "FactuurNummer7.txt")
'If controle = "" Then GoTo EerstAanmaken
'Open pad$ + "Factuurnummer7.txt" For Input As #10
'Input #10, Nummer1
'Close #10......................

View Replies!   View Related
User Input For File & Path For File Copy
Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?

Sub Macro1()
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located
newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls"
Set fs = Nothing
End Sub

View Replies!   View Related
Show Workbook Location & Path
Is there a way to display the location of an active workbook on my local drive and save it into a varible. I'm new to Excel and VBA but I'm trying to create a template to use for multiple vehicles. When the macro is run...

1) the workbook will have to identify what Workbook and worksheet it is in and save it as a varible so that it can access it later. Each workbook is specific to a vehicle.
*(The reason is because I will need to refer to the address of the original workbook when pasting the copied data from the "OilChart" Workbook.)

2) Copy the vehicle identification # and save as a varible. (Dim vehicleNumber As Variant)

3) Open another workbook on the local drive (OilChart.xls)

4) Run the macro in the new workbook (OilChart.xls) to update the data

5) Search Column B in (OilChart.xls) for matches of the vehicleNumber, and for all rows that match, select and copy all rows and paste it back into the orig workbook (Vehicle Specific Workbook).

This is the code I have so far.

Private Sub btnFind_Click()

Dim vehicleNumber As Variant
Dim sourceLocation As String
Dim sourceFileName As String
Dim fileName As String
Application. ScreenUpdating = False

I have'nt figured out how to do step 5 yet so if anybody has any ideas I'd be open to them. I tried looking at Aaron Bloods Kickbutt VBA Find Function but I got a little lost on how his code would relate to my code. It also looks like it doesn't copy or paste anything to another workbook.

View Replies!   View Related
Split File Name From File Path
Each month I am sent a spreadsheet which contains a list of 100's of different file paths (including file name).

For example (Quater 1 is the document name)
R:ManagementJohn2008SalesSectionAQuater1

I would like to remove the file name from the file path (I will then convert the file paths to hyperlinks which will open the folder the document is stored in but not the document itself.

For example
R:ManagementJohn2008SalesSectionA


View Replies!   View Related
Separate File Name From File Path
I am trying to separate a filename from a filepath in Excel VBA 2003.

I am using the Application. GetOpenFilename command to get the user to select the correct file.
However, this also seems to record the filepath.

I am trying to separate the filename out of this so that each file( name) activates a different sub procedure.
I am storing the result of the getopenfilename as a Variant.

At the moment I am having to specify the full path in order to get each sub to run. This is fine for 1 user - but not for others as the files are stored in different locations for each user.

View Replies!   View Related
Use The Path Of My File
how can I refere to the path whaere my addin is located without having to write it
i tried with this, but....................

Sub AddingAddIn()
Dim p
p = Application.Path
AddIns.Add Filename:=("path")
AddIns("HTML").Installed = True
End Sub

View Replies!   View Related
Fill UserForm ListBox & Show Userform
having trouble with the details of actually making these features work for me. I figured out how to create a UserForm with a ListBox and 2 buttons, but I don't know how to proceed from here.

1. Populate the ListBox in the UserForm with a list of names from the sheet "Totals_Dropdowns", cells K2:K11

2. Make the UserForm pop up and enter the user's selection into cell C40 of the "Regenerate Request"

I know these are very basic operations, and I'm pretty sure I can figure out the rest of my problems once I can get past the above.

View Replies!   View Related
Run Userform: Show A Userform In The Spreadsheet Environment
how do i show a userform in Excel in the spreadsheet environment? I used to "run macro" but unable to run userform code "private sub".

View Replies!   View Related
Get A File Path Using Vba
I'm using this code to import a file, but I want to be able to save just the path (with no file name) to a variable to use for the output file. This code saves the path and file.

FileName = Application. GetOpenFilename _
FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)

Any help would be greatly appreciated. I didn't know but what there was a function that I could apply to the 'FileName' variable that would remove the file name...don't know.

View Replies!   View Related
Return File Path
I have a UserForm with a button that, when clicked, opens up a File Directory Browser that the user can select the file of their choice.

The code is below :

Private Sub DirectoryBrowser_Click()
Dim fn
fn = Application.GetOpenFilename 'can add parameters. See help for details.
If fn = False Then
MsgBox "Nothing Chosen"
Else
MsgBox "You chose " & fn
'now that you have the name, you can open it or do something else
End If
Workbooks.Open fn
End Sub

What I would like is the filepath (of the file they choose) to appear as the displayed caption on another button or label.


View Replies!   View Related
Extract File Name From Path
I am having trouble with a macro of mine. Essentially it opens a file, based on user input (Full path is stored in variable FilePath), but then I want to select that workbook, and therefore need just the .xls filename. I figured I would create a new variable, FileName, to pull out the filename.

How can I edit the string in my macro to extract just the filename.xls from the full path?

View Replies!   View Related
Get Rid Of Path In File Name
How can I get the name of a workbook WITHOUT the path when I have the name WITH the path?

To be more specific. When I use "Application. GetOpenFilename" the result is a file name for say "my_book" including the path. With this I can open the file using "Workbooks.Open" and Excel will show me "my_book". But when I want to reference this workbook later on in say "Workbooks(my_book)", then I need the file name without the path. How can I get that one in VBA?

Till now I was using "CurDir" to find out the path after opening the file and just subtracted it from the full file name. This seamed to be fine. But now I had a situation where CurDir gave me a path with abbreviations (~),

View Replies!   View Related
Launching Windows Explorer Path Error
I am trying to launch windows explorer from within excel vba. The routine below is borrowed from another post on this site and modified for my purposes.

Sub LaunchWindowsExplorer()

Dim PID As Double
Dim strRootPath As String

Const strExpExe = "explorer.exe"
Const strArg = " " '" /e,/root, "

'// Change rootpath here
strRootPath = "J:MyDirectoryWilliams,Bob 7264" ..............


View Replies!   View Related
Check If Certain File Exist On Path
I am using below mentioned Code for checking if certain file exist on Path.
Its working on my home PC (XP SP2 Excel 2003).
BUT its not working on my office PC (XP SP2 Excel 2003).

Can somebody tell me why its not working in my office PC ( or if its missing something, then from where can i download it.)

Sub CheckFiles()
Const strFolder = "C:Documents and SettingskreshnrDesktopTest"
Dim fso, msg, i
Dim rngData As Range

Set fso = CreateObject("Scripting.FileSystemObject")
Set rngData = Sheets("Sheet1").Range("A1")

With rngData
Do While .Offset(i, 0).Value ""
If (fso.FileExists(strFolder & .Offset(i, 0).Value & ". ")) Then
.Offset(i, 2).Value = "Yes"
Else
.Offset(i, 2).Value = "No"
End If
i = i + 1
Loop
End With
End Sub


View Replies!   View Related
Correct Declaration For A File Path
I am using the format of using variables for declaring file paths and document names in VBA however I want to make sure I am doing it properly...

Dim Server, VersionName As String
Server = Worksheets("Calculation Matrix").Range("CalculationMatrix_Server").Value
VersionName = Worksheets("Calculation Matrix").Range("CalculationMatrix_VersionName").Value

ActiveWorkbook.SaveAs Filename:="" & Server & "uploadcomplete" & VersionName & "", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Should these variables be declared as String?


View Replies!   View Related
Macro That Will Open The File Path
hi, i need a macro that will open the below file path, and when i select the file i would like the macro to copy the active sheet from the source file being opened from range A2:L10000 and then paste special values to destination CELL A16 of the activesheet of the workbook.

I would like the date format for the below to be generic so it will apply for all months and years.

S:RECSBANKRECNominees200812-2008

View Replies!   View Related
SaveAs VBscript File Path
I'm using a VBscript that will copy a worksheet and it works perfectly except that it saves the copy to the same directory as the original workbook. I need it to save to a different directory.

What I believe is the pertinent part of the code that needs adjusting is:

"Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")

' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False "

How would this be changed to specify that "NewName.xls" be saved to: D:/newdirectory/copies

View Replies!   View Related
Get File Path And Store A Variable
If I have a file open called test file, How I can find the file path of the file that is open and how can I store that's as a variable?

What I am doing is trying to open a file that excel closes in a save as process!

View Replies!   View Related
Creating File Path Automatically
I am looking to have these workbooks save automatically to a folder on the C: drive.
If the folder I specify is not available on the C: drive, how do I write teh save comand to create it automatically with a prompt

View Replies!   View Related
Get File Path From Windows Registry
How to search and get the path of a folder present in windows registry using VBA excel.

View Replies!   View Related
Copy File To Specified Folder W/out Path
Is there a way for excel vba to find a folder named "MyFolder" in Drive C: and copy/paste a file into that folder without knowing the entire path?

Example:

Workbook("Myfile.xlsm").copy

"C:" Pathfile "MyFolder" . paste

View Replies!   View Related
Concatenate Columns Into File Path
I am creating an excel file for a mail merge following this process. I need to take the three columns below and enter the path to the corresponding chart. I have all the charts created in a directory. The naming convention for all of the files is XX-XX-XX.gif. The X's represent the numbers below without the decimal places. Therefore the example below would be 44-43-27.gif. Is there anyway to create a formula to drag down instead of entering the filename's manually? In the end, I want to have the full file path as shown in the XLS.

q_3_abc q_3_teamq_3_yscore
4.4 4.3 2.7

I have 700+ rows to do this for, therefore I want a formula or VBA solution instead of the manual donkey work.

View Replies!   View Related
Code Dynamic File Path
I have a question. How to code the filepath name to make sure it change when i move my files from one directory to another. Example:

I need to update 4 files from this directory, but i am moving them to another directory.
FROM
c:documents and settingTesting
TO
The file being able to follow which ever folder.

View Replies!   View Related
Macro For Relative File Path
I have a macro in excel that saves to an external text file, but the excel workbook (and the text file) will be copied to different folders so i need it to alway read the file from the location the worksheet was opened from. I hope that makes sense

Ive tried using ActiveWorkBook.path but that doesnt work, maybe im just using it the wrong way

View Replies!   View Related
Get Relative File Path Of Workbook
I work on multiple workbooks. They all calculate a lot of functions and I am forced to create multiple workbooks to do these calculations. Linking to other workbooks basically displays the results of workbook1 and works from there.

But...

I need to keep an overview of these files, so I store them in different folders all within the same 'master' folder. So let's say: master folder is FolderMaster, and this contains folderA, folderB, folderC. FolderA contains workbookA1, workbookA2; folderB contains workbookB1, workbookC2 and finally folderC contains workbookC1 and workbookC2.

Suppose that this hierarchy might change. Moving eg workbook A2 to folderB. This would cause a #REF if it is linked in another workbook.

So I thought: organize your hierarchy in a 'master workbook' and store all paths to the individual files in a worksheet. If I link to a specific folder I do not directly link to the actual folder, but would use INDIRECT and grab the filepath from the masterworkbook. If the path to a workbook change I do not have to update all individual links, but only change the path in the masterworkbook. Right?

But 'how' is the big one here. How do you formulate your path to a file? It can not be the entire file path since that's variable... (sometimes 'hd:User1:....'; sometimes 'HD:User2'..."). So I think I need something as mentioned in this office help thing. But I have no clue. I do not want to use VBA, I hoped for another solution to this.

View Replies!   View Related
File Path Changes On Linked Formula
We have a set of workbooks with a linked Vlookup formula. When we email the file to staff that do not have access to the linked file, the linked formula seems to change the directory. see below

Original Formula
=VLOOKUP($A$30,'G:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)

Formula after user opens email with the drive changed automatically to C
=VLOOKUP($A$30,'C:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)

Note that the user does not have access to the G drive and they are not updating the links when they open the file.

View Replies!   View Related
Two Cells Which Have Formulas Stopping Error Messages Show An Error When Added
See the attached sheet. I am trying to add together two figs which are linked to calculations which have formula built in to stop error messages when there is a 0 / 0 = #value type error. However when these two cells are added, if the cells are blank I get an error message. And if only one cell has a value, I get "" with my existing formula. what I need to do to get a result of 7 if for example cell A4 = "" + B4 =7. At the moment my formula shows "" in the sum total of these cells

View Replies!   View Related
MoveFolder. Run-time Error 76, Path Not Found
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:


Sub MoveFolder(Sourcepath As String, Destinationpath As String)
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

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.

View Replies!   View Related
Unload Active Userform And Show New Userform
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Application.ScreenUpdating = True
Unload Me
RestrictedOptions.Show
Else
Cancel = True
End If
End Sub

Trying to use above code to unload active userform and show new userform when red X (close) selected by operator. With the code as is, the Unload Me leaves the form on the screen and displays the RestrictedOptions form. If i remove the Restrictedoptions.Show, the Unload Me does remove the original form.

View Replies!   View Related
Path Of File Picked Up From Cells In Spreadsheet
Is there a way to make the contents of a particular cell in a worksheet show where to pick up another file. Here is my code to pick up a file:

View Replies!   View Related
Check File Path Of Before Running Macro
I have a spreadsheet with a macro that saves a backup s/s to a particular folder, but the problem I have need the macro to check the filepath is that of the original s/s before running.

Its so that when people take copies of the main s/s and save them to their desktop I dont want the copies being saved in the backup folder

macro is below

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If ActiveWorkbook.ReadOnly Then Exit Sub

If MsgBox("Do You Want To Save A Backup?", vbYesNo) = vbNo Then Exit Sub

Dim fso As Object
Dim objFiles As Object
Dim myWkBk As String
Dim myFName As String
Dim BkUpDir As String
Dim CountFiles As Integer
Dim mydate As Double

BkUpDir = "k:singstatappealsappeals trackerBackups"

Set fso = CreateObject("Scripting.FileSystemObject")
Set objFiles = fso.GetFolder(BkUpDir).Files

On Error Resume Next
'the actual counting of files takes place here.
If Err.Number 0 Then
CountFiles = 0
Else
CountFiles = objFiles.Count
End If


If CountFiles > 4 Then
MsgBox "You have " & CountFiles & " saved workbooks. Perhaps you should delete some?"
End If

mydate = Format(Now(), "ddmmyy")
myWkBk = ActiveWorkbook.Name
myFName = ActiveWorkbook.FullName

fso.CopyFile myFName, BkUpDir & mydate & myWkBk

End Sub

View Replies!   View Related
VBA Generic Code For The File Path
how can i write the year and month in generic code for the following file path

.SaveAs Filename:= _

"S:RecsReportingRec StatsSummary Stats2009JanStat Summary_" & Format(Date, "dd-mm-yy") & ".xls"

View Replies!   View Related
Determine File Name & Path On Opening
Sub Workbook_Open()
Dim myPlace
Const mFileLoc As String = "c:documents and settingsdcrakerdesktop est location.xls"
Set myPlace = Sheet1. Range("F1") 'currently has formula ->=CELL("filename",A1)<-
If myPlace = mFileLoc = False Then
MsgBox ("Warning, File is in incorrect location. Please contact David for corrections"), vbCritical
'add codes to disable macros from running (still need to find this)
Else
Exit Sub
End If
End Sub

>What happens, is it always tells me that the file is in the wrong location. I want it to be able to see what is hardcoded in VB and compare it to active workbook paths, and if it sees a change then it knows that it is in a different location.

> used the macro recorder also to make sure that the filepath was correct in the code also.
It happened, and could happen again someday. The company upgraded the server, and all the files were moved around. When the User accessed the file that I had created awhile ago, he couldn't figure out what happened. For some reason, my computer did okay on finding the correct file.

What it's suppose to do:
It will popup a message, informing the User that the file is no longer where it should be and may not work properly.

And then I have to add/find codes that will not allow the macros to run, so that I can come in and fix the problem.
It took me about 2 1/2 hours to figure out what happened and since I might not remember next time this could happen.

View Replies!   View Related
Hyperlink Does Not Maintain Path To Target File
I read about Hyperlinks in the August 2006 "OZGRID" Newsletters but found no answer.

Assume 2 workbooks - book1 and book2, both located in "c:" (root of drive C)

Now, In book1 I have declared an HL to book2 using CTRL+K and upon clicking in that cell the book2 is opened.

After MOVING(!) book1 to a new location (D:DATA) and clicking the HL - I get an Error "cannot open the specified file".

While book1 was still in root C: - I tried to force to c:ook2.xls by typing it in the HL address window but after saving, Excel deletes the "c:" and leaves "book2.xls" only I think because both files were located I the same directory.

I am aware that my "problem" can be solved by using the function HYPERLINK but I would like to learn (if possible) how to insert an HL using CTRL+K that will maintain the path although moving the file which contains the HL).

View Replies!   View Related
Including The FULL File Path On All Printouts
whats the simplest method of including the FULL file path on all printouts from excel.

View Replies!   View Related
Find File Path To USB Drive
I have a file which is kept on the C Drive. Each day, a different excel file is downloaded to a usb memory stick. The first file reads data from this file. The problem is, the drive letter keeps changing from E to F and the macro then crashes out.

The file on the memory stick always has the same name, so is there a macro to find the drive letter of this file and then use it in my existing macro?

View Replies!   View Related
Check File Open With Random Path
I have an application that requires users to download a file from a location that is then imported at their execution into the application. I've done my best to counsel users to download the file to their desktop (for ease of locating) and to stick with a standard filename so that there are no errors. Some of these people just don't take direction well, will save the file wherever, or save the name inappropratly, or worse, open up multiple copies of the same file corrupting the name: ie: FileName.xls, FileName.xls(1), FileName1.

View Replies!   View Related
Coding File Path To Insert Picture
Explored a few posts on my topic, but didn't quite find what I needed.

I need to open the Insert Picture dialog with it pointed at C:My DocumentsTemp. (Users will be required to save their images files to this location only).

Attempted to run a macro to get the code, but the dialog box forces you to select a file. I only need to get some low skilled users to the folder and let them select the image to insert.

View Replies!   View Related
Find Customised Toolbars File Path
In Windows 98 for example when you created a Customised Toolbar and attached macros to it a file was created with the user name followed by 8.

i.e. timbo8

You could copy this file and keep it was a backup in case you lost the toolbar/s. Which invariably happened whn someone else logged onto your pc. When the company transferred over to Windows 2000 this file was called something else I thing it ended in a .xla extension but I don't know what it was called or where it it located.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved