i try to write a code to pop up input box with browse button to select the file path
the code without browse button is workin corectly
Private Sub CommandButton3_Click()
Dim N As Integer
Dim Fname As String
Fname = InputBox("Please enter the file name and path to save the output", "enter file name")
Sheets( Array(Sheet2.Name, Sheet3.Name)).Copy
ActiveWorkbook.SaveAs Filename:=Fname
ActiveWorkbook.Close
End Sub
Need to add browse button to make it easier than writting the file path
I am sourcing data for my pivot table from an csv file. The ultimate task I am trying to accomplish is to allow the user upon hitting a button to select the file that would feed the pivot table. The code below is what I got by linking the initial source file to the pivot table. As I can see the source file named "4 Port.csv" is listed in the SQL-like statement. How do I go about allowing the user to pick the file from the "open file" dialog?
Code: With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) .Connection = Array(Array( _ "ODBC;DefaultDir=C:Documents and Settings2301Desktop;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferS" _
Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?
Sub Macro1() Dim fs As Object Dim oldPath As String, newPath As String oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save Set fs = CreateObject("Scripting.FileSystemObject") fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls" Set fs = Nothing End Sub
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath 'controle = Dir(pad$ + "FactuurNummer7.txt") 'If controle = "" Then GoTo EerstAanmaken 'Open pad$ + "Factuurnummer7.txt" For Input As #10 'Input #10, Nummer1 'Close #10......................
Can I use the asterisk (*) wildcard to save the file in multiple PATH as long as it the file is in the master drive C:
Ex: the file now is in "C:Public" then can I use "C:Public*" so user can save file at their desire sub-folder such as "C:PublicHenry" as long as it has to be in drive C
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...................................
I have a report send to me daily. And I want to have a macro to save this report in the daily folder, such as “c:
eports8052008”, so tomorrow 's folder would be “c:eports8062008”.
All the daily folders already exist. Just need to change the file path. I tried some codes including sPath and format(now(), “mmddyyyy”), get error message.
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
I have written a sub to open a txt file, but I want to then save it as Excel, with a filename (a variable). The variable is public, and is set by other subs. I have added the standard "save as..." button to my custom toolbar to use for this and the following code (taken from previous thread)
Public Sub SaveFile(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = True Then Application.EnableEvents = False Application.Dialogs(xlDialogSaveAs).Show MyVariable, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Cancel = True Application.EnableEvents = True
End If End Sub
But it comes up with the original filename in the save as dialogue and txt file type. Any help would be great, this is used by inexperienced users and I don't want the original file saved as it's prevoius name and type
i have this code to run autofilter in protected sheet2, assigned to a check Box, but it gives me this Msg when i run the code by checking the check Box: File in use. The file is locked for editing by the (my name). However, when i excute the code by F5 in the VBE window, it works fine also when i excute the code from Worksheet_Activate , it works fine
Sub t() With Sheet2 . Protect "0", , , , userinterfaceonly:=True, Allowautofilter:=True .AutoFilterMode = False .Range("A5:DT5").AutoFilter End With End Sub
We have an excel file which is made every thursday for comparison purposes etc. I've made an macro (excel 2003) which - on start - asks via an inputbox for the extension in date format for saving purposes. Later on, the macro will search for the same file but from last week to open it and copy/paste a 'comments' column into this new one. All went well, everybody happy. However it may be well possible that file is made and saved but with an extra extension: example:
dir to save = C:file
file when starts runs inputbox "Please enter a date (e.g. 2008-06-20)" The macro records this string as mydate:
Sub Start() Dim AnyString Dim MyStr Dim DirString Dim mynum 'As String Dim resp As Long Dim get_mynum Dim mydate
'Define extension for the file name to be saved and the correct path (dir) where this file will be stored. get_mynum: mynum = Application.InputBox("Enter the filename's extension to save in yyyy-mm-dd (e.g. 2008-06-30)", vbOKCancel) If mynum = False Then msgbox ("You do not want to continue? Ok, programm stopped") Exit Sub ElseIf mynum = "" Then..............
I have a variable ("DestFile") that defines a path to a file (used in saving the file)...
I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...
How would I go about trimming the "DestFile" address to get a "DestFldr" address?...
An example of "DestFile" might be; S:BryanFor KenGulf ConstructionST0609014-t.xls (the file name length may vary) What code can I use to consistently trim it back to; S:BryanFor KenGulf Construction as the "DestFldr" variable?
I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.
My VBA code in Excel 2000 copies a worksheet and Saves it. The default path of the file location is held in a cell. The user defines this path through a userform. The userform simply has a text box and the user types in the path. This value is then transferred to the mentioned cell. What I want is for the user to be able to pick a directory from the user form rather than having to type it.
I have a script that runs through all the workbooks in a folder However the path is hard coded. What I would like to do is use part of the hard code path but have the user pick the last folder that contains the files the script would run on. I tried to use the application getopenfile but that only picks the file itself not the folder.
I am using this code to import excel files into one workbook..I have the path hard coded. Is there a way to get this automatically so if another person is running on there machine it will work..
OR
can you define the path in like cell A1 and have it pull from there?
Sub GetSheets() Path = "C:UserswharnedDownloads" Filename = Dir(Path & "*.xls") Do While Filename "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub
I am writing a macro that will perform a loop operation to export files. I want the user to specify the path, and the macro will save each exported file to that folder.
I did a number of searches and I can't find exactly what I'm looking for.
I think I want to use ChDir after using CurDir. But I don't know how to code the macro so that it asks the user to specify the location.
I have the following code, taken from an example off the web. But I would like to change the save path to just C/: rather than copy the save path of the original file...I would also like to not close the active workbook that I am working in....how can i achieve this?
Sub TwoSheetsAndYourOut() Dim NewName As String Dim nm As Name Dim ws As Worksheet
If MsgBox("Copy specific sheets to a new workbook" & vbCr & _ "New sheets will be pasted as values, named ranges removed" _ , vbYesNo, "NewCopy") = vbNo Then Exit Sub
With Application .ScreenUpdating = False
On Error GoTo ErrCatcher Sheets(Array("Copy Me", "Copy Me2")).Copy On Error GoTo 0 For Each ws In ActiveWorkbook.Worksheets ws.Cells.Copy ws.[A1].PasteSpecial Paste:=xlValues....................
I am creating a training records matrix which will be used by 30 or so locations. each one may want to save in a different location. I would like to save the chosen location as an entry in a cell so this can be accessed by other macros.
I want a Macro which will copy my workbook and paste it into a new workbook. Then it will automatically save it under the SAME name as the original and the SaveAs Box must show up (for cosmetic fixes). It is important it asks if it wants to save it under the same name. Example: My file is called "Summary_2008" and when I hit the Macro it will do those things and it will ask me if I want to save it in the SAME directory under the name "Summary_2008" (I will only add a number then).
(The macro must know how to read the original name since I have 7 possible filenames - it depends on which company I choose, that is why it is important it is saved under the same name)
Sub Zamenjaj() Worksheets.Copy Dim WS As Worksheet For Each WS In Worksheets Zadnja_vrstica = WS.UsedRange.Rows.Count Zadnji_stolpec = WS.UsedRange.Columns.Count For rwIndex = 1 To Zadnja_vrstica For colIndex = 1 To Zadnji_stolpec With WS.Cells(rwIndex, colIndex) If WS.Cells(rwIndex, colIndex).HasFormula Then..........................
Save my workbook Then Open the SaveAs box with the path already pointing to a specific directory and with the Name and file format already entered (.xls)
This is basically to save time navigating through our folder structure (which is a real mess). I don't want to automatically saveas the file as the target folder will change each month and the last part of the file name will chance (month). So I'd like a bit of flexibility rather than total automation.
The macro is working fine but what I want to change is the path for saving the file. The path that I would like it saved to is in the worksheet "NEW ORDER_" cell "N2"
Sub RENAME2() Dim strWbKill As String Dim myName As String myName = ThisWorkbook.Path & Application.PathSeparator 'copy to same folder that file is in myName = myName & Application.Cells(3, 7) & "_" 'cell G3 Style
I am using the below macro to save my workbook with in the correct path and naming convention.
In fact, as I also need a pdf file from that excel file, I tried to change the below macro and replace ".xlsm" by ".pdf"... However acrobat does not like it..
HTML Code:Â
Sub PDF() 'created by Gregory Charbonnel 'ActiveWorkbook.SaveAs Filename:="Z:FOLDER & FILE MANAGEMENTREview (ETF)PDF ETFETF_" & Format(Now(), "dd_mm_yyyy") & ".pdf", _ ' FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False End Sub
I guess that I should use the print option which gives me that when I record the macro. how to implement my naming convention + path in that macro.
