Change The Folder Location
Dec 14, 2006
which part of this macro I need to edit to make it open a folder I want?
Sub OpenFolderRequest()
YesNo = MsgBox("Would you like to open the folder to see" _
& vbCr & "which files are currently there?", vbYesNo + vbQuestion, "Open Folder?")
Select Case YesNo
Case vbYes
myval = Shell("c:winntexplorer.exe c:winnt", 1)
Case vbNo
End Select
End Sub
I have tried changing the (c:winntexplorer.exe c:winnt) to the location name of the folder I want to open (S:Stock ControlSTOCK CONTROLOrder Confirmation)but it doesn’t seem to work.
View 9 Replies
ADVERTISEMENT
Feb 24, 2008
When someone right clicks on a cell and chooses "Hyperlink" current folder is being displayed as a default.
is there a way to change this for a particular workbook through VBA or API calls ?
I would like to always have "c:" as the default folder no matter where the workbook is stored.
View 9 Replies
View Related
Apr 20, 2012
How to query the current folder location in VBA?
The macro will need to import text from a file (file.out) located in iteration folders (iteration_001, etc) but I need to start by seaching the location that the excel file has been saved in.
View 5 Replies
View Related
Nov 12, 2008
I am trying to generate a folder within a specific location (For now lets call it C:Jobs) that will be named as per the adjacent cell (Column H on attached)
Obviously if the folder already exists I want the code to stop.
But this code will have to generate a different folder for each row within the spreadsheet.
View 8 Replies
View Related
Jul 12, 2012
I'm trying to write a set of macros and one of the macros needs to move a folder with subfolders from P:CJ to P:WO
Now I've been using the CopyFile State. Is there something similar because in my search they talk about using FileSystemObjects and I have now clue how to use those.
View 1 Replies
View Related
Oct 10, 2007
I am running WinNT and need to find the folder containing the default icons available for use on buttons in the toolbar window. I can copy the image but it saves as a picture (device independent bitmap). I want to be able to send the icon to other people so they can put it in their directory and choose to select it.
View 3 Replies
View Related
Jun 4, 2012
I have a folder "Macro" in the below location in my PC:
C:UserskkumarDesktopMy WorksMacro
There are 4 excel files in this folder:
Sales.xls, Quantity.xls, Forecast.xls and Macro.xls.
Macro.xls has 3 sheets: Sales, Quantity & Forecast.
I want a macro which will pull all data in:
Sheet1 of Sales.xls to "Sales" sheet of Macro.xls
Sheet1 of Quantity.xls to "Quantity" sheet of Macro.xls
Sheet1 of Forecast.xls to "Forecast" sheet of Macro.xls
Also one additional requirement is if I copy the Macro folder to some other location in my PC the macro should still work.
View 3 Replies
View Related
Jul 23, 2012
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.
View 9 Replies
View Related
Feb 7, 2014
I have a template for information that needs uploaded into a database via CSV. This database is then access via a HTML front end. As part of this there is a 'folder structure' with in the front end. This is displayed in a database table (See attached excel file for example data structure)
Currently I have this feeding into a drop down list which is somewhat cumbersome so want to make it more efficient and easier to use. I had toyed with the idea of adding more columns into the excel template and split the string up to populate however this isnt exactly professional looking.
My current thinking is, is there a way of putting this data into a listbox which is easy to navigate? Ideally in a folder tree navigation structure, I know this is possible for a windows folder structure
Folder Structure.xlsx
View 1 Replies
View Related
Apr 14, 2014
i want to use the "Browse for Folder" to select the folder where the files is in eg. E:My DocsGlobal and list the folder location and filenames to table (column I:J), and color them according to every folder location.
I would like the "Browse for Folder" windows to be able to select multiple files instead of just one file a time.
View 1 Replies
View Related
May 21, 2008
Sorry that I'm very new in Excel VBA coder. And, for this topic, I don't even know how to start. I want to make a MS Excel database of a numerous files. This database must be consist of Filename, Location, and it's attribute (let's say updated date, size, hidden status)
View 2 Replies
View Related
Feb 2, 2014
I have the below code that saves selected sheets of my workbook as pdfs in the current file location. What I would like this code to be able to do is to create a new folder (named with todays date), and then save each of the pdfs into this folder.
Code:
Sub SaveWorksheetsAsPDFs()
Dim sFile As String
Dim sPath As String
Dim fPath As String
Dim wks As Worksheet
[Code] ........
View 3 Replies
View Related
Feb 10, 2014
1. A workbook is closed
2. In addition to the original workbook being saved, a copy of the workbook with the current date is also saved to the specified location of my choice.
For example
"C:UsersUSER1DesktopBackup Test as of 02-10-14"
3. If a copy of the workbook is already saved with today's date, then overwrite it automatically without prompting the user.
4. All the above happens without any user interaction.
View 8 Replies
View Related
Aug 7, 2012
I have a row b1, b2, b3, b4, b5 etc. I need to change it to c1,,c2,c3,c4,c5 etc. Is there a fast easy way to do it. I know about $ but I'm past that.
View 6 Replies
View Related
Oct 29, 2008
When I use the GetOpenFilename() method, it seems to default to the particular users My Documents. Is there a way to force it to default elsewhere like a share drive, or the users desktop?
In the same line of thought, if I have a file named TestFile.xls. and I want to try to open it from the users desktop if they have it, how can I do this, since their desktop location is different than mine?
View 12 Replies
View Related
Aug 27, 2013
I created a drop down in a file that contains a list of locations. The people that run these locations have to input data (mileage) in other areas of the worksheet. Is it possible to have the data in the cells change as someone changes the location in the drop down so the information is not lost or overwritten each time?
I am really trying to avoid making a workbook with several tabs.
The cells that are colored blue are the cells that the people will have to put input into. I would like to be able to keep the data from all the cells that are blue each time they change the location. The rest of them are already set to change with the location change via a formula. Those are all based on data that I pulled and was able to provide.
View 2 Replies
View Related
Sep 16, 2013
The comment boxes keep changing shape and location in my spreadsheet. Some are completely smashed together, while others are extraodinarily wide. I had them all to the right of the cell they applied to; but now they are all over the place.
View 1 Replies
View Related
Sep 28, 2007
I have a file that has 114 rows of data. One for each office I'm working with. I need to insert 6 rows in between each of those rows and type text into some of the fields. I recorded a macro (since I don't know how to write one), but it uses a static location for the rows and data, as opposed to a relative location. So, what happens is, I get the same data entered over and over again. Also, even if I get this to work right with a relative location, I still would have to run it 114 times. I'd like to create one macro that would do the entire document. Here's what I've recorded: .....
View 9 Replies
View Related
Sep 5, 2009
I have a line in my VBA code that i need to change the location of Sheet1 to another workbooks. Instead of Match at Sheet1 at the same workbook I need to match with sheet1 at another workbook called ("Master.xls). The Original Line:
View 3 Replies
View Related
Feb 7, 2007
I am trying to achieve can not be based on a range of rows or cells it must as this data is imported from a forecasting application and the location of the targets may change.
Perhaps an offset to the current selection can be used some how, but have a look-see if you know where Im coming from.
'I have used this to find a target in a data range.
Dim r1 As range
Public Sub FindDataIn Range(r As Range, target As Variant)
Set r1 = r.Find(target)
If r1 Is Nothing then
Msg Box target & " was not found"
Else
r1.Select
End If
End Sub
'Then I inserted and Named this procedure to find the data on various assumptions or targets - SUCH AS THE VALUE "14306".
FindDataInRange ActiveSheet.Range (A1:A226), "14306"
Selection.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=x1Down
Selection.EntireRow.ClearContents
Then I write this again for another Target such as 14307 and it repeats
The result is that it finds the target cell I get an empty row above the two rows that contain that target. (LET ME EXPLAIN WHAT I MEAN BY TWO ROWS)
The thing is there are two rows containing 14306 in that range and the range is sorted ascending so that they are positioned one under the other. Each row has different forecast totals beside this number because one is an export SKU and one is a Domestic.
What I want to do is combine the two rows as one with one row of forecast totals for the number rather than two.
Like this:
14306big Widget Domestic 26 89 (This is combined as a new row)
Instead of this:
14306big Widget Export 12 14 (These 2 rows are then deleted)
14306big Widget Domestic 14 75
Also the Forecast totals run across 12 columns (one for each month) and then there is a column for year totals of each row that needs to remain the same.
Most important this here is this cannot be based on a range it must as this data is imported from a forecasting application and the location of the targets may change.
View 9 Replies
View Related
Mar 11, 2014
I have the following code written but I'm wondering if it's possible to modify this to change the red line to update to the path that the workbook is saved in? Meaning that User1Folder1 would change but [Workbook1.xlsm]Sheet1'E1 would always be the same.
[Code] .......
View 2 Replies
View Related
Sep 28, 2008
I use an image analysis program ImagePro which can call Excel within it's macro language (which seems to be visual basic). I looked up on this site how the change the drive (ChDrive command). But still when the Excel section (after With oExcel) executes the default file location in the browser is in My Documents on the C: drive.
Prior to this code Excel has been launched by this ImagePro macro, and a file Cumberland Template has been opened from the C: drive. Now I would like to do a Save As, but have Excel start at the I: drive location.
View 7 Replies
View Related
May 30, 2008
I have a workbook with approximately 30 worksheets. This workbook already has a massive macro that I've written. I'd like to write a code that will take a designated worksheet, check to see if there is data in cell A2, if so, save it as a new workbook.
The name of the workbook should be predetermined, for example "SIA April(Previous Month) 2008(Current Year) P-Card Import Template.xls"
The months and years will need to change.
The file to save it in will also change monthly- for example- G:PCard DirectoryCloses2008 ClosesApril(previous month) 2008(current year)
View 9 Replies
View Related
Jun 2, 2007
i have as many as 100 workbooks in the same folder(such as "e:/data"),i should change the same address value in each workbook. first i write the code use "application.filesearch",but it can't run because it has removed FileSearch in excel 2007. i get the code below from Ron de Bruin
Sub test()
Dim wbOpen As Workbook
Dim ws As Worksheet
Dim I As Long
Dim myfiles() As String
MyPath = "e:data"
If Right(MyPath, 1) <> "" Then
MyPath = MyPath & ""
End If
'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If...................
View 2 Replies
View Related
Dec 18, 2013
The CFO wants to change the name of the top folder on our network drive at work.
I have literally thousands of files in different folders within this top folder, and changing the name of the top folder is going to cause hundreds of thousands of reference errors.
Is there a best way to protect these links, other than to find+replace for hours and hours?
View 5 Replies
View Related
Oct 20, 2006
I know how to create a backup file for an excel file (General Options Menu in Save As dialog box, we have to tick the "Always create backup" check box). When we tick this option the backup file is created in the same folder. Can we specify the path were this backup file can be stored and updated each time when we save the main file.
View 4 Replies
View Related
Oct 6, 2009
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.
simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
View 6 Replies
View Related
Nov 11, 2013
There is a folder with all excel files with the same structure. I need a macro, who opens one file by one in a folder, change the layout, and save it too same place with same name. Changing the layout will I do with macro record.
Sub AllFiles()
Dim MyFolder As String 'Path containing the files for looping
Dim MyFile As String 'Filename obtained by Dir function
Dim MyBook As Workbook
MyFolder = "D:LABODIESTSOST_DIEST" 'Assign directory to MyFolder variable
[Code] ......
View 9 Replies
View Related
May 14, 2014
I 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.
View 4 Replies
View Related
Mar 15, 2013
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".
View 9 Replies
View Related
