Filling A Worksheet With Data From Many External Files
Sep 11, 2009
I'd be very grateful if s.o. help me find solution for the following task:
I want to fill certain worksheet with data from external .tsv files. The .tsv files are with a almost a common name (something_date.tsv), located in folders for each month.
What I'm usually doing and want to automate:
- I'm opening the first .tsv file from the monthly folder;
- Creating AutoFilter on the first row;
- Selecting a custom criteria from the AutoFilter;
- Copying the cells matching this AutoFilter criteria;
- Pasting in a predefined worksheet (with AutoFilter on first row);
*All of the copied cells are not being altered in the predefined worksheet, i.e. the first rows of the .tsv file and the worksheet are the same.
- Doing exactly the same with the next file in the monthly folder (but pasting below the already copied cells in the workbook).
- Etc.;
- Etc.;
View 14 Replies
ADVERTISEMENT
Apr 28, 2003
I have 12 external files (one per month) and need a quick way to pull these into a reporting book. I've used the following code...
Workbooks.Open FileName:= _
"J:ManfinMISNew ReportingMIS2P200301.xls"
Columns("A:B").Select
Selection.Copy
Windows("MainReportingBook.xls").Activate
Sheets("P200301").Select
Range("A1").Select
ActiveSheet.Paste
Windows("P200301.xls").Activate
Call OpenClipboard(0&): Call EmptyClipboard: Call CloseClipboard
ActiveWindow.Close
However with 10000++ rows of data in each file, this is very very slow (approx 5 mins to update all 12 months).
View 9 Replies
View Related
Apr 13, 2009
to fix a .xls file that wasn't created by me.
In order to do that I need to change the path of an External Data that is an access 2003 file (.mdb file).
The only way that I imagine it is possible is to select a cell that is an adress for the query result, than click on properties and change it selecting the new path of file (the path moves depending on the user of the .xls).
View 9 Replies
View Related
Nov 21, 2006
I'm attempting to make a simple userform that inputs data onto an existing worksheet. I have the userform but would like to use a combo box to choose a "category" item of data, however I dont know what the categories are! I would like the combo box to, somehow, look at the spreadsheet and read off the already entered categories and offer those as choices.
A picture is worth a thousand words:
http://www.copestake.org/images/excel.png
Is there a simple way to fill the combo box (using the form initialize I assume) with the existing categories?
[Edited to link to image instead of displaying on board~admin]
View 9 Replies
View Related
Jan 31, 2013
Every month I have to do a report that takes information from four other excel spreadsheets. The spreadsheets are all the same format and contain information on community events. I have to run a report that shows what events are happening in the following month. Currently I copy and past from all the relevant data from each spreadsheet into one. Is there and easy way of making all this information to come together every month.
View 4 Replies
View Related
Mar 20, 2014
I have a combo-box in a Multipage control. Now I want to get the combo-box populated with list in 2 columns from 2 different consecutive columns from the same worksheet (Product List). Now, Once the list gets populated, user will be selecting his choice from the list, which is then needs to get copied to another worksheet (Order Placed). Both worksheets exist in the same or one spreadsheet.
I am trying to make it work with the following code. I am able to see 2 columns but with no data getting populated. I am working in MS Excel 2007.
[Code] .....
View 3 Replies
View Related
Oct 30, 2009
if it is possible to have one xls file load all .xls files that are inside a folder or a folder with subfolders and so on?
View 9 Replies
View Related
Oct 31, 2007
On the first worksheet of my workbook I have a list of file names in cells I11:I27. The filenames have been 'compiled' using the concatenate function, so for example, although cell I11 displays:
Richard 2007-09.xls,
the cell contents are really:
=CONCATENATE(G20," ",L9,"-",J9,".xls")
Using VBA, I want to be able to open each respective file in cells I11:I27, copy the contents into this workbook (sheet=raw), and close it. My problem is that I don't know how to tell excel the filename in VBA, because the cells contents are not really the filename - they are a formula.
View 9 Replies
View Related
Dec 6, 2006
I want to link data between two files, which are always in the same position relative to each other, but can be copied to other locations. Here is an example:
D:Job 1SourceJob Info.xls
D:Job 1SlaveClient Form.xls
Cell B1 in Client Form.xls refers to Cell A1 in Job Info.xls. This link is created when both files are open. When Job Info.xls is closed, the reference in Client Form changes to 'D:Job 1Source[Job Info.xls]Sheet1'!$A$1.
Then, I copy and save Job 1 as Job 2. Thus, the second set of folders are: ....
View 7 Replies
View Related
May 5, 2014
I have a drawing index in excel sheet as shown below "there are thousands rows but here is only an example":
FILEID
TITLE
brd213962-1-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-2-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-3-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-4-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-5-1.pdf
SPECIAL PIPE SUPPORT DRAWING
And in the same folder I have all the drawings in PDF format and all the drawing are exactly named as listed under FILEID column.
I just want to click the drawing number in the excel sheet and get the drawing opened automatically.
Currently what I am doing is searching the index for a particular drawing and then searching the folder for that drawing to open.
View 1 Replies
View Related
Aug 26, 2013
I have a list of file locations of .pdf files in column A of my spreadsheet. All the path and files have info on Sheet1.
i.e. Column A1 c: estpacking_1.pdf A2 c: estcountry_1.pdf
Can I use VBA to, when I run it, print all of the files in the list in column A1:D20?
View 1 Replies
View Related
Jul 23, 2013
I have the following code which edits an excel file and saves it in a different format, I think want the code to take this newly saved file and open it in an external application (Softplot) and then save it through this new application.
VB:
Sub FormatMacro1a(ws As Worksheet)
ws.Copy
Rows(1).Delete
Columns("D:E").Cut Destination:=Columns("J:K")
Columns("F:K").Cut Destination:=Columns("D:I")
Range("E1:E201").Value = "0"
[Code] .....
As it stands I have the file saving in a new format and I can open a specific file in softplot through VBA however where I am getting stuck is opening my newly saved file and then saving it.
I have tried the following :
VB:
Path = "C:Program FilesSoftPlot-8softplot.exe"
File = "ActiveWorkbook"
View 3 Replies
View Related
Mar 7, 2014
I am trying to put this in about 25 cells to point at 25 different files. Basically I am trying to keep an eye on when the files have been updated each morning so that I can then pull off some data from them and who saved it.
I solved the first part (see next post) but I still havent been able to get it a function pull off the username.
View 1 Replies
View Related
Nov 12, 2009
im filling out a worksheet.
on the rows i have this data ex. (123467.00) is there a way i can format the cell so the final product is this ex. (1234.67).
i've tried may things but what i get is (123467)
i have multiple worksheets like this just want something that can do automatically instead of manually.
View 9 Replies
View Related
Oct 4, 2012
Need macro to search xls files in folder/directory for common text string "see reference" and then output the file number which is located in cell A1 to new spreadsheet for each file the text "see reference" is found.
View 6 Replies
View Related
Jul 13, 2006
I'm using the following code to import thousands of html files into my spreadsheet. The code is working fine. Since I am importing thousands of files, when there is no more space on my worksheet, the code stops with an error message. I want to make this code add another worksheet & continue importing the html files until there are no more files to import.
Sub Master_Importer()
Dim I As Long
Dim strFilename As String
Dim strPath As String
strPath = "file:///C:/Documents and Settings/c/Desktop/New Folder/"
With Application.FileSearch
.LookIn = "C:Documents and SettingscDesktopNew Folder"
.FileType = msoFileTypeAllFiles
.Execute
For I = 1 To .FoundFiles.Count
strFilename = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "") + 1)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & strPath & strFilename _ .......................
View 5 Replies
View Related
Aug 14, 2014
I have the following:
[C1] My dynamic email subject
[C5:C10] My dynamic email body message
My recipient is always same "burak@burak.com"
Now, I need a VBA code, that will automatically open a blank new outlook email window fill the fixed recepient fill the dynamic subject and body of the message from a range in my workbook.
Then just stop there so that I can attach some different files each time and send myself.
View 5 Replies
View Related
Aug 20, 2008
I want to be able to import an external worksheet (say sheet1) to some data structure such as a 2d array or a dictionary. I don't want to store imported data temporarily in a local worksheet - I want to import external worksheet straight to array!
I have code that can import to a given worksheet name ... but I don't know how I can modify it such that it dumps it to an array or a dictionary.
The code I have is:
Sub read_in_workbook(sheet_name As String, source_dir As String, file_name As String, Optional append_from_y As Integer)
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim stCon As String, stSQL As String
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFolder As Scripting.Folder
Set fsoObj = New Scripting.FileSystemObject
Set fsoFolder = fsoObj.GetFolder(source_dir)
If append_from_y = 0 Then append_from_y = 1
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fsoFolder & "" & file_name & ";" & _
"Extended Properties='Excel 8.0;HDR=No'"
stSQL = "SELECT * From [Sheet1$]"
rst.Open stSQL, stCon, adOpenForwardOnly, adLockReadOnly, adCmdText
Worksheets(sheet_name).Cells(append_from_y, 1).CopyFromRecordset rst
'###Instead of it dumping to a given sheet name, can I turn this routine to a function
'###and have it return a populated array or dictionary object?
End Sub
View 9 Replies
View Related
Aug 3, 2006
I have an excel workbook we use for quoting jobs. All our pricing references are in another workbook on our network. I have reference in the 'quoting' workbook that refer to the 'pricing' workbook.
The trouble is, when some users open the 'quoting' workbook, and have another Excel file open, some references to the 'pricing' workbook change to the other file they have open.
I suppose there is some sort of replicable condition that is happening, but I can't figure out what is going on or why.
So my question is: Can I ' lock' an in-cell reference to an external workbook so that it NEVER changes?
I tried hand entering the reference (network location+workbookname+sheetname) into a cell and then referencing that cell, but I couldn't get that to work.
View 9 Replies
View Related
Apr 17, 2007
I am trying to use a conditional sum formula where the data resides in and external workbook. This is fine until I close the external workbook and the formula no longer shows the external reference. Is there a way of getting excel to use conditional sums in this way (or what is the best way of achieving this?)
View 4 Replies
View Related
Feb 15, 2013
Upon opening after "Enable" is selected the workbook attempts to locate several nonexistent pieces of data, either internet based files or network based files. Requested data appears to be about 11 years old and would not be applicable it located.
Edit Links shows the location of the requested files, i.e., E:filename but does not show the location within the document that causes this request. A search for "E:" does not locate text in any worksheets.
The question is how to delete or turn off this problem which slows opening, saving, and recalculation of a large multiple worksheet workbook.
View 1 Replies
View Related
Sep 11, 2013
I have a list of data in a column that has blanks along the way.However, I don't just want to fill it up with the one above, or the one below.
If the data is in column B, I want to fill it such that where the row above has the same criteria in column A, column B for the above row is returned.Otherwise, column B in the row below should be filled in instead.I have set up example data below to illustrate.
Initial Data Set:
A
B
1
Criteria
Number
2
a
1
View 7 Replies
View Related
Jul 9, 2014
I'm simply inputting zeroes and ones down Column A. Basically, I either put a "0" or a "1" in A1, then A2, then A3, etc. However, I want to create a Macro that allows me to simply type in a string of 1's and 0's and it automatically takes each 1 and 0 and fills it in down the column. This website has the basic idea (Automatically Moving from Cell to Cell when Entering Data (Microsoft Excel)) but I need only one number per cell and for it to move down the column, not across the row. Here's what I have so far:
Sub SAMPLE()
Dim str As String
Dim x As Integer
Dim y As Integer
str = InputBox("Enter string")
y = 0
For x = 1 To Len(str) Step 4
ActiveCell.Offset(y, 0) = "'" & Mid(str, x, 4)
y = y + 1
Next
End Sub
*I can't get it to only break down into one number per cell down Column A. I think the Mid function or something needs to change.
View 2 Replies
View Related
Apr 8, 2008
I have a column of data with various values and a bunch of blank spaces. Essentially I want to leave the values as is but fill in the blank cells with a number. I’ve written a loop to do this in VBA, which grabs the value in the cell above, but it’s somewhat slow. Is there a more efficient way to do this?
Sub Downfill(Max)
'
'This count variable is used to run the loop
Dim i As Long
i = 1
Do While i
View 9 Replies
View Related
Jun 21, 2009
Here's the outline of my problem...I'm building a directory of hedge fund contacts and need help in cleaning data and automating the entry of fields that will remain the same. I have one workbook with 2 pages... One is for the Companies and the other is for the contacts that work at those companies.
ex. Company #551 is 1794 Management - on this page we have fields for address, floor, city, state, zip, phone, fax and website.
on worksheet 2 is where the contacts are kept... the identifier is #551... on the contacts page all contacts under a specific company number will correspond to that company.
Here's where it gets tricky.... for the contacts the addresses are all formatted differently - first I'd like to sort by management co and address and have excel copy the first listed address format and copy this to the other contacts for that company.... when the formula reaches the next contact with company #552 it will look for the new first address and use this one for all that companies contacts.
after the formula finishes the fomating I'd like the sheet to become intuitive( dont know if this can be done but what I'm looking for is say when I add a new row and enter #551 as a company and enter the new first name, last name, and job title - if the excel sheet noticing that all contacts under that format have the same address, phone, fax, company name etc...
View 9 Replies
View Related
May 21, 2014
I am working with monsterous excel sheets (named Data) and need to a column that is governed by the ZIP. I have a sheet named Source in my excel folder with all the matching information next to the excel sheet. The column my zip codes in Data starts on J6 to J290 and the zip codes in Source are from A2 to A2671. The information I am trying to pull from Source to Data are names and branches located in Source from B2:E2671.
What function will auto fill the columns with the correct information. If it can only be done by putting a function into each column, that is not a problem
View 1 Replies
View Related
Aug 6, 2014
As you can see, I have 2 worksheets. I want that all entries in columns B,D,E and F automaticly fill in in multiple tables in sheet2. Also, if possible that macro creates tables (for example: if I have 100 rows, I want to extract 100 separetly values from column B,D,E and F and to enable automatic creation for new table - for 101 entry )
here is the dropbox link of the file:
HTML Code:Â
[URL]
So, I want when I click on the button I want to automaticly import all mentioned values into sheet 2 (I have marked with RED where to put values from which column). In this example, I expect, when I clik on Print all tables button, to print 7 tables in sheet2. In sheet2 I have 2 tables as you can see.
View 2 Replies
View Related
Jan 23, 2014
I have a peptide sequence that is 4500 amino acids long. I have the numbers 1 - 4500 in column A and the corresponding letter code for the amino acid in that position in column B. I have done a bunch of other calculations and isolated small series of amino acids within the entire sequence (example: positions 25-42, 153-166, 381-297, etc).
I would like to fill column C with either a blank space or, if it is in one of the selected series, the letter code again. So that the first 24 rows of column C would be blank but rows 25-42 would mirror the letter shown in B. Then blank spaces again until we reach row 153, etc.
I've used a vlookup to paste the letters with the chosen parts of the sequence, but now I want to space them out along the entire 4500 so that I can see if there are overlapping areas between several sets of criteria for choosing series (these would go in columns D, E, etc).
View 3 Replies
View Related
Apr 26, 2006
My worksheet looks like this: column A - ID, B - Group #, C - data. When data has been entered for all the IDs in a group I would like a message box to appear stating end of group #.
View 4 Replies
View Related
Jul 12, 2006
I have previously used an Excel file - created by someone else - which had pre-entered data. It was set up in such a way that if I typed a person's ID number into a cell, their name popped into the following cell. I know that the names and IDs were pre-entered somewhere else - in another sheet? In a file linked some other way?
What is this called? How do I do it? I want to end up with a file where I can type a department code into one column and have the department NAME pop up in the following column. I am mostly doing this as a time- saving measure when completing long lists of supplies being distributed to different departments (long story.) If anyone can even tell me what this is called, so I can look it up correctly, that would be... sublime.
View 6 Replies
View Related