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
Is there a dialog box like the one used in GetOpenFilename that can be used to select a directory?
I'm trying to select a directory/folder which will change each month but the macro selects the files from inside the folder once it's specified. I don't need the name of the current path; the path name needs to be able to be selected.
I am trying to set a macro that will delete a sheet which the spreadsheet user types the name of the sheet into a dialog box rather than go to the actual sheet and delete the sheet.
I am making custom time sheets to suit our agriculture business - these excel sheets get sent out to the different farm managers who send back in staff times.
This code below is brilliant and works perfect for our needs. However I need to be able to emailPDF the sheets not print.How / where do I change code so the selected sheets go to Save & Send via email as a PDF instead of going straight to the default printer .( hard copy )
VB: Option Explicit Sub SelectSheets() Dim i As Integer Dim TopPos As Integer Dim SheetCount As Integer Dim PrintDlg As DialogSheet
I have an Excel Addin that I deploy as a read-only addin on a server share. I instruct my co-workers to add the Add-In as they would any other Add-In, but I tell the to answer NO to the Windows question "Copy 'AddinName' to the Addins folder for John Doe?".
That way, everyone will be working with the server version of the addin, and any time I want to roll out an updated version I just place a new addin file on the server. The next time the users restart Excel, the new version is loaded.
Except users never follow directions and some of them click the default 'Yes' when asked and get a local copy of the addin and never receive updated versions of the addin.
How do I disable the "Copy Addin" question completely? I don't want to depend on the user. I know this is possible because I have this 3rd party addin that we use that is installed from c:Program Files and it just stays there and you're never asked to copy the addin when you install it in Excel.
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 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'm building my first Add-In, which I'm using to gather all Sub's that I've created through my small VBA writting career. The thing is that when I write new code and some reason exit Excel and forget to save the XLA, obviously the code is lost. I've used:
I have some code in a standard module. When I try to run it, it does not appear in the dialog box. The code's below.
Code: Sub CopyCat(ByVal Target As Excel.Range) If Target.Column 21 Then Exit Sub If Target.Value = "Y" Then Cells(Target.Row, "A").Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) Cells(Target.Row, "B").Copy Destination:=Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1) Cells(Target.Row, "C").Copy Destination:=Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1) Cells(Target.Row, "D").Copy Destination:=Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1) Cells(Target.Row, "E").Copy Destination:=Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1) End If End Sub
ive created a spreedsheet for work calculating money etc from different tills. is there a way i can have a box appear when the spreadsheet is opened asking for particular information to be entered.
I have created a worksheet with Excel Dialog worksheet. I have Edit Boxes and have input data into them. Is it possible to add these data and put the result in another Edit Box,
I've written a function macro that results in a dialog box popping up as in ....Application.Run "showsampdat"
What is the general Syntax for adding a macro line that will enact the OK Button on the Dialog box so that the user will never see the dialog box but the OK button functions?
I have created a dialog box for entering data into once that data has been entered i want the dialog box to store the data into a separate worksheet at A2 in then want the dialog box to clear and reappear ready for new data to be entered. This new data would then need to be entered at A3 etc etc etc. Is this possible and how would i go about doing this.
I am very sure that I have deleted all my macro in my excel spreadsheet and saved. However, when I tried to open the file, the macro dialog prompt me again..... I have press alt F11 and confirmed that there is no macro.
I'm modifying a template that originally shipped with Excel 2003 (I have not upgraded to 2007). In the template, there is a pop-up box (not a dialog box) that shows up when I'm on certain cells. I've attached an image of it. It's the yellow box containing the words "Company Information..." etc. I cannot find any way to remove it! It's not a comment, and selecting it doesn't allow you to edit it. What is it, and does anyone know how to remove it?
I would like a macro to print 2 sheets in a workbook. I can write the macro that will select the sheets I want, but I don't want it to just go and print because depending on if the user wants it printed in color, b&w, etc.
they have to choose a different color. What code do I use to open the print dialog box?
