Macro: Create Directory Based On Active Workbook And Save To It
Read “My Documents” Path And Use Result
Problem:
Note:
Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003
1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.
2. Per customer request, VBA application needs to save extracted files for future use.
3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.
4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:
Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Create Directory From Cell & Save Workbook With Cell Value
what code can be used to be place in a Command Button on a worksheet that will use the value in a cell, change it to the directory where it have to be saved and save it to the value of another cell. Example: On a worksheet called "Customer", at cel A1 will be the customer's name and at cell A2 will be the asset number. Now let's say a directory does not yet exist on the root for this customer, a new directory must be created in the root directory by using the name in A1, and the workbook must then be copied and saved under the value of A2.
View Replies!
View Related
Create & Save Workbook To Same Path As Macro File
I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property." Dim objXlApp As Object Dim wkb As Workbook Dim wks As Worksheet Set objXlApp = CreateObject("Excel.Application") ' Create a workbook Set wkb = objXlApp.Workbooks.Add ' Delete all worksheets bar the first one. For Each wks In wkb.Worksheets If Not wks.Index = 1 Then wks.Delete End If Next wks 'Create some worksheets and names With wkb .Worksheets(1).Name = "myWorksheet1" .Worksheets.Add.Name = "myWorksheet2" .Worksheets.Add.Name = "myWorksheet3" .Worksheets.Add.Name = "myWorksheet4" End With...................................
View Replies!
View Related
Defining Workbook Save Directory
I have managed to get my workbook to save with a filename based on two textboxes. I am not sure, however, how to define the directory. I have tried adding a ("mydirectory") on the saveas line but it does not work. This is my Private Sub CommandButton2_Click() Dim wb As Workbook Set wb = ActiveWorkbook Dim strText As String strText = txtLMSRef.Text & "," & txtPartial.Text wb.SaveAs Filename:=strText wb.Close Set wb = Nothing End End Sub
View Replies!
View Related
Save As And Close Active Workbook
I am trying to create a form button that will save as and close the active workbook. I have the Save As code working but I can't find any code to close the workbook that will work. A couple of issues: I don't have control of what the workbook is called - the user will name it - so I can't do a simple Workbooks (" ").Close. Also, I don't want to quit the application. I don't know if the user will have other worksheets open. I also tried ActiveWorkbook.Close and that didn't seem to work either. Not sure what I am doing wrong.
View Replies!
View Related
Save Active Sheet To Another Workbook
Copy the active sheet that is open in a work book and save it to another file with the date in the name? For example Report_11-03-08.xls The only problem I see with this. How could I also make it append a letter to report if the file already exits Example saved as Report1_11-03-08.xls. Then just continue incrementing. Sub CopySave() Application.ScreenUpdating = False ActiveSheet.Copy Application.DisplayAlerts = False ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls" ActiveWorkbook.Close Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
View Replies!
View Related
Workbook Save, Make Sure Active Cell Is A1
When saving a file that has 5 sheets that extend to the right of the users screen, sometimes the left part of the report is left somewhat hidden because the last person to save it was using the part to the far right. Is there a way to move all the active cell to A1 and make sure rows A:D or so are showing?
View Replies!
View Related
Save Active Sheet As New Workbook, Formats & Values Only
I would like to link a macro to a button on the active sheet, that saves the active sheet In a New Workbook, with Format And Values Only. Preferably saved in the same location as the origonal workbook. This is my main goal. If possible I would like the new workbook to be named from a chosen cell (E19) on the active sheet with date added.
View Replies!
View Related
Search For Workbook In Specific Directory, Based On Keyword
I am trying to find code that locates a workbook (file) in a specific directory, based on a keyword and stores the workbook location in a string to be opened later. For example, find a workbook containing the word "ancaster" in it's file name (actual file name is "ancaster_summary_2009") in file path: "C:My Documents" and store the filename and path in a string called "ancasterBook"
View Replies!
View Related
Open File Macro: Same Directory As Macro Workbook
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 Replies!
View Related
File Copy Macro - With Directory Specified In Workbook Cell
I am continuing to struggle with my first attempt at a macro, and wonder if someone would mind taking a look to see where I have gone wrong? All I am trying to do is create file copying tool where the user can define the source and destination directory in workbook cells, and assign the macro to a button to complete the copy of all files from source to destination. The macros do this: Source and Location directory defined in Sub Archive1. These are passed to file copy macro Sub ArchiveScript. This macro checks for presence of destination directory, ensures that the directory backslash is in place, specfies the variables fn and fn2 to make sure the filecopy command copies all files in the directory, and then carries out the filecopy function itself. However, I am getting two errors from the code that I can't figure out (I am still very much a beginner!) - Invalid procedure call or element on fn = dir() at the bottom - The copy still works, but only seems to copy one file from the source to the destination, and not all of them.
View Replies!
View Related
Create & Save Workbook From Filtered List
I have a workbook (see attach) That has 2 worksheets. (LOAD DATA AND SKIP) What I would like to do is have VBA that automatically filters for each of the names in column A, creates a new workbook naming the workbook as the filtered name (ex D Fowler) and current date and then copy in all info based off filtered data from columns A thru Y Then save and close for each of the names in column A.
View Replies!
View Related
Active Directory Authentication Using VBA
From an Excel workbook userform, I want to capture a logon name and password, and then authenticate against Active Directory. The only information I want from the process is whether the user name/password combination is valid in AD. I know how to do all the Excel stuff (userform, etc.), and I know how to get information from AD using an administrator binding. What I am missing is being able to verify whether the Excel workbook user is the AD user that they claim to be.
View Replies!
View Related
Opening A File From Any Active Directory
The file that this code is located in is often sent to others to use which means I have to modify the directory and path each time I send this file in order for the VB code to work. Workbooks.OpenText Filename:= _ "C:My documents-2Monthend Supplemental ReportsDeficiency ReportscurrentC37.txt" Is there a way to have the file opened from any active directory it happens to reside in?
View Replies!
View Related
Create Macro To Chart Data With Location As Object In Active Sheet
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet. Sub ConsDiscChart() ActiveCell.Offset(29, 11).Range("A1").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Offset(0, -1).Range("A1:C24").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub
View Replies!
View Related
Macro To Create New Folder And Save File
I want to create a macro that will create a new folder called "Fungicide Quotes" under my documents and will save the workbook using the cell reference d4:f4 for the file name, which are merged cells. I have tried the following but can't get it to work. Any help would be appreciated, Thanks Sub Save_wrkbk() Dim strFilename, strDirname, strPathname, strDefpath As String On Error Resume Next strDirname = "Fungicide Quotes" strFilename = Range("d4:f4").Value strDefpath = "C:My Documents" If IsEmpty(Filename) Then Exit Sub MkDir strDefpath & strDirname strPathname = strDefpath & strDirname & "" & strFilename.......................
View Replies!
View Related
Macro Will Not Look At Active Workbook
I have 2 workbooks open in the same excel session. each with its own unique name. The macro looks for a specific name and then copies data from this workbook to the 2nd workbook. For some reason it no longer like the name of the workbook #1 which has not change (as far as I can tell) When I run the macro i get the run time error '9' subscript out of range, when i click on the debug this is what is highlighted and underlined.
View Replies!
View Related
Save To A Directory
1. Is it possible to save additional worksheets (right now it is only copying one worksheet)? 2. How can I hard-code the directory path so the file goes to that one locatin (i.e. the "master directory")? Also, is it possible to save userforms out of this workbook (i.e. some of the userforms contain information that is useful...it would be great to have them in the "output" file that gets saved to the "master directory"). Thanks! Private Sub SAPfile_Click() myfile = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls") Worksheets("Output-SAP Plan").Copy ActiveWorkbook.SaveAs myfile ActiveWorkbook.Close MsgBox "SAP file created." End Sub
View Replies!
View Related
Change Directory For Save As
I have a workbook that will save as if certain conditions are met. The workbook saveas filename is determined by user input into a textbox. I would like to define the path but allow the filename to be the value of the textbox. I used the ChDir function to do this but it is not working. Here's what I have: Private Sub CrmFrm1_Click() ChDir "S:AccountingProbationTest Files" If Len(TextBox1) <> 12 Then MsgBox "Incorrect Case File Number" FrmSave.TextBox1.SetFocus Exit Sub Else ActiveWorkbook.SaveAs Filename:=FrmSave.TextBox1.Value End If Unload Me End Sub
View Replies!
View Related
Set Macro For All Worksheets (active Workbook)
Sub display_all_false() With Application .DisplayGridlines = False .DisplayHeadings = False .DisplayOutline = False .DisplayZeros = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False .DisplayFormulaBar = False .DisplayStatusBar = False .ShowWindowsInTaskbar = False .CommandBars("Standard").Visible = False .CommandBars("Formatting").Visible = False .CommandBars("Control Toolbox").Visible = False .CommandBars("Drawing").Visible = False End With End Sub But, when I run the code, seems there're run-time errors detected at these codes: .DisplayGridlines = False .DisplayHeadings = False .DisplayOutline = False .DisplayZeros = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False 1. Is there any way to correct these codes? 2. Does the bolded codes applied to ActiveWindow (Worksheet) only (eg. With ActiveWindow ...)? Can I change it to ActiveWorkbook (perhaps)? 3. Can the command menus (File Edit View Insert Format ...) be hidden also? 4. Does looping method for each worksheet
View Replies!
View Related
Macro To Create New Workbook And Sheets Inside Each Workbook
I have a spreadsheet which has all the names of trips from a warehouse, the day that they operate (1,2,3 etc) and the job line allocated to each trip. It looks something like that: A B C DAYTRIPCUST I would like to create a macro that will be creating 7 new workbooks and then in those workbooks as many sheets as the trips. In these sheets, the customers should be displayed. How do I write it? I could not find how to have a "dynamic choice" in the macro. I.e. not to have the criteria as "1", "trips1" but to choose from the range of inputs that are available.
View Replies!
View Related
Check If Directory Exists Before Save
I am looking to see if a directory exists, and if it does, to save a file in it, otherwise create it, then save the file. My IsFileThere function works fine on files, but does not detect directories. The len(Dir(Filename)) statement always returns a 0, with the Dir statement always simply populating with "". It works fine on filenames - so can anyone tell me why it does not pick up directories? Shouldn't the Dir function on a directory name return "."? I have excerpted some of my code to include here: Global Const StoredJobsSubdirectory = "Jobs" Dim AppPath As String: AppPath = ActiveWorkbook.Path 'other code in here - activeworkbook changed, hence previous line If IsFileThere(AppPath & "" & StoredJobsSubdirectory) Then ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _ "" & NameString & ".xls" Else MkDir AppPath & "" & StoredJobsSubdirectory ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _ "" & NameString & ".xls" End If............................
View Replies!
View Related
Create Directory Tree
I've been trying my best to create a directory structure using VBA and Excel, and have run out of patience on how best to do this. My goal is to create a directory tree containing 2 levels. The 1st level (Main Folders) will be named from data in Column A of an Excel Spreadsheet, the 2nd level (Subfolders) will be created from data in Columns B, C and D of the spreadsheet. I've been using the following code with success, but don't know how to create subdirectories: Sub StartHere() Dim rCell As Range, rRng As Range Set rRng = Sheet1.Range("A1:A20") For Each rCell In rRng.Cells CreateFolders rCell.Value, "C: est" Next rCell End Sub Sub CreateFolders(sSubFolder As String, ByVal sBaseFolder As String) Dim sTemp As String 'Make sure the base folder is ready to have a sub folder 'tacked on to the end If Right(sBaseFolder, 1) <> "" Then sBaseFolder = sBaseFolder & "" End If............
View Replies!
View Related
Create New Workbook Based On Checkbox Selections
Could anyone please help me frame a vb code for the below explanation? I have a sheet where in some terms are provided. Users have to open this sheet and check its description. After going through all the terms, they have to select the required terms using a checkbox given beside these terms. After checking the reqd. boxes, they would click on 'Submit' at the end of the sheet. Once Submit is clicked, a new excel workbook should open up with the selected terms as various column headers.
View Replies!
View Related
Create New Workbook Based On Column Data
I was going through a thread that is open now about a similar subject: Create Workbooks & Worksheets For Each Group In Table. I tried getting it to work myself but was unsuccessful. I want a code that will create a new workbook for every new Vendor. I tried doing it myself but was getting compile errors... If this is not what I should be using let me know, it works the way I want with just worksheets, i thought it would be an easy conversion to workbooks Sub PagesByDescription() Dim rRange As Range, rCell As Range Dim wSheet As Worksheet Dim wSheetStart As Worksheet Dim strText As String Set wSheetStart = ActiveSheet wSheetStart.AutoFilterMode = False 'Set a range variable to the correct item column Set rRange = Range("A1", Range("A65536").End(xlUp)) 'Delete any sheet called "UniqueList" 'Turn off run time errors & delete alert On Error Resume Next Application.DisplayAlerts = False Worksheets("UniqueList").Delete 'Add a sheet called "UniqueList" Worksheets.Add().Name = "UniqueList".....................
View Replies!
View Related
Macro To Save A Workbook
I want to record a macro (so i can asign it to a button) that saves the workbook to a specific folder. I want the workbook to be saved under a name from a cell in the workbook. the code below saves it to the correct folder but does not save it under the name typed in cell "I5" (it saves it under "false" now) ActiveWorkbook.SaveAs Filename = "Z:ESTIMATES 2.xls" & Range("I5").Value & ".XLS" End Sub
View Replies!
View Related
Create Directory Path From Cells
This should be so easy, but I know I am missing something. I know that you can only create one directory on one statement line at a time with MkDir. in the "var_dir_path" resides a part number that changes dynamically. Private Sub CreateFolderFromCellPath2_Click()...
View Replies!
View Related
Create List Of Files In A Directory
Is it possible to create a VBA script that will list the filenames of files in a set directory? I have about 100 directories, each containing 1000 files and they need to go into an excel spreadsheet (each file on a new row)so they can be audited... ;(
View Replies!
View Related
Create Folder.... Make Directory MkDir
how can I creat a directory and then save the same file into that directory. At the moment Im using - ThisFile = Range("T3").Value ActiveWorkbook.SaveAs FileName:=ThisFile ...which works great, but before I do this, I want to create a folder with the name the name/value in T4, then ofcourse save the file into that folder ....
View Replies!
View Related
Macro To Save Workbook/File As Cell Value
I need to be able to save a workbook by running a marco and it save the file automatically by pulling what ever the value of cell A1 is. Ex: I want to run the macro and it save my excel workbook in C:Documents as (Value of Cell A1) What code would you put into Visual Basic.
View Replies!
View Related
Save Workbook As CSV Macro Code
I want is a Macro that will save the file as .csv, with the same filename and location as the original (just in csv format). A hotkey would be quite handy for this. I tried using the macro recorder and fiddled around with the code a bit but my programming knowledge is quite limited and I ended up with something that doesn't quite work as i'd like. I'm sure this would only take a few seconds to code for an experienced user, which is why I post here. I would post my attempt at doing it myself but i'm on a computer that doesn't have excel at the moment.
View Replies!
View Related
Macro: Save New Workbook After Copying Sheets To It
I am moving some sheets into a new workbook and from the new workbook I want to prompt the user to save the workbook where they see fit. For some reason I cannot seem to save the new workbook only the old with the below code. Sub Moving Dim wb As Workbook Set wb = ThisWorkbook Sheets( Array("Hk", "Li", _ "SAM")).Select Sheets("SAM").Activate Sheets(Array("Hk", "Li", _ "SAM")).Copy If wb.Saved = False Then Select Case MsgBox("Do you want to save your changes?", vbYesNo Or vbExclamation Or vbDefaultButton1, "J & R Solutions") Case vbYes wb.Close True Case vbNo wb.Close False End Select End If
View Replies!
View Related
Macro: Create A New Workbook
I've created what I thought was going to be a simple macro which will create a new workbook, rename it "Applications Calls", then copy a pivot table/report from another workbook and paste it. Here's my rather amateur code which doesn't work. Sub copyreport() Workbooks.Add ActiveWorkbook.Name = "Top Ten Apps Calls.xls" Windows("Applications Calls Logged North.xlsm").Activate Sheets("Calls Logged by Customer").Select Cells.Select Selection.Copy Windows("Top Ten Apps Calls").Activate Cells.Select ActiveSheet.Paste Range("A16").Select ActiveSheet.PivotTables("PivotTable5").PivotSelect "Silo", xlButton, True ActiveWindow.DisplayGridlines = False ActiveWorkbook.ShowPivotTableFieldList = False Range("A16").Select End Sub
View Replies!
View Related
Open Workbook, Run Macro, Save - Automate
I have 5 Excel files. The first one is Main, and the other 4 are subfiles, namely A1, A2, A3 & A4. I have a need to open Main, then click a button to start a Macro (in Main) which will do the following, sequentially: 1. open A1 2. run a Macro in A1 3. close and save A1. 4. open A2 5. run a Macro in A2 ... 12 close and save A4. The files A1..A4 are a file server and being shared. If they're being used by another user, it cannot be saved - so I would have to skip it and continue with the next A. It would be great if start Excel, open Main, and run the Macro in Main fully automatically.
View Replies!
View Related
Macro Will Not Create New Worksheet And Workbook
Im trying to create a document that creates a new workbook when certain functions are selected and have written a macro to assist with this. Basically im working on a comprehensive checklist with all possible scenarios that will need to be tailored for use, so you can create a document with just what is applicable to you and not the whole checklist. I have attached a sample of what i mean, along with clearer descriptors of what the document is designed to do.
View Replies!
View Related
Macro To Create New Workbook With Name Other Than Book1?
I'm tryign to create a macro which will copy certain worksheets into a new workbook (i.e. Book1), and the macro continues to work with this Book1. Is it possible to create a more robust macro which will rename Book1 into something usable (i.e. "Report")? because if the session of Excel has already had another worksheet, it will go to Book2, and then the macro won't work.
View Replies!
View Related
Error Handling Within Errors (macro To Create New Worksheets In The Workbook)
I am currently creating a database which involves using a macro to create new worksheets in the workbook. When the macro is run and a work sheet is being inserted, an input box asks the user for a worksheet name. As you can imagine, the worksheet does not like it when the worksheet name input by the user, is the same as one already existing and so throws up a 1004 error. In order to resolve this error I have included an error handling code to request the user to input a differnet worksheet name, as the one previously inserted exists. My problem: It all works fine until the user types in an existing worksheet name twice, so once initially and again when the error handler has prompted a second attempt. On the second incorrect input a 1004 error warning is displayed. I would like the error handler to keep repeating until a worksheet name that doesn't exist is inserted by the user. Is this possible? My code so far: Sub NewTrancheSheet() ' Sheets("Tranche Sheet Template").Visible = True Sheets("Tranche Sheet Template").Select Sheets("Tranche Sheet Template").Copy Before:=Sheets(1) Sheets("Tranche Sheet Template (2)").Select Sheets("Tranche Sheet Template (2)").Move After:=Sheets(Sheets.Count) Sheets("Tranche Sheet Template (2)").Select.................
View Replies!
View Related
To Create A Macro Based On Events
I am trying to create a macro based on events. The event should be triggered as soon as the user leaves the cell by clicking on a different cell or by using the arrows on the keyboard. So if the user type SONY in cell A1, A2-A10 will be populated right on the spot automatically based on the word Sony, for example A2 will have electronic populated automatically, A3 will have Japan...and so on If the user enter GMC instead in cell A1, A2-A10 will be populated right on the spot automatically based on the word GMC. A2 will have Automaker populated automatically, A3 will have USA, ...and so on. After I created a small VB sample it looks like I need to press the execute button every time to run the program in VB instead of triggering an event automatically. Here is a small sample I am using to test: Sub test() If Range("a1").Value = 10 Then Range("c1").Value = "Yes" Else Range("c1").Value = "No" End If End Sub
View Replies!
View Related
Macro To Create New Tabs Based On A Contract Number
I work with contracts and have to report the sales for each contract by customer number, name, address, product code or sku, sales amount, quantity, and invoice date. Each contract number is associated with a product group and within that product group are a laundry list of product codes or skus. Customers are often on different contracts for different products and may purchase some or all of the products on that contract. When I run a query from the system to extract this data, I get one sheet that contains 20,000 lines that have to then be sorted and seperated into different tabs based on the contract number. Keep in mind that a customer may be listed multiple times because each line in the query represents a product code and an invoice date. I currently have a macro that will format the reporting such that it sorts the data, however, what it doesnt do is look for each instance of a particular contract number and separate it into different tabs. So below are two examples: April and May 2008 Reports April 2008 Report Contract 1 contains 5,000 lines of data - extract into new tab Contract 2 contains 5,000 lines of data - extract into new tab Contract 3 contains 5,000 lines of data - extract into new tab Contract 4 contains 5,000 lines of data - extract into new tab Total lines = 20,000 May 2008 Report Contract 1 contains 7,500 lines of data - extract into new tab Contract 2 contains 5,500 lines of data - extract into new tab Contract 3 contains 8,000 lines of data - extract into new tab Contract 4 contains 9,000 lines of data - extract into new tab Total lines = 30,000 So for the April Example, I would like excel to take this one list and break into 4 new tabs based on the contract number and carry over all of the data into that tab. Where this may become difficult is that there may be more lines of data one month and fewer another. Im not sure if there is a function that will tell excel to stop when it reaches the last line for one contract number and to start a new tab for the next contract number. (see the month of May example)
View Replies!
View Related
|
TRACKER
