I am trying to streamline the code in a macro. It has a BrowseForFolder to "set oFolder". Since the folder location is fixed, is ther a way to remove the browse function, and eliminate the selection process?
HTML Code:
Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "PLEASE SELECT FOLDER", 512, _
" est estfile")
If Not oFolder Is Nothing Then
foldername = oFolder.Self.Path
If Right(foldername, 1) <> "" Then
foldername = foldername & ""
End If
End If
The ultimate goal is to make the "oFolder" a string that can be variable for different paths.
I have code below which brings up a dialogue box and allows me to sect an excel file. Is there a way to change this code so the dialogue box will ask me to select a folder instead? I want to be able to select a folder and the rest of the code will open the files in the folder.
Sub Euro() Dim wb1 As Workbook Dim sFileName1 As String myMsg = "Please Select Euromcontact Value File" Response = MsgBox(myMsg, vbExclamation + vbOKOnly, myTitle) sFileName1 = Application. GetOpenFilename If sFileName1 = "False" Then Exit Sub Set wb1 = Workbooks.Open(Filename:=sFileName1) With wb1 .Activate End With End Sub
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.
I 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 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.
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?
I would like to create a code that will open all workbooks in folder "A" one at a time and then close the work book after another code is executed. So one workbook will open and while the workbook is open, I will call another code to format the active workbook and then the active workbook will be saved in folder "B" and closed and go to the next workbook in folder "A" to start all over. The folders are saved on the desktop.
What I'm hoping to do is just look for the 4 digit number on the end finding that folder name and opening it up. The name conventions before the 4-digit serial number are quite random. Sometimes it includes their full first name, other times it's just an initial for the first name and other times there are other characters included in the name. The one thing that is constant is the 4 digit serial number on the end.
how I could get the Open dialog box to pop up and have the chosen folder number open?
I have a multiselect listbox with values that gets populated from a sql statement, and I would like to get is the first or second index from the selected item. I know how to get the listindex from a combobox by using:
VB: cbnumber.List(.ListIndex, 0)
How can I loop through and get the 1st index number for the selected items only from the listbox? I want to pass this index number to another sql statement.
I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now.
Private Sub Worksheet_Activate() If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then Me.ClearPrevious.Visible = True Else Me.ClearPrevious.Visible = False End If Sheets("MASTER").Select End Sub
I need to get a macro to select all the data in column "A", sort it in ascending order, omit the blanks if any, then select (highlight) all the data so that another macro can be run.
When I record it, it will only record up to the last row I highlight but the data always changes so there could be more or less.
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
Private Sub CommandButton1_Click() Worksheets("Sheet1").Activate Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial End Sub it errors to: SELECT METHOD OR RANGE CLASS FAILED
I am trying to figure out a way to open a folder through VBA. I just want the G: drive to open and then it lets the user navigate any folder within the G Drive and select which ever file they choose to open.
How do I write this to always save in a particular folder? C:Documents and SettingsErinDesktopJob Cost Estimates and how is it different if I am using a shared network?
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim varFileName As Variant
I am trying to save a file to a different directory but am not having any luck with the syntax.
so far have created new folders on the X drive. The x drive folders have been assigned a variable name which I am attempting to use again when telling the save as function where to save the file. the variable name is not being picked up in the new macro. I have declared the variable name as public at the start of my module.
