I have a path to a folder that is already running. I just want to bring it up on the screen. Would that be done with AppActivate?
path:
"xxxxxLoanImportFileLoadingDockXXXXXSplitter"
Dim TargetDirectory as String
TargetDirectory = "xxxxxLoanImportFileLoadingDockXXXXXSplitter"
Looking for correct syntax.
I do not know if I have this written correctly, I would like to have the sub - Retro run whenever some one opens this worksheet - "FORM". The retro is also suppose to test cell H12 to see if it is blank before running the msgbox.
View 2 Replies View RelatedI have a Excel 2010 file that I want to activate the file and create a folder in it named customUI.
the steps to achieve this?
I have to run several different types of macros depending on the folders. each folder contains several excel files. so what i want to do is if a folder has a certain name run a certain macro, Here is the code:
VB:
Sub Macro1() '//Change the path to the main folder, accordingly
Call RecursiveFolders("C:Path")
End Sub
[Code]....
then call macro 2 end what syntax code should go there?
I would like my macro to go to a certain folder- in this case N:Aexeo ClientsJabre2008Excel Diet Run, open each workbook therein ( to this end I have found the first part of the code below on another thread) and perform the ExcelDietMacro (also below). I am missing something though as nothing is happening, would anyone know from a quick glance what is wrong? Should I have these as 2 seperate Sub End Subs or combined into one? I know that the Excel Diet is correct thanks to the original designer and Rory on the forum for getting it adjusted to my needs.
Also each workbook within the folder will have the same password to open, is it possible to insert some code in the macro to do this automatically?
Sub LoopFiles()
Dim MyFileName, MyPath As String
Dim MyBook As Workbook
MyPath = "N:Aexeo ClientsJabre2008Excel Diet Run"
MyFileName = Dir(MyPath & "*.xls")
Do Until MyFileName = ""
Workbooks.Open MyPath & MyFileName
Set MyBook = ActiveWorkbook
Application.Run "ExcelDietMacro"
MyBook.Save
MyBook.Close
MyFileName = Dir
Loop
End Sub
Sub ExcelDietMacro()
'
' ExcelDietMacro Macro.........................
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.
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 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?
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.
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.
View 5 Replies View Relatedif it's possible to use XlDialogOpen to open up a folder that you only have part of the name.
For example, say you have the following folder paths:
C:Main FolderSubfolder1Smith,Billy 2413
C:Main FolderSubfolder1Brown,Charlie Ray 2416
C:Main FolderSubfolder1Bunny,B 3619
C:Main FolderSubfolder1Fudd,E-G 1864
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?
When i exdcute my code in the userform, the line
AppActivate "Microsoft excel"
does not run, so it gives me
Run Time Error 5 :
Invalid proceduer call or Argument
I have a spreadsheet with several formulas where I have to go into each one of them to activate the calculation. I use F2 and enter. Automatic calculation is on. Do any of you know how this can be done automatically. A VBA-code will fit the purpose.
View 3 Replies View RelatedI have two or more DIFFERENT INSTANCES of excel workbooks open at the same time. EX: Wk1, Wk2, & Wk3
Currently I have Wk3 showing in the screen.
Through a Macro, how can I display (bring to front) Wk1 without closing Wk3 ?
This is part of a longer macro, so I only need to know how to do above.
I have an open workbook(A.xls) where the user can press a button which opens another workbook (B.xls) In workbook B they need to add new names then press another button to run another script. The script needs to switch to workbook A in order to work correctly. How can I switch to workbook A without using the name of the workbook? The reason I cant use the name to activate is because the first workbook that is open is not always A.xls
View 4 Replies View RelatedActiveCell.Activate. I'm reading through a VBA book and one line of code was
View 4 Replies View RelatedI'm reading a hyperlink into a spreadsheet using an =index,match,match formula. The Hyperlink appears as the correct one when you read it in the cell, but it does not link to that address....
View 8 Replies View RelatedI have a cell (B2) in which there is a formula referencing the value of another cell with the purpose of generating a URL- ="[URL] such that when a numeric value is entered in B3, B2 is made to contain the full URL including B3 as a unique identifier.
I have a macro which straightforwardly copies B2 and pastes its value in the same cell, rendering the text value of the URL.
How do I go about activating the URL as a hyperlink in the macro? It seems like if I click in the cell and hit return, or right-click/Hyperlink.../OK I'm creating a macro to reference the exact unique identifier present at the time that I'm recording the macro- e.g. if B3 is "123", I'm setting the macro to set B2 as [URL] rather than the actual unique identifier in B3.
Has anyone successfully activated the content of a cell as a hyperlink dynamically based on its exact content?
In the following code, I have a find sub... when the user enters a date and hits the "Enter" key, is there a way to bypass the "Ok" key?
Private Sub cmdFind_Click()
Dim ws As Worksheet, myDate
Dim rFoundDate As Range
'check for valid distribution date (between October 1, 2006 thru December 31, 2014)
myDate = txtFindMyDate
With myDate
If DateValue(txtFindMyDate) < DateValue("10/1/2006") Or DateValue(txtFindMyDate) > DateValue("12/11/2014") Then
MsgBox "Please enter a date between October 2006 and December 2014"
.SetFocus
Exit Sub
End If
End With
Cells.Find(What:=txtFindMyDate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Unload Me
End Sub
I have 5 Optionbuttons (in userform) named: Sheet1, Sheet2, Sheet3, Sheet4 and Sheet5.
When I open workbook Optionbutton1 (Sheet1) is activated, but what kind of loop(?) I need, if I want activate same named optionbutton than activate sheet.
Example: I activate sheet3 then Optionbutton3 (named sheet3) have to activated also.
I tried simple codes,
First worksheet:
Private Sub Worksheet_Activate()
OptionButton1.Value = True
End Sub
Second worksheet:
Private Sub Worksheet_Activate()
OptionButton1.Value = False
OptionButton2.Value = True
End Sub
I am getting old and can't remember how to activiate a graph to accept and show new numbers.
Example: In "Charts and Graphs for Microsoft Office 2007" by Bill how does one get the graph (Figure 3.35) to accept data for the coming months and have the data reflected in the Linear Trend Bar (the forecast bar is not in the figure) but I would like to include it?
1. What's the difference between:
Worksheets("Sheet1"). Range("A5").Select
and
Worksheets("Sheet1").Range("A5").Activate
?
2. Can I use a Selection. method with an object 'selected' using .Activate in the preceding code line?
3. Say I am currently on Sheet2, Cell A5. When I click the macro button, I want Excel to copy the value from Sheet1, Cell A5 and paste it into Sheet2, Cell A5. But I do NOT want the screen display to actually jump from Sheet2 to Sheet1, and then back to Sheet2. So do I need to use .Activate, or .Select, to copy the value of Sheet1, Cell A5?
Is there a way I can from vba cut a file from a folder and put in to another folder? And also create a folder? If yes, how can I do this?
Is it better to use dir or scripting.filesystem object?and if there is an advantage to use one over the other.
I have a bunch of array formulas that need to be activated by going on the cell, hitting F2, then hitting Ctrl+Shift+Enter. In the attached sheet I have done a record macro to automate this. I have another sheet with something similar just a lot more items on Sheet1 (~250 items). It will be very time consuming for me to record macro and hit F2, then hit Ctrl+Shift+Enter ~250 times so I am wondering if there is any way to activate all the array formulas on the sheet in one go using 1 formula. I'm not too concerned with file size.Also, not all arrays only include columns A and C, other columns (D, G, H and J) are included too.
View 3 Replies View RelatedIs there a way to activate a Macro with a conditional in one of the cells? It's like this, I want that if the content of A2 changes to "True" then C2,D2 and E2 change to bold and the background color change to yellow. Is there a way to do this?
View 14 Replies View RelatedLearning VB6 by trial and error from code snippits on the web, however, am embarrassed to say I can't solve this simple one:
Trying to Open an Excel sheet and read it from VB6 code written in another Excel file.
What is the quickest way to eliminate .Select, .Selection and .Activate from code to increase efficiency?
How do I refer to the range and workbook without selecting them first?