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 Repliesinformation about users selecting a folder at this link. However, how do I change the starting point to a network drive, as opposed to "My Computer"?
For example, we reference our network drives like this:
\OurSite.comSharedFoldersDivisionName
I would like a user of my program to choose the destination folder where a .txt file will be saved. I have code below which allows the chooser to browse through folders and choose a file:
Dim f1 As Variant, s1 As Variant
s1 = "#1: Choose the .txt file which contains montly data for the stock price."
f1 = Application _
. GetOpenFilename("TextFiles(*.txt),*.txt", , s1)
txt2.Text = f1
I want the user to browse folders and subfolders like they can with the above code. However the user will select a file path not a particular file. In the case of the code above, the text of Text Box txt2 would be set to the file path (i.e. "C:Documents and SettingsAdministratorDesktop")
I am working to make an automatic result card in which excel automatically gets data of every student which is present in sheet within the workbook by simply writing roll number of the student in a cell. and i have done it easily by Vlookup and index functions but the only problem i am facing is, that i want to show the picture of each student by roll no. pictures are in one folder on my hard drive, now i want that as the roll number of the student is written the result excel should display the picture (from the folder, each picture named by roll)of the student. for example i write 1234 in roll no cell and want to get and show the picture no 1234 in another cell. (from the folder)
View 2 Replies View RelatedI am trying to open a folder so I may choose the workbook to open. I am able to open a workbook but I want to open the folder which contains the workbook.
View 6 Replies View RelatedI have alot of project folders on my harddrive.
All in format: I:/12345-costumer-projectname/
The five digits are unique for each project.
I make calculations for these projects using an excel file. In this excel I also type the projectnumber (cell J2)
Now i would like to make a button. When pressed, it checks the projectnumber cell J2, looksup the corresponding folder and saves the excelfile in PDF format in this folder.
I have found macro to find files in folders, but none which do the above.
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
I have up to 50 workbooks in one folder with data in a specific range. I also have one workbook which includes additional data, including conditional formatting and dropdowns. I need to copy the desired range from the first workbook in the source folder to the second workbook, then save the latter to my destination folder, using the same name as the first. I need to repeat this process for all workbooks in the source folder.
View 2 Replies View RelatedI have an Excel VBA Macro for creating/logging drawing numbers. Each drawing belongs to a job number. Each job number has a folder name containing the job number followed by a description (ie 999999 - bracket assembly) for storing drawing pdf's. The job number is only known as a 6 figure number in the drawing creation process BUT for the PDF saving process it is a string value...as my example above
I have to change my process by pre-creating the Job Number folder, then have the macro look for the appropriate folder by searching the parent folder for a sub folder containing the job number (always the first 6 figures).
I want to insert in my macro some code that searches...
The parent folder for a folder containing the job number. The macro value for the parent folder is P:engineeringdrawings (this never changes). the macro value for the job number is iOpenair (it's an entry that is entered as start the macro). Imagine the value for the job number is "999999"...so the search would be for "999999*.*" The code needs to search the parent folder, find the folder name that begins with a six digit number. Capture the complete name of the folder and store that name as a Dim value that I can call up elsewhere in my macro.
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
I am creating a document for work that automatically generates hyperlinks to pdf files in a given sub folder. Currently I have a section of code that opens a window to choose the folder location.
Code:
'Prompt user to select a directory
Do
Problem = False
Set ShellApp = CreateObject("Shell.Application"). _
Browseforfolder(0, "Please choose a folder", 0, "c:\")
On Error Resume Next
[Code] ........
What I actually want is to not have the window open, but excel automatically choose the sub folder location.
The directory looks like this
Z:ClientProjectDRAWINGS-2 RECORD COPIESPDF
The xlsm file is stored in the DRAWINGS folder, and I need it to point to the PDF folder.
Currently I am using this ......
View 9 Replies View RelatedI 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.
I have a list of items in an excel sheet... 500 of them... and there are images for them in an other folder... The problem is those images are in 1000's and I only need those 500 images.. Images are saved as "SKU.jpg eg NS2354.jpg and we have an SKU column in excel as well .. SKU and their images have same name...
Is there any solution to pick up only those 500 images from those thousands of images?
Is it possible to save those 500 images in an other folder?
looking for some code to save to a destination
destination address is
C:Documents and SettingsstDesktopOJF
now the problem is OJF has folders named 1 to 500
so if cell d5 = 487 it will need to look in the above desination and then open the folder and save it there.
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.
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 RelatedI 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"
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.
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
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).
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
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 RelatedI 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
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?
I currently have the following ...
View 8 Replies View RelatedI 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.
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
I want to write a macro that would go to a certain drive and search for a file?
View 9 Replies View RelatedIf 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