VB Code To Change DIR Drive
Oct 24, 2007
I have part of the code below and need to modify it so that others who only have C: instead of D: to use it
Filename = "D:TempBudgetMonthly.xls"
FileExists = (Dir(Filename) "")
If FileExists = False Then
msg = "The data file Monthly.xls does not exist !! Load the required file and run the report again !"
If MsgBox(msg, vbExclamation + vbOKCancel) = vbOK Then
End If
Exit Sub
End If
I need code to replace all D: to C: for the entire PROJECT not MODULE.
View 9 Replies
ADVERTISEMENT
Jan 31, 2008
I have a user whose links in his spreadsheets are in UNC format. He changes them to drive letter mappings, but when he opens the workbook again, the UNC format returns. how to change the links so that they remain as F: etc.. rather than \servernamesharename The user has MS Excel 2000 and Windows Xp Professional
View 2 Replies
View Related
Oct 23, 2013
I have written code that allows the user to open a file. To make things easier I changed the file path to point directly to the folder where all the needed files will be placed. The problem is everyone that will be using this work book map their drives differently. One user may have it as H, and the other as J. Also users can have the same drive mapped but at two different starting points. Ex: H:DataReportsxxxyyy or H:Reportsxxxxyyy this causes the code to crash. Is there a way do default the path so it will not matter how the drives are mapped?
Here is what I am using so far:
Dim filt As Variant ' flit to be used in the getopenfile. variant is need for its multiple
Dim FilterIndex As Integer
Dim Title As String
[Code]....
View 2 Replies
View Related
Apr 14, 2013
I need code to print a file located in my C drive.
View 6 Replies
View Related
Apr 3, 2014
I have problem to change text code into numerical code using macro. i have data contain text code and i would like to convert it into numeric . each text code has dedicated numerical code for example I have 4 fluids with text code text code:
FW = Fresh water
SW = Saline eater
CW = connate water
MW = Meteoric water
numeric code as follow
FW=1
SW=2
CW=3
MW=4
I would like to convert the text code into numeric code, it is easy if using excell , but it is routine job for me , i need to create macro to be more simple .
View 1 Replies
View Related
Mar 27, 2012
I have recorded the code below that does what I need, problem is I will use the code on files with various amounts of rows. What can I change in it so it will work on all different size files.
Code:
Sub Macro3()
'
' Macro3 Macro
[Code]....
View 6 Replies
View Related
Feb 21, 2007
I am busy writing projects and the macros that I write reference external files (no surprise there). Normally, all referenced files are on the company's main G: drive but recently the company have started the process of re-appraising all access authorities for the folders in the G: drive. This has necessitated that they create a new drive (called J:) and folders and users are being migrated over there as they have been cleaned and approved.
All of this means that some of my users reference J: and some G: and my macros are intermittently falling over because of it. For each user, all the files that they will need will be on either the J: or the G: drive, but not both.
All of the above leads me to ask the following question... How can I build into the macros a "Check what drive you're on and use that letter" line of code
View 4 Replies
View Related
Nov 18, 2009
Currently I am using this ......
View 9 Replies
View Related
Nov 20, 2009
I use the Hyperlink to open PDF files that are on the CD drive from my spreadsheet. I am on Windows XP and the CD drive is the D drive. In Vista the CD drive is the E drive.
When I take the spreadsheet to a Vista machine the link fails as there is no D drive.
Is there any to code the path of the Hyperlink so it will go to th default CD drive for that computer.
View 9 Replies
View Related
Jan 6, 2009
We have a form that is completed by our employees and emailed to a central group email address that up to 8 employees within our team can access. When one of our 8 team members open the file sent they need to add comments to it and then save it in a central directory on one of our network drives.
Now the problem I have is that we all have the network drive mapped to a different letter.
Is there a way that I can have a macro (see below for current macro that does not work) save to a network path and not a drive letter.
View 10 Replies
View Related
May 18, 2012
I have a an excel workbook that is distributed on a flash drive. Currently a Save As Macro copies "A1" as the file name and then opens a screen to allows the user to save that filename to a location that the user selects. What I would like to have happen is for a command button to start a macro that would automatically save the file back to the flash drive that the master workbook is located.
View 3 Replies
View Related
Jun 30, 2013
I have a program that relies on a remote drive being open and ready. My current tactic (which works perfectly well) is to have a file in the destination drive and ask the date that file was created. If an error occurs I know the destination drive is not ready.
Code:
On Error Resume Next
temp = FileDateTime(destDrive & "xDeletedStuff" & "testFile.txt")
If Err Then
MsgBox "Destination Drive / Folder Not Ready"
Exit Sub
End If
On Error GoTo 0
Idealy I would rather not use the "testFile" I would prefer to just test to see if the folder existed, "destDrive & "xDeletedStuff"
View 1 Replies
View Related
Apr 9, 2009
I am trying to get a excel to list all the file names in a drive along with date last modified, author and file size. I have got the following bit of code to pull back the file names, but i am struggling to find anything that will pull back the other data, I think its something to do with the DIR function, but im not sure........
Range("B1").Select
D = ActiveCell
Cells(2, 1) = "Filenames"
r = 3
f = Dir(D, 7)
Do While f ""
Cells(r, 1) = f
r = r + 1
f = Dir
Loop
End Sub
where "B1" is the file path of the drive
View 9 Replies
View Related
Jan 10, 2007
I want to create and add-in that will allow a user to select a file (using the in built dialog boxes), the file will then be copied across the network to another server to which the user doesn't have access - I do have access so would hard code in the appropriate username and password in to the script.
The way I had done this before was to look through the users mapped drive to find a free one, map the network drive connecting as myself, save the file and then disconnect the drive.
I am able to map and disconnect the drive, but can't work out how to find an empty drive to use (thus preventing me from overwriting any mappings they may already have).
View 3 Replies
View Related
Mar 22, 2007
I was just viewing the VBA to "get a hard drive serial" and would like to know how I can make the serial appear in a cell?
Sub SerialNumber()
Dim oFSO As Object
Dim drive As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set drive = oFSO.GetDrive("C:")
MsgBox drive.SerialNumber
Set oFSO = Nothing
Set drive = Nothing
'release memory
End Sub
View 3 Replies
View Related
Feb 22, 2008
I am trying to get the user to choose which disc the file structure should be stored in. Is it posible to get a dialog box which contains all the disc drives? I tried using the GetOpenFileDialog, but it seems I must choose a file. I must only choose Disc Drive.
View 5 Replies
View Related
Dec 3, 2008
I need to know how to read a filename from my local drive c: ? for example in my local drive i have some 100 files, i need to pick there filename, date and time stamp .. etc.
View 7 Replies
View Related
Apr 2, 2009
I need to code an option to overcome the fact that 1 workstation sees all drive letters differently.
I have many macro functions that may refer to "P:My documentsTrucks 2008filename"
However this workstation sees that drive as "T:Trucks 2008filename" and the macro will either not function or cannot find the location to save a copy of action in question.
Can I code an option that will "If filepath not found" ie "P:" go to "T:" etc
View 14 Replies
View Related
Jun 12, 2009
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 10 Replies
View Related
Jan 26, 2010
I currently have the following ...
View 8 Replies
View Related
Jul 10, 2012
I have an Add-In installed on multiple computers. When I update it I update them all at the same time so that everyone is on the same page. The Add-In is always in the same location for all computers.
C:- Global FilesVF MacroVF Macro Add-In.xlam
I use the Add-In to format a sheet and one of the formats is to display the "last author" & "Last Save Time" This is the code used:
Code:
Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
[Code]....
The same situation occurs with cell Z8 as well. It appears it is looking in the wrong location. If I change the E to a C in the formula bar it fixes the issue but I cannot rely on my users to do this every time. We keep the WB's in question on an external drive and pass it around as needed. So they will run into this problem on a regular basis.
I also get a "Security Warning - Automatic update of Links has been disabled" message only when run from the USB drive.
View 2 Replies
View Related
Aug 11, 2007
I'm trying to make it so that a backup copy of my file is written to floppy when the file is saved (but not with SaveAs). So far, I have the following code, which if the floppy is in the drive works fine, and doesn't error out if the disk is not in the drive. But it doesn't make sure that a backup copy actually got made. How would I check to see if the disk is in the drive and loop somehow until it was so that the file got saved to floppy?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Resume Next
Application.DisplayAlerts = False
If Not (SaveAsUI) Then
MsgBox "Please Insert Floppy Disk in Drive A:"
ThisWorkbook.SaveCopyAs "A:" & ThisWorkbook.Name
End If
Application.DisplayAlerts = True
End Sub
View 9 Replies
View Related
Sep 2, 2008
I want to write a macro that would go to a certain drive and search for a file?
View 9 Replies
View Related
Dec 1, 2009
If you don't know what the drive letter will be, because on other people's computer it could be different than what you have.
I only have the folder address
\ROGERSDAILYREPORTSXLSDM
View 9 Replies
View Related
Dec 12, 2006
I have a problem that I have several workbooks that are getting more widely used by a number of users and I'm starting to encounter problems. The workbooks open files from particlaur locations to retrieve information but I am finding that not everyone has the same drive letter for the same server or shared drive.
For example Server607 could be drive J: for me but drive N: for people in another department. Is there a way to refer to the server location in VBA when opening files rather than the drive path?
View 2 Replies
View Related
Jun 13, 2008
I made an excel sheet that links to other sheets on a different drive. It works while I am making it, but upon save and reopen all the hyperlinks produce this error: The address of this site is not valid. Check the address and try again.
Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive.
View 2 Replies
View Related
Jun 16, 2007
Is there any way, in which i can make my code dynamic?? For example, in a code written in VBA, there is a reference to particular sheet, say "ABC". If i change the name from "ABC" to "ABCD" from excel, the underlying code should change automatically from "ABC" to "ABCD".
I want to make my code dynamic..
View 10 Replies
View Related
May 25, 2011
Trying to create a VBA code that will enter a formula such as: SUM(Q2:Qt) in a range of cells for instance A2:A10, and if there is information in B11, B12, B13 etc., it will consider that variable change and enter the SUM formula in cells A11, A12, A13.
I read that Dim LR As Long is the solution but not sure how to apply.
View 9 Replies
View Related
Jun 5, 2013
This code is coulnm specific, can this code be adapted to look through a range for a match i write this code up to 25 time changing the column it looks at each time, the row always stays the same the location it paste to also always stays the same if it could be made to look through a range in reducing the amount of code to write.
the range it needs to look through would be, row 6 from column B to column Z.
the line i am hoping to adjust for this is below it reads: "WST.Cells(6, 2)Then"
HTML Code:
If WS1.Cells(8, 2) = WST.Cells(6, 2) Then
WST.Cells(7, 2).Resize(30, 1).Copy
Worksheets("torr1").Cells(9, 5).PasteSpecial Paste:=xlPasteValues
nextrow = nextrow + 1
End If
View 8 Replies
View Related
Feb 23, 2007
Is there a way that I can add a statement or change my code to automatically have the checkbox checked if a certain value in a cell is greater than zero?
This is my
If CheckBox1.Value = True Then Range("RANGE_WATER_AND_SEWER").PrintOut Copies:=1
If CheckBox2.Value = True Then Range("RANGE_ELECTRICAL_SERVICE").PrintOut Copies:=1
etc. Note: I have about 80 checkboxes on my form.
View 9 Replies
View Related
