Import Appropriate Data To Worksheet
Nov 11, 2009
I have a workbook that contains an 'AllData' sheet. That sheet has 6 columns titled "category, keyword, bid, advertisers, search vol, online". Each category has its own worksheet and i want to put the data for each category onto the corresponding worksheet. I am having a difficult time describing this so I have attached a brief example. I have to do this for 145 categories so I would much prefer to do it automatically rather than manually.
View 14 Replies
ADVERTISEMENT
Mar 2, 2012
I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.
The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.
BRIDRequirement Bug DescriptionBUG ID Alt Bug ID
PQ115 PQ115-Login Bug Description text xihllloloj43712 123
PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126
For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.
These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath
BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do
BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do
So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.
View 5 Replies
View Related
Jun 27, 2013
I've created an Auto_Run macro that imports a CSV file into a specified worksheet. That worksheet is referenced by VlookUp in another worksheet within the workbook. Everything works perfectly EXCEPT, in the midst of running the macro, the user is prompted to (RE)choose the csv file already specified in the macro. Neither the folder nor the filename ever changes, so I'm not sure why I have to keep indicating the file every time the macro runs.
PHP Code:
'Sub Auto_Run() ' ' Auto_Run Macro ' ' Sheets("CSVData").Select Cells.Select
Selection.Delete Shift:=xlUp With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;
G:TestProjectsWorkOrderTest.csv", Destination:=Range("$A$1"))
.Name = "WorkOrderTest" .FieldNames = True .RowNumbers = False
[Code] .......
View 7 Replies
View Related
Aug 21, 2013
I'm looking for a macro that will import the last row of data of each .csv files from a specific folder, i.e., c: emp, into one new worksheet.
View 1 Replies
View Related
Oct 7, 2007
I currently have a userform, and on commandbutton_click, it performs
the following code that adds a column in a access table to a combobox(cbList)
Private Sub CmdName_Click()
Dim rstName As ADODB.Recordset
Dim strClientDatabase As String, strConnectionString As String
strClientDatabase = ActiveWorkbook.Path & "9001.mdb"......
After its listed on the combo box, upon commandbutton_click on another button, i wish to extract a particular record, based on the selection made on the combobox(cbList). This is where im having problems caused i have no idea how to do so. I just want it to extract the particular record row, based on cbList, and insert it into range A100:D100 in a particular worksheet. It is then updated and added to a listbox which ive already done the coding for, and with another button click it would add the details in the listbox into the appropriate location i wish to.. The only place im stuck is with extracting the data from access into a A100:D100 range in any worksheet. what ive come up with, but is incomplete is :
Private Sub CmdImport_Click()
Dim adoRS As ADODB.Recordset
Dim strSQL As String
Dim strClientDatabase As String, strConnectionString As String
strClientDatabase = ActiveWorkbook.Path & "9001mdb"
.........
View 9 Replies
View Related
Mar 17, 2009
I have a workbook which, amongst several worksheets, also includes 12 blank worksheets (one for each month of the year).
Is it possible to have a macro that will (a) first prompt the user to select an excel file to import (the excel files to import are provided each month and only have one tab); then (b) select which worksheet to import the data (values only) from the selected file into.
View 6 Replies
View Related
Oct 7, 2008
I found this script on the net which I think is very good for importing multiple workbooks/worksheets into a current workbook.
It works fine when running as a module, but when I change it become an add-in it gets a bit a confused when trying to move sheets.
I'm pretty sure its something to do with the way thisworkbook is referenced. From some of the suggestions I see it could fixed by setting-up a class module, but it sounds like i'd have to do that for every workbook i wish to import worksheets into.
View 2 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 17, 2009
I am trying to do, I have a master workbook (Formatter.xls) which contains a macro 'Import'.
When run, I want to open a series of workbooks - the filenames are listed in cells B3:B20, and the workbooks are located in the same folder as Formatter.xls
I am trying to open each of the workbooks listed in B3:B20, copy the entire contents of Sheet1 to a new sheet in Formatter.xls. The names of the new sheets are listed in cells C3:C20 in Formatter.xls.
The biggest problem is that I don't want to have to specify a path to the files to be imported. Since they are all in the same folder will this be a problem? Alternatively (and I have absolutely no idea how to acomplish this), would it be possible to have an input box to browse to the folder containing all of the files?
The reason that I can't specify the path is because it will be run on different computers and the locations / paths will change all of the time.
View 9 Replies
View Related
Dec 11, 2012
Excel 2010 / Win 7.
I have some code that imports a csv file into a workbook. This works fine when i use a file named .csv. I was hoping to use a random file name (in this case .bmhs) so that we can determine which files we need to import.
The problem i have is when importing a '.bmhs' file the data comes in column A and is a comma seperated list. If i use .csv then the commas are used as the column seperators (which i need).
impFle = Application.GetOpenFilename(filefilter:="BMHS Files, *.bmhs", Title:="Select Import File")
would give me 1,2,3,4 all in column A
impFle = Application.GetOpenFilename(filefilter:="CSV Files, *.csv", Title:="Select Import File")
would give me 1 in column A, 2 in column B, 3 in column C and 4 in column D.
Is there any way that i can maintain using my own file name (.bmhs) but have excel treat it as a csv file.
View 2 Replies
View Related
Jun 5, 2013
The Workbooks.OpenText works for me, but I need to import the text file into one worksheet of an existing workbook. Is there a method to do this?
View 1 Replies
View Related
Jul 27, 2007
i want to import a file called "export" into my work sheet, I recorded this macro
Sheets.Add
ActiveSheet.OLEObjects.Add(Filename:= _
"C:Documents and SettingsmattDesktopexport.csv", Link:=False, _
DisplayAsIcon:=False).Select
which works as long as it is on matt's pc and on my desktop, what i need is to import the file from the same folder the work sheet is stored in, that way it will work on any pc?
View 14 Replies
View Related
Sep 7, 2006
I have found the code shown below that imports Folder Contents into a Worksheet in Excel, however i need to import a folder tree structure for a set of folders into a worksheet not the folder contents. Is it possible to do this?
Could the code i found below be altered to list the folders not the folder contents?
The code is:
Sub Print_Dir_Contents()
Dim Input_Dir, Print_File As String
Input_Dir = InputBox("Input the path containing the files you " & _
"want to list on your worksheet" & Chr(13) & Chr(13) & _
"for example:C:My Documents*.*")
If Input_Dir = "" Then Exit Sub
' If you want only to print a specific file type, you can
' substitute the "*.*" with "*.xl*"
' (for Excel files only) for the directory specified in the
' InputBox above.
If Application.OperatingSystem Like "*Win*" Then
Print_File = Dir(Input_Dir) & "*.*"
End If
View 9 Replies
View Related
Oct 25, 2007
I am trying to convert multiple tab-separated text files into one worksheet. All the text files have the same format and file structure. I have been copying and pasting these files into an Excel spreadsheet but this is time consuming. I need to find out if anyone has any ideas on how to accomplish this task using a VBA macro.
View 3 Replies
View Related
Jan 23, 2008
I want to read data from Notepad into Excel. I found this code on the net and have been trying to modify it for my needs:
Sub ImportText(FileName As String)
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Lines() As String
Const DataRowStart As Long = 1
Const DataColStart As Long = 1
FileNum = FreeFile
Open "C:...data.txt" For Binary As #FileNum.................
This pastes the contents of each line into 1 cell but I want to paste each value into a seperate cell. So in the attached data.txt there are 5 records each of 2 lines. For example in the 1st record I want to paste 05-693-1900 into 1 cell then 0040 in the cell to the right of that, Town A into another cell, 000000 into another cell....and so on. For the 2nd line it should be 000000000033 into one cell, AA28816 into the adjacent cell...and each remaining number into a seperate cell. I also want to leave 3 blank lines before going to the next record.data.txt
View 7 Replies
View Related
Apr 6, 2008
I have been using the attached to pull multiple csv files into seperate sheets. This takes all files from a user specified directory and dumps them into a workbook. I only need three specific files. How do I request specific files instead of a wild card.
View 3 Replies
View Related
Sep 26, 2011
I'm a novice at Excel trying to create some forms for work. My goal is to have a working workbook that holds all my values from multiple jobs that gets updated daily with additional worksheets that gets sent to me. I'd like to be able to hit a macro in my workbook that allows me to select a file that has been emailed to me and import their values into mine. For example:
While using my active workbook I want to select a file. Then take cells A1:A15 from selected file and paste to cells F1:F15 in my active workbook.
View 2 Replies
View Related
Jul 30, 2009
I've got a single folder for the year 2009 that contains multiple files, identical in nature but updated for each business-day. In other words, the same report is generated daily with updated info. The naming convention is the same for every file (i.e. Daily Net Debt Report 02.2.2009.xls)
My ultimate goal is to have a macro that is dynamic enough that if its run on any given day of the year, it pulls the data (specifically from the worksheet "Detailed Cash" cells C1:E26) from every file included in the folder and places it in a single worksheet in the master file. So, for example, data from 1/2/09 would go into cells A1:C26, data from 1/3/09 would go into cells D1:F26, etc...
View 7 Replies
View Related
Aug 5, 2013
Below is the code I currently use to do the following which creates service reports.
When you click on the command button you can choose any XML file you want to import. This file is copied to a temporary worksheet where i selectively choose cells to populate textbox's which later populate my reports. What I want to do is the same exact thing but with a text file. Not all text files are the same length so it needs to be able to have a range. Here's my working code.
For Each SheetName In Array("Maintenance Data Sheet")
With Sheets(SheetName)
.Range("B127") = ""
.Range("U127") = ""
.Range("AJ127") = ""
.Range("AV127") = ""
.Range("AY124") = ""
[Code]...
Cancel:
MsgBox "No XML Selected"
End Sub
View 1 Replies
View Related
May 26, 2009
I have requirement to extact data into a spreadsheet. This data is extracted from CSV file which is huge normally over 7MB. I have found a macro on the internet which I have included with this post.
Sub split()
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum .................
I have also included reult data that I get after running the macro. I have cut down on data due to attachement restrictions. But usually there are multiple spreadsheets named "Sheet1, Sheet2, Sheet3 and so on". I have numerous requirements. First of all I want to be able to spreate data into columns. For now how I do that is by going to data -> text to columns and then selcting delmited and then selecting comma as my delimiter. Second thing I want to do is is only extract range of data from this output into a new worksheet. I am only interest in the name like for example ALBANY-Serial0/0/0 and data that is in the range of 8:00 AM to 6:00PM. So the new sheet should only have name and for that name data in the range of 8:00AM to 6:00PM for all the sheets "sheet1, sheet2 etc" until all data has been extracted.
View 7 Replies
View Related
Jun 5, 2014
I am trying to take multiple tables from a Word document and import them into an Excel worksheet. Currently I have found two versions that when combined, could yield what I am looking for. The first one imports the table's data from Word, but does not maintain formatting of the table (font, colors, rows/columns etc.):
The next code maintains formatting, but only imports/pastes one table:
[Code] .........
For the second one, I do not like the fact that it is calling a specific Workbook to paste into. If I could somehow maintain the ability to import/past multiple tables while keeping formatting that would be perfect. An extra bonus would be to import each table within the Word document into individual Worksheets in Excel. I am also using Office 2010.
References: [URL] .........
VBA - How to preserve source formatting while copying data from word table to excel sheet using VB macro? - Stack Overflow
View 14 Replies
View Related
Feb 4, 2009
I have the following data retrieved via import data tools ...
View 9 Replies
View Related
Jan 22, 2014
i attach the file so you can look after you read
i have soccer table 1 sheet is "teams" which is the table of the league. 1 sheet is "games" which is the table of games of the league the last sheet called "import by date". in the end of every game day i sent email with the results of current day. for now im copy and pate all results and its not that easy and convenient. i want to make some formula that when i click in import data sheet the date its shows me all the results from that date. i try to do it with pivot table but didnt like how it displayd
View 2 Replies
View Related
Jun 20, 2014
I have a tedious task of copy/paste from our website. have a look at below sample data. I have around 1500 rows of data like this.
Sheet1
A
B
[Code]....
Column A has input data which i need to copy and paste in a web form. Column B will have results scrapped form webpage[ URL]. This webpage will need login details
If a code can be written which take value form column A of spreadsheet and paste in webpage like shown in above image and hit search button. Next webpage will be loaded with number of results like shown in below. Take the number of results and paste in column B of spreadsheet.
View 1 Replies
View Related
Jan 29, 2008
I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.
However, I have a problem in that my import macro gives me 'Run-time error '1004:
Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!
The code below shows is for the import macro only:
Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
View 8 Replies
View Related
Aug 6, 2009
I'm wondering if I can use external data in an excel spreadsheet without having to import all the data. I basically need a persistent connection with the external data and I want the excel spreadsheet to get the item descriptions by using the item number as the user types in the item number. Writing out the descriptions is killing me and I have all my products in an excel spreadsheet already. I really need someway to link two separate excel files together. One with data and one with user input that searches that data as I type in the item number.
View 2 Replies
View Related
Jun 17, 2014
I have a tab in my workbook with 10 various items 50 pieces each, in the 2nd tab i have about 100 orders for these items. What is the best way for me to find out for each of the 10 items how much i sold?
View 6 Replies
View Related
Jan 14, 2010
I have this VBA macro which imports data from a text file. I do not know exactly how to make it good.
I have two sheets, named <INDATA> <Drawing_list>
On sheet <Drawing_List> I made a command button with this ....
View 14 Replies
View Related
May 18, 2009
The task that I have to make is a little bit complicated, especially for reason that I am not v VBA programmer. But however here is the task (and is not a school assignment).
First I have to clear the worksheet Invoice_Plan (file Financialplan.xls), but the whole row(s) that have data in column F (comment) have to stay on worksheet.
Than I have a worksheet Data (file invoices.xls where are all invoices from year 2009) and in column A are the data, that I have to import into in worksheet Invoice_Plan to (second file FinancialPlan.xls) in column A data under following rules (other must be skipped):
View 9 Replies
View Related
Aug 27, 2009
there is i website that i want to imoport data from it to my excel sheet using vba, but the poblem is that the website will need my username and password...
i search around for possible threads and i found a very close one but couldnt have it do the submit option. also i donot want it to show me the intenet explorer window. i just wantit to do these steps:
after clicking a button:
1-logon to the site using my user and password
2-redirecting to the report link and improt what ever is in that page
3- all this with out opening IE.
Public Declare Function ShowWindow Lib "user32" (ByVal lHwnd As Long, ByVal lCmdShow As Long) As Boolean
Private Sub LoginTone()
'Dim ie As InternetExplorer
'Set ie = New InternetExplorer
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Navigate "http://s8.brono.com:80/mongol/fiona/index.php" ; this is the login address
Do While .busy And .readyState 4: DoEvents: Loop.......................
View 9 Replies
View Related
