I need to extract data from another source file using VBA. I have problems copying the extracted data and format into the required data format. And also, how do i delete the row that is not required in the output file, in the below example: The row, D0, is not needed. An Example Data Format From the SOURCE file:...............
View 2 RepliesI have a base excel file for summarizing some data, the problem is that the data comes from a different excel spreadsheet. What I want to do is make a function that pulls the data from another spreadsheet into my base file. It would be easy if it were just one excel sheet, but this job would require where the data is pulled from a data file which has many modified versions.
Can anyone tell me how to do this? The files with the data will be structured the exact same but with different data entered in. I just want a button so I can click the file I want the data from and have it show up on my summarizing base file.
The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.
how I could add to the code so that it also inserts the file name in column c?
It would make it easier to track the data in column B.
VB:
Sub CopyFromFile()
Dim fPath As String
Dim lRow As Long
[Code].....
I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.
The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.
If I have a spreadsheet with some data loaded in through XML and viewed as a
map, what is the VBA command to reload/reimport the source XML data?
The scenario is that the source data (XML) may change at any time, and I would like to reflect this to the presentation layer (excel) at regular intervals. I have a loop set up but cannot find the command to reset the source XML data.
I have 2 excel files. One excel file (File A) is a basically a source file on a shared network that users input data into. The second excel file (File B) I created in hopes of basically using to analyze the data in File A. I used a data connection to access the data on File A while on File B. The issue I'm facing now is that when I refresh the connection, it locks File A from being opened until I close File B. Currently, I have the data connection to refresh only manually, so I'll open File B, refresh it, save, close, and then re-open without refreshing, so that way File A only stays locked for that short amount of time. Is there any way to make it such that when I refresh the connection, it doesn't lock File A?
View 1 Replies View RelatedI need to set up an automated process to open an existing .xls file, extract the data from specific cells located in that file and insert it into a totally different file that I have preformatted to accept this data.
Example scenario:
If I have "that_file.xls" and it is unopened, but it contains data in cells "A2 thru F2" that I need to extract... then I have "this_file.xls" , which is open, and set up with all my calculation cells and a specific layout (lets say it has cells "D8, E9, F10, G11, H12, and I13"). I want to be able to add a button to "this_file.xls" to run a macro that would open a file search window where I can select "that_file.xls", and then it will automatically extract the data from "that_file.xls" into "this_file.xls" as follows:
A2 to D8
B2 to E9
C2 to F10
D2 to G11
E2 to H12
F2 to I13
I'm trying to streamline some processes at work and have encountered a bit of an issue.
I have a source file containing a grid of data (9 columns) with a series of dates they relate to down the left side e.g. 13-Feb-2013. This data covers a whole year.
What I want to do is pull through data relating to a discrete month into a grid in a seperate workbook.
I have a feeling that date ranges and possibly some kind of lookup might be useful but am struggling to see how to pull it all together.
I have a report that is downloaded off a company database and creates a report that is attached.
Is there a way to create a macro to extract some data that I need and leave the rest:
I need to keep and everything in the assigned column
PB ID (A3), Issue (B3), Group Code (J3), Group Name (K3), Sub group id (L3), Sub Group Name (M3),Subject ID (L3), Subject Group Name (M3), Subject ID (N3), Subject (O3), PB Status (P3), CPM Report Name (T3), CPM Profile Name (U3), Profile State (V3).
The worksheet's name changes so I can't reference the worksheet. Attached is a copy of what I get when downloaded.
I have my Excel workbook open with 690 files hyperlinked to the specific folder on my documents, if I click one link the folder opens fine with the file I am looking for.
What I need is one of you genius's to invent a code so that I can highlight my 690 files and extract the data from each file and input that into a seperate workbook.
I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
Thanks in advance for any help,
Jon
Is it possible to decode an executable that was written in VBA? Not VBA for excel but just VBA.
View 6 Replies View RelatedI would get the End of day bhavcopy from the exchange which is a .csv file. The first column contains the column heads like name, open price etc. and the first column. Now I have to extract the names and the corresponding column values from this list by referring to a notepad that contains the ticker names to be extracted, place in another workbook and save that file.
My data looks like this - it's a text file that I import into Excel.
What I'm looking for is operations that require welding, as indicated by the "Weld:" in Row 61. If it is a weld operation, I want to know the time (121.705) and the Operator involved (Opr 2 - Row 64).
I'm simply using an IF statement in a loop to extract these specific cells:
Range("N2").Select
Do
ActiveCell.FormulaR1C1 = "=IF(MID(RC[-13],11,5)=""Weld:"",LEFT(RC[-13],3),"""")"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -13))
Because I don't know how to write "real" code, it does a similar loop 5 times. Once to pull the sequence number (the first number), then the time, then I have 3 separate columns looking for Operator 1, 2 or 3, respectively. Once those 5 loops are complete there is a pivot table that summarizes the sequences for each operator.
It works fine, but it's slow. Some of the text files have 10,000 lines in them.
Extracting data from an excel file, the data needed in lies in one column.
in this line
<GPSPosition pntRef="GPS_Auto_0080" wgsLatitude="33.334329659885" wgsLongitude="44.405736558207" wgsHeight="27.615921">
i need to get a table with three columns shows
wgsLatitude, wgs Longitude , wgsHeight
33.334329659885 ,44.405736558207 , 27.615921
etc
and get rid of all other information
this is an example of the how the file look like
---------Column N -------------------
</Feature>
</GPSPosition>
</GPSSetup>
<GPSSetup id="GPSSetupID_18" GPSReceiverDetailsID="GPS_1" antennaHeight="1.660000" stationName="GPS_Auto_0080">
<GPSPosition pntRef="GPS_Auto_0080" wgsLatitude="33.334329659885" wgsLongitude="44.405736558207" wgsHeight="27.615921">
[code].....
seems from above that there are too many data not needed the only thing need to be exracting is one lies of GPSPosition Line.
I need a macro created, which extracts data from the text file, and displays the low level detail which is on the text file.
I have created a sample of the desired results on a tab called "Low Level Results".
I have already created a macro already which extracts data from the text file, but this gives me a high level view and the results of this is on the tab "High Level Results" ( which you might be able to adapt)
Please find attached two files,
Text File,
And Excel spreadsheet
I have a text file (EP-User.txt) contains records of User information like the example below:
[User]
org_id=HR
uid=JohnChen
role=employee
email_address=John@etex.com
first_name=John
last_name=Chen
job_title=Manager
country=USA
language=EN
time_zone=GMT-8
street=3
city=MX
state=NY
zip=123456
telephone=09847684
fax=23469483.....................
The fields of each User record may not appear in sequence and some of the fields may not appear in other User record. The file contains thousands of User records.
1. I need to extract the User records (only certain fields) into excel for data analysis and manipulation (fill-in missing value in certain fields).
2. After analysis and manipulation in excel, I need to export the data back to the original text file format again.
I'm trying to download an Excel file from the internet and then extract data out of it. The problem is that I don't get any errors, yet the file downloaded is only 1kb in size. The extraction bit works, but the file is empty. The actual file is size 350KB.
Code:
Sub ExtractDataTest()
Dim FileNum As Long
Dim FileData() As Byte
Dim MyFile As String
Dim WHTTP As Object
On Error Resume Next
[Code] ....
I am looking to import data in text file to Excel sheet. Here is the main catch - All the data are not of fixed length and are in multiple lines as in the example below.
Text File:
ID001 Dr Dang 01012011 Male
USA Married 123
Below is how I expected the data extraction in Excel:
ColumnA ColumnB
CardID ID001
Name Dr Dang
DOB 01012011
Sex Male
Country USA
Status Married
Pin 123
I have been trying to work this out by looking at other posts (mostly concerning Binary Access) but can't figure it! The source text files I am using can vary in length from 4,000 characters to well over 100,000 characters. However the data I am looking for always starts 40 characters from the end of the file and is 10 characters long! I need my macro to pick out this data and store it as a string (so it can be added to an array and exported to a worksheet later)
View 2 Replies View RelatedI am working with a bunch of files to do a dashboard. I have the main file which shows end results and what my users will see "Dashboard". I have about 10-15 files which I export weekly from MS Access. and I have the "Dashboard Data" file. In this file I have a sheet which links all the information for the files exported from MS Access. In here I have named ranges and dynamic ranges plus additional columns created to look up information in other tables. I use this file as my source data for my Dashboard File so that I can reduce the size of the actually dashboard. Dashboard Data file is about 5.5 mb and my Dashboard is 300kb. I have 12 branches that need to view the data over a radius of a hundred miles so the smal file are better.
My problem: I can get the Dashboard Data file to update without opening all the 10-15 other files. I cant however make the Dashboard file update without opening the Dashboard Data file. I have it to auto update without prompting but its still tells me that it cannot update. I was wondering if there is some VBA code I could use that would work like this:
I open the Dashboard File, Dashboard Data file opens so that Dashboard file updates and then close Dashboard Data File so that the user never has to see the Dashboard Data file. I dont want them to see it or have access to it.
I want to get source code of htm file. The files are on my folder (E:SepehrData).
The files names are like A2 [F1].htm, E2 [F4 conv].htm, E2 (DC L180G).htm and etc.
I want to bring all source code (with the tags, ) on a worksheet at column A in cell A1.
Example below.
HTML Code:
--------------------------------
<!DOCTYPE "-//DTD HTML //EN " html public>
<HTML>
<HEAD>
****** content="Vanemeze">
****** charset="ISO">
</HEAD>
<BODY>******** type="text/javascript" src="..showHide.js">
[Code] ..........
Is there a way to paste an image onto a user form in Excel instead of using the Image button on the tool box?
I am using Excel 2003 for this one.
I have previously used the following code to successfully pull out IE webpage source code for string manipulation.
Its a crude example to demonstrate the principle:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public IE As Object
Sub Sample()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
[Code] ......
However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"
The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......
The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.
Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?
Using :
MS Excel 2010
IE Explorer 8.0
I have a workbook entitled "Correlation Matrix". I download data from my data provider and then update it in excel each night. I have several worksheets in the workbook, one called "MCC". Today I made a copy of MCC, named MCC2, to experiment, then deleted it. Now, after I update my data in Excel it gives me the msg, "We can't open the source file '(Path)[Correlation Matrix.xlsx]MCC2'".
The data updates fine and all values are correct in the worksheets. I've tried closing and reopening the file, closing and reopening excel, but I'm still getting this msg when updating the data.
I have a workbook with a number of pivot tables as well as the source data for each pivot table. Some of the source data sheets needed to be deleted so now if I try to refresh the corresponding pivot table I get "Connot open pivot table source file '[Std Mthly Rpt.xls]Report_2'". I recreated the source file again and would like to know if I can link the pivot table to this new source file or do I need to create a new pivot table??
View 7 Replies View RelatedI am trying to combine sheets from various workbooks into a single file named "Combine". The goal is to have the sheets in Combine indicating their origin.
For example, with:
"File_1" having 2 sheets, "Sheet1" and "Sheet2"
"File_2" having 1 sheet, "Sheet1"
the goal is to have a file "Combine-forum" with the sheets named:
"Sheet1(File_1)", "Sheet2(File_1)", "Sheet1(File_2)"
I have grabbed pieces of code from various sources and read on the forums, but the result is different from what I want; I don't succeed to add the file name to the sheet name. Instead of the file name, the second part of the sheet name appears to be the order in which the workbook have been opened.
To be clearer, what I get is:
"Sheet1(1)", "Sheet2(1)", "Sheet1(2)"
Attachments: To_be_combined.zip 3 compressed files to be combined. I suggest to copy them in C:TEMP and to copy this path in the InputBox (see below). Combine-forum.xlsm Best working file that I have been able to create
I have an excel file - central database - that holds a lot of raw data organized in tables. There are more than 15 files (dependent files) that access this data using VLOOKUP formulas.
I now need to add a few columns to the central database and these columns can't be after the columns already existing, i.e. they have to be on the left of some of the existing columns. Inserting those columns is going to mess up all the vlookup formulas in the dependent files.
Is there a way change the central database file without affecting the vlookup formulas in the dependent files?
I've inserted a Solidworks drawing into a worksheet & wish to change the displayed image using VB. I suspect this could be done by changing it's source file, but can't get the code to work.
View 3 Replies View RelatedI 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" _
[Code].....
I have code in VBA that takes a source Excel file and parses it to multiple re-formatted worksheets. Each formatted worksheet is different depending on a variable in the source. There are n of each type of data (1:n Water Sources, 1:n Water Treatment, 1:n Water Storage). Each item needs a copy of a blank re-formatted worksheet appended to the end of the existing data-type worksheet. For example: Water Source #2 is appended to the bottom of Water Source #1 on the same worksheet.
Currently, I am opening the blank re-formatted workbook each time an additional variable of same type is found in the source file. Meaning, if there are 8 Water Sources, I am opening and closing the blank re-formatted workbook 7 times to copy a blank Water Source to the bottom of the Water Source worksheet and then populate the data. AND if there are 3 Water Treatment, I would open and close the blank formatted workbook 2 more times to copy a blank Water Treatment to the bottom of the Water Treatment worksheet. The same goes for Water Treatment.
Obviously, a huge waste of resources and performance. I would rather open the blank re-formatted workbook once (first time an additional worksheet is needed), use the worksheets as needed, and close it at the end.
Attached is my current, working code of the above.
How should I re-write it to check if the blank workbook is already open use it, if not open it?
Code:
Sub Add_Facility(ByVal Worksheet_Name, Copy_Range, Max_Limit)
Dim Last_Row As Integer
Dim Source_Range As Range
Dim Dest_Range As Range
Dim wkb As Workbook
Set wkb = ActiveWorkbook
[Code] ........