Get A Macro To Look In A Subfolder Relative To Its Current Directory
May 28, 2014
This Macro (Located within "SWMS Auto-Fill.xlsm) scans the folder its in and transfers information into select cells within each .xlsm file it finds. However I need to move the SWMS Auto-Fill.xlsm file to the parent directory.
So I need it to scan: Workbook DirectorySection4
Code is below:
VB:
Sub Autofill_SWMSs()
Dim MyPath As String
Dim MyFile As String
Dim Wkb As Workbook
Dim Cnt As Long
[Code]....
View 1 Replies
ADVERTISEMENT
Jan 10, 2012
I would like a macro that saves the current open workbook to a directory specified in a cell on the open work sheet.
View 4 Replies
View Related
Jun 12, 2014
Getting attachments from outlook subfolders.
My attachments are in Inbox > First subfolder (sales) > second subfolder(Sales 1)
I need to save attachments from second subfolder (Sales 1) to my drive.
Currently i got macro which picks up attachments from first subfolder (Sales). The macro code is
Sub SaveAttachmentsToFolder()
On Error GoTo SaveAttachmentsToFolder_err
Dim ns As NameSpace
Dim Inbox As MAPIFolder
Dim SubFolder As MAPIFolder
[Code] ...........
View 2 Replies
View Related
May 15, 2013
I am trying to use VB to save a workbook into the same directory but with a file name that references the folder it is stored in...if that makes sense!
Here is where I have got to so far but fails on save,
Sub Rename()
Application.DisplayAlerts = False
mdy = Month(Now()) & "." & Day(Now()) & "." & Year(Now())
fold = ThisWorkbook.Path
FName = fold & "-" & mdy & "-(New).xls"
SaveName = fold & "" & FName
ActiveWorkbook.SaveAs (SaveName)
Application.DisplayAlerts = True
End Sub
View 3 Replies
View Related
Aug 22, 2013
The issue is the I want to put the condition here in my the code that only copies the desired cells if the sheet is named specifically such as "Jan", if not named as this the worksheet should not be used. The current code I am using was posted on this site in 2009: VBA to copy specified cells from all the files in a folder
My modified code is:
Option Explicit
Public strSourceFldr As String
Public EachFile As Object
Public objFSO As Object
Public objFolder As Object
Public objFile As Object
Public strSheetName As String
[Code] .........
View 1 Replies
View Related
Dec 5, 2008
i am trying to do, i have a spreadsheet with 100+ tabs all with the day of the year. Jan1,Jan2,Jan3...Feb23,Feb24,Feb25... etc etc. I want to pull all of the same values for each of these sheets on a single tab (using vlookups, hlookups and reformatting the information for a macro to input all of this data into a database)
My question is, how can I put a entry form, lets call it a1 and fill in the tab i want the contents of the page to update with? For example, if i input "Jan18" in a1, all formulas on the page look for their source information on A1.
View 2 Replies
View Related
Sep 25, 2008
What is the easiest way to display in a cell the opened file's directory path where the file is stored? I would like to use some sort of formula (instead of the VB route) but I don't know if such a thing exists.
View 5 Replies
View Related
Oct 31, 2006
I would like to select a range of cells relative to the current cell and move them to the right two cells. Basically, if I were in cell A1 I want to be able to have a macro select A1 - A8 and move them over by two cells leaving A1 and A2 blank.
Excel 2003
View 9 Replies
View Related
Nov 29, 2013
what I am doing wrong in this script to open a subfolder with a name starting with the value in cell (1,1) ?
VB:
Option Explicit
Sub FolderInfo()
Dim fso As New FileSystemObject
[Code]....
would this code also look in the subfolders of the subfolders? I'm looking for this function also
View 6 Replies
View Related
Mar 12, 2013
I need to activsheet create in .pdf witch i did with this
VB:
Sub Save_As() ' macro za snimanje racuna u PDF pomocu Microsoftovog ugradjenog alata za Office 2010
Dim txtName As String
txtName = "D:
eports" & Range("G14") & "_" & Range("L3") & "_" & Range("E18") 'ime nove datoteke i folder u koji se snima
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=txtName, _
OpenAfterPublish:=False
End Sub
and for that pdf file i need to create folder based on cell e.g. q1 and sub folder l3 and all that in case that they dont exist and if they are just put file in it. For example i fill out sheet (one of many in one day) and i click buton and sheet go to pdf in subfolder L3 in folder Q3.
View 1 Replies
View Related
Mar 7, 2014
The code below is sending back an error. It's supposed to create a folder and a subfolder within it and if the folder already exists then a msg box should pop up saying that folder already exists.
[Code] ....
View 14 Replies
View Related
Nov 17, 2006
I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.
Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"
View 3 Replies
View Related
Jul 5, 2007
The title pretty much sums it up, actually. I have a macro, and a beautiful piece of work it is. It selects a particular selection of the active workbook, parses it in a particularly thrilling way, and writes the result to a text file. I'd like it to do this for ALL the files in a particular directory (somewhere over 500 files, if that matters).
View 2 Replies
View Related
Jun 1, 2006
I can record a macro that will sum a column of data at the end of data in that column - simple; my problem is getting it to work on other workbooks that have a different amount of rows in it. I tried recording the macro using the relative button as well as without using it, but either way I do it, I can never get it to work on a different workbook. For example, I record the macro, then remove a couple rows after to simulate running on another workbook, and then when I run it, the sum gets put in the cell that used to be the end of the column instead of the real end of the column…Same is true if I add some rows. I don't know if I am using the right terminology below (OK, I know I'm not) but it gets the message across best when I say I think I need something that will do the following (substitute "code" for "make believe code"):
1. Go To cell F1
2. Go To last entry In this column
3. Move down one cell
4. Sum F2:Fx (where x = one cell above current selection - the last populated cell In the column With data In it before the sum)
Instead of this (what I have right now)................
View 6 Replies
View Related
Mar 9, 2013
The code bellow allows me to save a copy of the invoice by way of doPDF using invoice number as the name of the pdf to be saved.
Code:
Else
Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= "E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _
[Code]...
As there is the need to, should a quotation or an invoice be wrong, to change it. In other words I have code to cancel the invoice as bellow.
Code:
Dim sResponse As String
Dim rFound As Range
sResponse = InputBox("Enter record you want to change." & vbNewLine _& "ATTENTION!
This will change the current record number and will erase record data from saved records sheet.")
If sResponse = "" Then --cancelled or OK with no entry: do nothing or add message
Else
Set rFound = Sheet1.Columns("L").Find(What:=sResponse, LookIn:=xlValues,
[Code]...
The code above simply removes a line on sheet1 to which yet another macro retrieved some info from an invoice such as date, invoice, customer and total.
Now I just realized that when I change an invoice, I will be again when finished, creating another pdf file that already exists.
How can I have the second macro here changed so that it will go to ""E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _" and delete this pdf file?
View 9 Replies
View Related
Nov 11, 2013
I have a 2 columns.
A: Current location Ex(C:UsersDesktoplabelsA02FAA.jpg)
B: Future location Ex(C:UsersDesktopoxes98300SA02FAA.jpg)
I want to move jpgs from location A to B
is there a way to execute this with a macro?
View 5 Replies
View Related
Aug 11, 2006
I got a task from my lab to write a macro in excel to "automatically email the detailes of the file listing from c: dir "
View 7 Replies
View Related
Oct 13, 2006
I would like to make a button that, when clicked, opens the following directory in a seperate window: "C:Documents and SettingssharkfootDesktopTNT" Here is what I tried...
Sub OpenLocker()
ActiveWorkbook.FollowHyperlink Address:="C:Documents and SettingssharkfootDesktopTNT"
End Sub
Because I am still a novice and because i wrote this myself, it doesn't work.
View 2 Replies
View Related
Oct 27, 2008
EXAMPLE: Complete Sheet called "Day1". When day1 is complete you click on button and it then copies itself and creates and renames new sheet to "Day2", then when "Day2" is complete you click on button and it then copies itself and creates and renames new sheet to "Day3", and so on and so forth to "Day30".
View 9 Replies
View Related
Dec 3, 2008
How can I run a DOS .bat (batch) file from within a Macro? This is what I came up with so far and it seems to work. The only thing is that I need this to have relative paths, running from the same directory the Excel Workbook is in.
View 2 Replies
View Related
May 8, 2009
I have recorded macros in the attached file to highlight cells that contain matching initials by using conditional formatting on the first cell and copying it to the remaining cells. The problem is that if rows are inserted above that change the first cell reference then the macro no longer works properly. Is there a way to use a relative cell reference within the macro? The macro buttons are in cells A4 to A13. I have no VB experience and created the macros by recording keystrokes and using tips found in this forum.
View 14 Replies
View Related
May 19, 2014
This one is a two parter. Part I is a bit of a necessity and Part II is a nice to have that would save me a boatload of time.
Part I: I need to assign a macro to a specific object in a cell. I've seen the code for targeting a static cell, but my data can be filtered and sorted.
For example, I have a cell in I6 that has text "Partial Deployment" that I want to run a macro on. If I were to sort alphbetically, this cell could end up as I20.
How to assign a macro to the text inside the cell? Also the text is not unique.
Part II: I would like to write a macro using relative referencing for similar to the below:
When user clicks on said object from Part I, the macro reads the corresponding column in that same row and users that to filter data on a separate spreadsheet.
Example: User selects "Partial Deployment" in cell I6, the macro references cell E6, copies the number "191", and uses that number to filter data on a separate spreadsheet.
View 4 Replies
View Related
Jan 27, 2010
This recorded macro inserts a line below the cell that active when it was first recorded. It then copies some text and a formula to the line that was created.
Sub Macro11()
Rows("10:10").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A9:E9").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Range("H9").Select
Application.CutCopyMode = False
Selection.Copy
Range("H10").Select
ActiveSheet.Paste
Range("F9").Select
End Sub
If you run this macro again at a different cell position, it goes back to the first starting position and repeats the same action. I see that the cell references from the first recording are fixed in the macro. I can't figure out how to make the macro use the new current cell position as the starting positiion when it runs again.
View 4 Replies
View Related
Nov 23, 2006
I have a macro in excel that saves to an external text file, but the excel workbook (and the text file) will be copied to different folders so i need it to alway read the file from the location the worksheet was opened from. I hope that makes sense
Ive tried using ActiveWorkBook.path but that doesnt work, maybe im just using it the wrong way
View 5 Replies
View Related
May 12, 2007
I'm trying to create a macro using Tools/Macro/ Record a New Macro. I need to record in the relative mode, but I cannot find the Relative/Absolute button. I have gone to Tools/Macro/Stop Recording but it only allows me to stop recording. I am on excel 2002
View 3 Replies
View Related
Sep 5, 2013
The script collects all the files in directory and sub-directories and list them in ascending format, I want them to get in transpose format. like for example: Root folder has many sub directories and in them a sub directory XYZ has 5 excel files, it will get the sub directory name in Col A and transpose all .xls files.
Col A | Col B________|Col C_________ |Col D___________|Col E________|
XYZ__|C:/root/test.xls|C:/root/Sales.xls |C:/root/Report.xls |C:/root/sam.xls|
[URL]
View 8 Replies
View Related
Nov 26, 2009
i'm looking for a macro which within a sheet named "foldernamedump" will list in a column the folder names within a directory I specify. I have seen a couple of sample codes but I just cant seem to get them working at all so I think its best to start from scratch and the work i get supplied here is always perfect. I also want the macro to clear the contents of the sheet before it loads again just to ensure there is no old data within the sheet.
View 5 Replies
View Related
Jun 17, 2014
I have recorded the below code that functions fine from my computer. My issue is that I need to export/import this macro to other workstations who may have the save to location mapped to a different drive letter. Is there a way to replace the drive letter with the URL (SharePoint)?
ChDir "E:3. CRQsRemedy Dumps"
ActiveWorkbook.SaveAs Filename:="E:3. CRQsRemedy DumpsToday_CRQ_8Dump.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Save To Directory: \server_1Dir_1Dir_2FolderToday_CRQ_8Dump.xls
Permissions are in place.
View 1 Replies
View Related
Aug 20, 2013
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
View 1 Replies
View Related
Mar 31, 2014
I have a problem with attached workbook, I downloaded from this forum,its what I need but problem is that I don't know much about macro & vba.The formula works fine when I make a single letter worksheet but when I enter a name it doesn't.
View 2 Replies
View Related