The code below opens a window which allows the user to select a file to open. Apparently, it starts in the directory of the file containing the macro. How would I modify this to open in "M:Archived PO ResponsesProcessed"?
View 3 RepliesIf a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View RelatedI Want make a code to open (OPEN File Dialog)
and it works good I can't change the Diroctory!
Sub OpenFile()
fileToOpen = Application. GetOpenFilename("Execl Files ( *.xls), *.xls")
ChDir "C:" ' Not work good
If fileToOpen <> False Then
Workbooks.Open Filename:=fileToOpen
Else
Exit Sub
End If
End Sub
check this code.
I am writing code that requires data from another workbook. Instead of hardcoding the directory and filename (which changes daily), how can I have the code prompt the user to do this for me?
I want to open the file with:
links updated
read-only
Also, is there a way to have an initial directory that appears so the user need not go through a dozen directories every time to get to the file?
I have code that prompts the user to open a specific excel file. However, with the following:
Filepath = "..."
name = Application.GetOpenFilename(FileFilter:="Excel Files *.xls* (*.xls*),",)
Workbooks.Open name
the default directory you start out in in the window that prompts the user to select a file is NOT ...
How can I properly assign which directory the user starts in so they need not go through 10 sub-directories everytime they run this code?
We have several salespeople who maintain a workbook tracking their workflow. I want to compile the customer account number, account name and deal number for each rep to another workbook. A new sourcebook is started each week. This code is in the target book
'Option Explicit
Public sourcebook As Object 'the workflow workbook
Sub GETDATA()
Dim wsc As Double 'the number of pages in the workflow
Dim col As Long 'the column in this workbook for returning values
Dim IntEndRow As Double 'the end row in this book
Dim SrcEndRow As Double 'the end row in workflow
Dim findval 'the column workflow where the sourcedata is found
My code allows the user to select a workbook and then loops through each sheet in that book searching for the column which has the info I need, this changes on each sheet as the reps each deal with different types of customer as so take different details. I solved this by having column headers in the target book and searching for them in the sourcebook.
This code works when I step through it but falls over at the "Set sourcebook" line when I run it where it returns a breakpoint error.
I want to open a user defined file name in excel and then activate it , to run a macro on it.
View 3 Replies View RelatedI'm trying to write a macro that will bring up the "File Open" dialog box, and allow the user to navigate to the directory where the file is located. It won't be the same file every time, and the files will likely have different names.
View 14 Replies View RelatedI have code (below) that works if the range "ImportFile2" contains a valid value but I want users to be able to select any .csv file preferably from a directory which should be held in a cell.
I added ...
I have a program that opens an excel-workbook, I first check whether the file is opened by another user(open for read-write). This works fine, but I'd like to know which user has the file open with VBa code. ex.
workbooks.open ....
if open
then msgbox " Book opened by user"
end if
So it's the same as you open an excel(with your windows explorer) on a network and you get the message that the file is already opened by the user....
I am trying to open a folder so I may choose the workbook to open. I am able to open a workbook but I want to open the folder which contains the workbook.
View 6 Replies View RelatedI have a workbook were a user will write down some info. Then I need to copy that data to a second workbook located anywere in the Hard drive. i used the function "GetOpenFilename" or the "Application.dialogs(xlopenfile) to ask the user where is the file (and open it). Now I need to get the name of that file. How can I get it?(the name of the file the user selected to open). P.S. I saw there is somewhere a "Print" function to copy data from a workbook to another closed workbook. I was planning to have both workbooks open and then just use several lines like:
View 4 Replies View RelatedI use the vba to open excel file. like workbooks.open filename:=abc.xlsm but I found that if the file is opened by other. Excel still open the file but it will not notify user that it is opened as read only.
Would you add more line to notify user if the file is opened by other?
I have developed a product that's a bunch of Excel spreadsheets strung together. Real estate and mortgage professionals use the tools to counsel home buyers. We include a pdf converter in case recipients of emailed file attachments don't have Excel.
We provide an 'Export' procedure that calls a custom dll that uses the outgoing mail server that the user [must] setup or our web's mail server as a default. The action attaches the Excel or the PDF file and sends in the background. We use an Excel form for user to write the message and the process saves a copy of the message sent and the file send in an Archives folder.
This procedure is not very user friendly, as it does not offer the user's address book to select a recipient, does not put the message in the user's email client Sent folder...Plus the Archives folder could become quite large...plus it involves a user setup that creates alot of support calls.
But actually the most critical shortcoming has become that ISPs (Comcast and ATT so far) are blocking the use of custom email clients!
Recently my son showed me in QuickBooks their attach file-to-email process. It simply opens the user's default email client with the file attached. I need an app that does that! And it needs to be distributable.
I am making a macro that requires that user to enter a directory path into a input box and the macro will open the file. Right now it works fine bit i would like to change the code so that instead of using an input box it brings up a box that will allow them to browse files on their computer and then select one to open.
View 4 Replies View RelatedIts been a while since I did this on 2003 and needing a pointer on 2007.
Just needing a simple macro that opens the relevant folder from a hardcoded path to allow the user to manually select the file to be used.
This file will then be used to copy from and paste to another file.
I have multiple files in one directory. I have a master sheet that gives me basic information about all those files. In column K it list's all the file names and file paths. I would like to be able to use the auto filter to sort these files. Once the files are sorted they are opened and the relevant information (the CHRD page) is then copied into the master (Query.xls) the first copied page is renamed to sheet1 and second to sheet2 etc. Right now i have to manually run the macro's depending on which line the files are listed. I have also had to create separate macro's for each line.
View 2 Replies View RelatedI have a series of cascading comboboxes on a sheet named report. In the first combo box,cboCategory, the user will have the option of selecting New, Existing, 401k, CPA Information. If the user selects CPA Information I need a user form named CPA to open.
View 4 Replies View RelatedI would like to be able to limit a drop down list's contents based on a user's selection in a different drop down. I am using Excel 2003, and I know how to do this in Access, but I can't figure it out in Excel. Basically, I have a list of themes in one drop down. The user picks a theme, and goes to a second drop down which is a list of sub-themes attributable to that one theme and no other theme.
View 1 Replies View RelatedI have a main worksheet that has two combo boxes. The first combo box has a list of machines. The second combo box brings up a list of models for the type of machine selected. I would like to insert a command button, that when pressed will open a different worksheet with the specs of the model selected in the second combo box. I am having trouble finding a starting point for the code to have the command button perform this.
View 6 Replies View RelatedI need to open an excel file based on a date mentioned in the file name, but minus 1 day. The file name is composed with Statistics_date_time.xls
Example: statistics_20140423_142754.xls
Once I need to open the file (only in the mornings), it has to be the one from the previous day to see the yesterdays performance. How can I create a macro that will look at the date in the file and take the one from yesterday?
This will be a part of a macro I'm making. The rest of the macro will just format the file and combine a few ones from different departments.
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
I need a code to open a file automatically based on file name, for example if the active file name is "trial", then it need to open another file named "trial1" based on which the macro will run, because I have two set of files and I want to compere daily with two users. for example the file names in Set a- iron, cement, stone, sand where as the set b-iron1, cement1, stone1, sand1
View 1 Replies View RelatedI am using the following code to open an existing file whose name is in cell A1. I want to modify the code so that if the spreadsheet does not exist, a template (Costing.xlt), in the same director, is opened and then saved with the name in cell A1.
Workbooks.Open Filename:="C:Spreadsheets" & Range("A1") & ".xls"
How to open files based criteria in cells value?
[Code] .....
is there a way using VBA code that you can have a file path entered into cell 'A1'
and then when you run the macro, the file referenced in the file path in cell 'A1' will open,
I want to create a Macro to open a file based on the data of a particular cell to update the data for macro file from that file.
For example I have 3files named Dept1, Dept2, Dept3 and I have one more file named Cost Sheet which contained macro and based on the cell D2 of my macro file I need to open the particular file i.e Dept1, Dept2 or Dept3 and the updated procedure and cell ranges are same for all file
I have the current code that opens our raw data file and copys it into my workbook. However it now turns out the file is saved everyday as a new file with todays date on the end. Here is my current code:
Sub CopyRaw()
Application.DisplayAlerts = False
Sheets("PROD_SELECTION").Visible = True
Sheets("PROD_SELECTION").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
[Code] .....
Todays file is now in the name "I:SalesSales and MarketingMarketing List Product Selection Details (20140416)"
I know i can do this somehow using the Date function but the closest I have got is using
"YEAR(NOW())&RIGHT(MONTH(NOW())+100,2)&DAY(NOW())"
To generate todays date like so: "20140416" but I dont know how to get that into my code correctly so i open the file with that on the end.
I have files saved as a code, then a customer name. I'm trying to write some more code to open the file only based on the initial code.
For example the file name is X090000 TEST
This doesn't work;
Open("V:\\\" & oldFileName & "*" _
& ".doc")
I would like to run a macro that is going to open a file (Test.xls) Simple so far....
However, this file is 1. stored every month in a different folder (eg: 200709,200710, etc..) and the file is named according to the month (eg: Test09.xls, Test10.xls, etc...)
In my excel book, I did created two cells which I already use to save a document according to those cells. One cell with Acct Period (200710) and another one with the month (10).
I have tried the following formula with no success:
OpenWorkbook "\pathpathpath" & Range("Accout Period") & "Test" & Range("Month") & ".xls"