Creating Multiple File Picker
Mar 6, 2014
I am trying to create a multiple file picker. The code is below.
I want to open the first file of lets say 5 selected files, do some autofilter task, copy data from that file, paste it in another workbook and close it. Open second file, do some autofilter task, copy data from that file, paste it in below the first file pasted data in another workbook and close it... and File 3... and so on.
It works for one file, the code for copying pasting is not yet added. I think I can manage that. But how I return to the file picker function after it opens the first file.
View 1 Replies
ADVERTISEMENT
Nov 27, 2007
I am using the following code to allow the user to select files to open. I have realised that you can create multiple filters with the msoFileDialogFilePicker command, which is very useful, since the program initalliy takes multiple text files, loads them into a workbook as seperate sheets and then saves the workbook as an excel file. Therefore I would like the user to be able to select the text files to load but also to be able to check if the corresponding excel file already exists and if so to be able to directly open this rather than have to create a new file from the text files. Therefore my question is: Is there a way to be able to tell which filter was in use when the file was selected?
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = True
.Filters.Clear
.Filters.Add "Excel Files", "*.xls"
.Filters.Add "Text Files", "*.txt"
.Show
' olWin.Activate
A = 1 ...........
View 2 Replies
View Related
Feb 7, 2008
I would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.
The daily file that i use has info like date, truck number, delivery stops, weight.
the database file has the similar headings.
at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.
This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine
View 9 Replies
View Related
Mar 2, 2010
I've got numerous vba-coded spreadsheets in use with lots of different users, and have found that some users try to hide errors of their own making by blaming the spreadsheets (I know - what is the world coming to!)
I've decided to add a script which creates a log file each time one of my spreadsheets is used - opening a log which relates to the user's spreadsheet and recording username, date/time, path & file, and the vba being used.
I'll probably add an "on error" script to save copies of all open workbooks too.
The issue I have at the moment is how to record the name of the macro being run?
Here's my code so far: ....
View 9 Replies
View Related
May 5, 2009
I have a text document to which i need to do some changes and then turn it into CSV. Here are some lines of what the document looks like and below i will show you what i need it to look like. So i open the document with Excel and next I'm not sure how to do what i will show you below. Original:
HTML 111text
112text
113text
Here is how i need it:
HTML 1,1,1,"text"
1,1,2,"text"
1,1,3,"text"
View 5 Replies
View Related
Nov 12, 2007
I need a little help creating the macro for a template file which is going to be used for data input and further analysis. I have a set of arrays, which can be altered. The arrays may look like so:
View 9 Replies
View Related
Jan 29, 2009
I have some code that looks at the part # in column D and then goes out to the D drive to drawings (.pdf files) and creates a hyperlink.
The problem is that it is only creating a link to the .pdf file and not to folders. I need a hyperlink to the folder so when it is clicked the open folder pops up and shows the multiple files associated with that part.
Link should be the same regardless of file or folder, no? As long as the name matches link should be made, correct?
E.g.
123-100.pdf (column D part #) links to 123-100.pdf file on CD
456-200 (folder name in column D) no link created to folder named 456-200 on CD.
Sub Hyper()
MyPath = "D:"
StartRow = 2
EndRow = 20
x = 0
For i = StartRow To EndRow
Cells(i, 4).Select
MyFileName = ""
MyFileName = Dir(MyPath & Cells(i, 4).Text, vbNormal)
If MyFileName "" Then
x = x + 1
Cells(i, 4).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="D:" & MyFileName
End If
Next i
End Sub
View 9 Replies
View Related
Oct 6, 2006
each row in sheet1 contains variables that, when applied to cells in sheet2, calculate a final figure. I have written code that selects each row, distributes its variables in sheet2, and shows the result back in sheet1, then selects the next row, and so on until all rows have been calculated.
The calculation is complex, and I would like to create a text file that stores the calculation for each row in the same text file. The calculation is a 9 columns by 33 rows, and I would like to preserve this shape in the text file, though its OK not to have columns not lining up.
There is a similar problem on an old thread but this requires only one iteration of "open file, write values, close file". I need "open file, write values, write more value, close file".
View 4 Replies
View Related
Mar 2, 2007
I need to distribute it to several areas of my company. Each area only needs part of the report. Therefore I have written some code which splits the existing report by area and creates a brand new report with that smaller chunk of data in it.
However I now realize that the new sub-report will require some code of it's own in order to function. My problem is that only the sub-report will be distributed to the end user. The core report will not. Therefore any code written for the new sub-report must be included within the sub-report.
View 4 Replies
View Related
Apr 2, 2007
I have many users of the same spreadsheet on different PCs writing .txt files to a common directory on a central server. I want to be able to create a uniquely named .txt file, using a set naming convention (datetimeotherthingsetc.txt) each time they execute my export code routine. Is there a way I can create a txt file from vba code? I have figured out how to write into an existing .txt file but would like to know if I can create a .txt file from scratch from vba code? ps to avoid any confusion I dont want to save (ie save as) the open spreadsheet to a .txt file
View 4 Replies
View Related
Apr 23, 2014
Just striving to have this kind of "picture arrangement's macro" to work, macro that whenever you choose a certain cell and operate it on hotkey it'll:
1. Create a new folder named "PicturesAuto" beside the excel file (wherever the excel file located).
2. Inside folder "picturesAuto it'll create a new subfolder named by the same name of the sheet related to that cell you operated the macro upon. (let's say this cell placed on sheet named: "happysheet"- that'll be the name of the subfolder created.)
3. Inside the subfolder "happysheet" it'll create a new subfolder named by the text of the cell you activated the macro upon (let's say you initially operated the macro on B5 cell -which has the text "montana" so it'll create "montana" subfolder also inside subfolder "happysheet")
4. Lastly instead of having only the text "montana" in "B5 cell" like we had initially before activating the macro, the macro'll also add to that text "montana" the link to "montana" folder (path of excel file->picturesAuto->happysheet->montana)
The way I'm planning to use this is to add a lot of HR pictures inside those folders created by the macro that related to that specific sheet, and that specific cell I've activated the macro upon. Plus just having those pictures so organized is just outstanding in every scale, and the excel file will keep running smoothly without any resource consuming overload.
View 7 Replies
View Related
May 27, 2009
I am looking to have these workbooks save automatically to a folder on the C: drive.
If the folder I specify is not available on the C: drive, how do I write teh save comand to create it automatically with a prompt
View 8 Replies
View Related
Dec 18, 2012
I'm trying to check to see if a file exists using VBA but the routines I see use a string that has the path and then the file name. I have a fixed path C:VBtesting and the a variable file name. I'm using a file name that consists of a username & date such as cwilliams201212. I'm having problems getting the variable file name into the string.
View 1 Replies
View Related
Jun 12, 2008
I have been tasked by the company I work for to come up with a simple to use interface that can be used by someone with little understanding of excel to set the autofilters to certain settings.
So here is what I have:
2 sheets (each with the same number of columns but different data which corresponds to good and no-good)
Each column has an autofilter but only two on each sheet need to be set.
One filter controls valve type (of which there are 10) and the other controls running condition (startup and normal)
Now my employer wants something that is basically idiot proof so that someone can simply open up the excel file, input the valve type and running condition, and be rewarded with only the corresponding data and charts. Now the filters are already in place, the charts already exist and the only thing left is the user interface.
View 14 Replies
View Related
Sep 23, 2009
Is there a way to create a comma delimitted .txt file from Excel? Or a semicolon delimitted file?
View 3 Replies
View Related
Apr 10, 2013
I am working on a research project of which details cannot be disclosed. I basically have many worksheets which each contain 3D positional data and I am plotting overlaid graphs using multiple worksheets. I have gotten them to work where I manually enter the names of the worksheets, but I am trying to use the CELL("filename") command to get the current worksheet's name, then using a substitute command to change certain fields, then I would like to use the string name created by those functions to call a global variable in another worksheet which defines a range. I will try to give an example.
Say I have 3 worksheets for arbitrary measurements:
1) filename 1mm
2) filename 2mm
3) filename 3mm
I would like to graph all 3 sets of data in worksheet "filename 1mm", using the fact that everything in the filenames are the same except for the 1/2/3mm part. I have a template of which I will be copy/pasting data from numerous data sets and then each worksheet has defined names "X_vals" and "Y_vals" which give me dynamic ranges for the data I wish to plot from that worksheet.
When I type the names manually--i.e. " 'filename 2mm'!X_vals" everything works fine, but when I try to create the exact same string dynamically using indirect/substitute/left/right etc, it does not let me do it. I am able to create a dynamic string with a range, such as " 'filename 2mm'!C10:C100" but then when I change it to " 'filename 2mm!X_vals" it just gives me #REF!.
I guess my question is just is there any way to use the indirect function to create a string name dynamically which references a defined name in another worksheet? If not possible with just simple excel functions, is there a way to do this with a macro? I am not exactly familiar with VBA in excel, though I know how to run macros.
View 3 Replies
View Related
Sep 10, 2009
What I have is a double quoted, comma delimited text file that is created from an external application, every now and again we need to open the file to make corrections and this is easier if we open it via Excel using the delimiter options, the problem is when saving the file as a csv it removes the double quotes, however I have found some vba coding that adds the double quotes back, which works great, but it changes the format of the date fields from what the original file had from 09-Aug-2009 to 09/08/2009.
I suspect this may be because the file is opened before the double quotes are added and excel changes the format.
Is there any code I can add that will stop the date format from changing ...
View 12 Replies
View Related
Mar 27, 2014
I have a sheet that has 5 columns as shown below, xxx represents the values within the cell that I need in each sql file.
A |B | C| D| E
test1|xxx|xxx|xxx|xxx|
test2|xxx|xxx|xxx|xxx|
test3|xxx|xxx|xxx|xxx|
test4|xxx|xxx|xxx|xxx|
test5|xxx|xxx|xxx|xxx|
What I am trying to do is take the context of each cell starting with B2, and place it in an Individual sql file (or text file with .sql extension) and name it A2 (so test1 in this case).
I tried creating a double for loop, one for last column, one for last row, taking each content and placing in file but I am not sure how to take the column A as the name of the file and to have 1 file per cell.
View 9 Replies
View Related
Jun 17, 2014
I have a piece of code that runs and creates an output Excel sheet that currently has the run date as a suffix for the automatically generated file name. However, I now sometimes have to run this report multiple times in one day, which currently means overwriting the previously run spreadsheet (whereas I would like to keep both versions of the ruin output).
Is there a way that I can create some kind of counter for the number of times the report is run and add that as a suffix for the file name? At the moment the only way I can think to do this is to set up code that looks at the file name containing the date field and adds 1 to every number that is added as a suffix, but this doesn't seem very efficient...
View 2 Replies
View Related
Jan 24, 2008
Create macro for automated import of a .txt file ...
View 10 Replies
View Related
Dec 5, 2011
I've made a macro that would create some folders on a shared drive.
there is an issue if the path i want to create is too long.
I get this error.
Uploaded with ImageShack.us
is there anything we can do to pass this or any solution for it?
View 5 Replies
View Related
Aug 22, 2013
coding mandatory field in Excel 2010.
This is a code that i have for having a master workbook, and saving it in different folder so my employee cant access it
Sub NextInvoice()
Range("J2").Value = Range("J2").Value + 1
Range("E6:E9,H9,J9,B14:K20,H4,B28:K32,B36:B39,D36:D39,F36:F39,B42:K43,B46:K47,B50:K51,B54:K55,B58:K59,B62:K63,B66:K67,B70:K71,B 74:K75,D78:E78").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy Invoice to a new workbook
[code].....
I have found a code for mandatory cell, but it creates a second macro and i was not able to link the two.The mandatory field has drop down of employee's and it is located in the cells D78:E78.
View 1 Replies
View Related
Jun 12, 2008
I'm trying take a reference from some workbooks (e.g. WB1.xls,WB2.xls,WB3.xls,...etc)
They are all placed in one folder but there filename is subject to change,
I need to get the result of Cells A1 from WB1.xls+A1 from WB2.xls + A1 from WB3...etc
to shown on AA1 in another workbook(e.g. Collected.XLS)
Is this possible knowing that workbooks filenames are changeable?
Moreover Can the value of A1 from any new workbook added to the same folder be added automatically?...
Can the formula typed in AA1 from Collected.xls be draged down?(So as to show the sum of Cells B1 and C1.....etc) from each workbook?
View 9 Replies
View Related
Aug 5, 2005
I have 7 different time pickers on a userform. They work great...they are passing the time to the correct cells. The problem is that if I close the userform and then open it again, the time value goes back to 12:00:00 AM, leaving the proper time in the cell. I'm trying to change the value of the time picker to the value in the cell when I initialize the userform using the following:
If Range("IncidentTime").Value < "12:00:00 AM" Then
dtpIncident_Time.Value = Range("IncidentTime").Value
End If
When I run it, I get a Run-Time Error '35788, An error occurred in a call to the Windows Date and Time Picker control.
View 9 Replies
View Related
Sep 4, 2009
I have a userform containing a date picker control. I am trying to get the date that is selected on to a woksheet called " Bank Holiday's" in cell O21.I have placed the following code in to a button used to close the form.
Private Sub CommandButton1_Click()
Sheets("Bank Holiday's").Select
Range("O21").Select
Range("O21") = UserForm17.DTPicker1.Value
Unload Me
End Sub
View 9 Replies
View Related
Feb 13, 2008
This is a sample of the data with which I am working. I know that a macro can do what I need, but I am only versed in Excel formulas and not that much programming. I need to be able to first sort the data by Column C ("Element Type"), then by Columns E, F, G ("Year", "Month", "Day"). Then, I need to be able to copy all rows that have the same "Element Type" and "Year" to a new file, using the same header from the original spreadsheet on each new spreadsheet - doing this multiple times until the end of the file is reached.
Ideally, the new files would have a strict naming convention: XXXXXX-ZZZZ (YYYY).xls, where the X's are the value of the "COOP Station ID" in Column A, the Z's are the "Element Type" from Column C, and the Y's are the "Year" from Column E. If this theoretical macro were run with the Sample Data file I provided, it should result in the creation of five new workbooks. Is there a way to write a macro to do this, or at least something similar
View 3 Replies
View Related
Mar 16, 2007
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
View 9 Replies
View Related
Oct 16, 2008
Is there an easy way to get a date picker on a cell, example i want a date picker in cell C4. I know there is a code you write to get this to work and create some kinda user form, but just wondering if there is an easier way.
View 3 Replies
View Related
Aug 24, 2009
On the attachment i have a userform which pops up when cell B2 is selected. It is a month view from the additional controls on the control toolbox. I've also added a couple of text boxes with spin buttons.
A few amendments i want to make to it are:
User form pops up next cell B2
'Enter button' enters date and time into cell B2
Spin buttons increase/decrease hour field in incremnts of 1 and minutes field in increments of 5
View 14 Replies
View Related
Aug 13, 2003
when a date must be selected, you can do it through a button that opens up a little calendar page on which you just click on the date selected.
Is there anything similar in Excel Office XP, or is this asking too much??
View 9 Replies
View Related
