I have bit of code that opens an external excel file (call it the source workbook), grabs certain data, copies the data to other excel file (call it the destination workbook), and then closes. I have most of the code completed but am running into an issue with how to grab certain data from the source workbook and copying it to destination workbook.
I want the code to read the data in the source workbook line-by-line.
For each line that is read, I want to copy part of that data (value in column A and column B) to column A and column B of destination workbook only if the value in column D is equal to “Block”. For column C and column D, I want to do some form of lookup, being I want the code to look at each line of data until it finds corresponding line in which column A = “60612105” and in which column B = the value in B. Once this line is found, copy the value in C to column C of destination workbook and value in D to column D of destination workbook.
I have attached two files. First file is the import file (destination workbook) which has two tabs; (1) import tab (2) desired result. Second file is the raw data file (source workbook). Hopefully this makes since.
I have 5 or 6 macros set up to import stats from the internet to specific sheets. I want all of my imported data to be pasted starting at A1. However when i run these macros, the data IS pasted at A1, but when i run it a second time (or the next day after the pages have been updated), it pastes the data BESIDE the previously pasted stuff. here's what i mean..
say my imported data spans columns A to S.. everything's fine. but tomorrow i want to update the data so i run the macro again. well, it pastes the new data to A1 and again it spans from A to S like it's supposed to, but now it also tacks on the same paste job from S to, AG for instance.here's the code... There are currently 5 macros that have this problem. I thought by selecting A1 as the starting point, i could alleviate all of this but apparently not.
I am importing external data to "Workbook A" from the source workbook, "Workbook B." Workbook A is set up to refresh every 15 minutes. Workbook B is shared. I am recieving an error saying either, the 'file is in use', or 'the file is locked,' depending on the senario.
Scenario 1: 1. I open A and enable data connections. 2. I try to open B, but receive the message: '(File location...) is in use. Try again later.
Scenario 2: 1. I open B. 2. I open A. 3. I go back to B to edit information. 4. When I try saving the information I get the following message: 'File is locked. Try the command again later.'
I need to be able to have A open and periodically refreshing. I also need to allow users unhindered access to B at the same time.
I'm having some issues importing external data from an Oracle 9i database. Here is what is happening:
I am initially able to import data from the table I want to into Excel. I do this through "Data -> Import External Data -> New Database Query" where I have my .ORA data source. I'm able to log in using my user and password and import the table. My problems come after I import this first set of data.
If I try to "Edit Query…" I get an error box that says "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
If I try to "Refresh Data" I get two error boxes. The first is just: "[Microsoft][ODBC driver for Oracle][Oracle]" and the next says: "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". It then prompts me for the User name and password of the DB I'm connecting and after I enter the information, I get the same two error messages.
Also, if I attempt to establish another connection following the "Data -> Import External Data…" steps I run into a different problem. Upon entering the information for the User Name and password prompt I get two error messages. One says "!" and the next box says "Couldn't read this file". Once I click "OK" in those boxes, the Microsoft Query window automatically opens with nothing in it.
I can't seem to figure out why I can initially pull down data and then cannot succeed afterwards. I'm unable to connect to the DB I have through Excel until I completely closed all Microsoft Excel instances.
I also use this data source in Microsoft Access without any problems.
I have a file that gives some statistical data to my co-workers every 15 minutes. A common question I get is "How does that compare to last week?" Then I have to open the file from 7 days ago, find the data from the same time interval, and subtract it from this week's number in my head. I'd like to have excel do this for me.
I know how to get data from an external file. The problem is, these files are named with a date on the end of the file name. So tomorrow, the static formula won't work anymore (or rather, it will give data for a file from 8 days ago, instead of 7). I'd like to excel to use today's date, find the file from 7 days ago, and get the data from that file to compare to the current file.
Here's what I've done so far:
Code: ThisDate = Range("C1").Value 'the cell with todays date in it ThisDateName = Format$(ThisDate, "yyyy-mm-dd") 'now formatted the way I need it ThisDate7 = Range("G1").Value 'the cell with the date 7 days ago ThisDateName7 = Format$(ThisDate, "yyyy-mm-dd") 'formatted correctly ThisDate14 = Range("G2").Value 'the cell with the date 14 days ago ThisDateName14 = Format$(ThisDate, "yyyy-mm-dd") 'you know, in case of a holiday 7 days ago
I have no clue what to do next. I want Excel to: Use "ThisDateName7" to find the file with the name "pph_tracker_[ThisDateName7].xlsm"Get data from a cell in that file (say, C15)Subtract it from the data in the same cell (C15) in today's file (this week - last week)Give me the result in today's file (say in cell C20)Do that again for cells D15, E15, and so on (result in D20, E20, etc)
I assume I can figure out the rest from there. Can I use the variable names in an actual formula in cell C20? Something like:
=C15 - '[pph_tracker_{ThisDateName7}.xlsm]Sheet1'!C15 or even =C15 - '[pph_tracker_{The Date in Cell $G$1}.xlsm]Sheet1'!C15 which would require no macros at all!
I am using the following code to browse to a CSV file and import it into my current workbook. How can I “capture” the name of the text file (myFile) for future use?
myFile = Application.GetOpenFilename("All Files,*.csv") If myFile = False Then Exit Sub End If
' Open file With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=Range("A1")) .Name = "DATA" .FieldNames = True...............
I have a group of CSV files that I will need to periodically import and sort in Excel.
The CSV files will contain one column I wish to use (It is the first one and the rest can be ignored or imported) and sort the desired data based upon the existance of a particular bit of text. Each entry will either be just a username or a username with a '-label' appended to it so for instance each of the CSV files might contain:
bob fred mark-label ted angie-label
Basically I want to seperate the names with the -label from the ones that do not have the -label. Doing searches, I was able to get part way using the code from this thread:
[url]
But am running into problems trying to modify it to suit my needs. Is there an easy way to accomplish this? I'm not very good with VB so that hasn't helped me. It would be nice to move the sourted output to seperate columns if that is possible.
I am trying to have the total (cell e78) from one of my own excel files automatically filled into a cell in a different excel file when the number of that file is typed into a third cell. i.e. if the total on Ticket 4126 in cell e78 is $4500, then when I type 4126 into the Ticket # cell on my seperate Invoice I'd like it to fill in the third cell under Amount as $4500. Is this possible?
is it possible to import just a portion of a csv file into my excel workbook? For example:
My csv file has data from 2010-2015. I want to somehow import only data between 2013-2015 from the csv file into my excel workbook every time I click refresh.
I cannot delete data from the source file because many other people are using the same file. Is there some sort of "SQL" for excel that makes it possible to trim csv file?
I'm working on creating a custom HTML report with javascript that I will be using to import in to excel to populate some graphs. Basically everything is working, except if I use javascript to show me a value.
When I use Import Data from Web via excel, everything gets imported except for the javascript part of the html doc.
An example of the html file (stripped down to nothing but 3 peices of data): [URL]
I've 1 master file with event info (dates, times, categories, prices etc). I have another file to track sales which uses index match to pull data from the master file (stored on a server) instead of double entering.
Upon opening the sales file, all the index match cells are blank until opening the master file, even after saving.
Is there a way to make the file retain all the data it pulls without setting manual calculation (manual spreads like a virus to other user's files - finance manager almost killed me last time)
I currently have a macro button set up to open/close the master file, but if I try to filter/sort the data without the master file open it all disappears again.
I have thousands of Excel files, each with a generic names (i.e. 2009092812163503.xls)
Each of them contain a header with contains column titles like Company Name, Executive Contact, Address, etc and then a single row of data for a single company.
I want to be able to task excel to extract all the data in the second line and enter them all into a single spreadsheet for further work.
We're talking 30,000+ unique files here, what would be the best way to approach this?
I wanted to see if there is a VBA code to do the following :
a) Select a TabText Delimited file based on a criteria b) Import the Selected Data to Excel
I have the vba code where I can open the tab text delimited file in excel, use a selection criteria and then copy the data into excel. But I am having problems with the case where the Tab Text Delimited file exceeds the row limit that excel currently has and wanted to see if the data import can be done without opening the text file into excel at all.
I have a number of large CSV files with approximately 1.9 million rows, (this is more then excel can bring in). I typically have to import/delimit the files when brining them into excel. However, when I try this I get the message not all data imported. I would like to be able to split the records in the csv file to multiple tabs in the excel woorkbook when doing the import/delimit but do not know how to accomplish this. The delimit of the data varies each time due to the nature of the data so doing a macro is more trouble to create each time. Currently I am having to open the csv file in notepad and split it up into multiple files then import each new file seperately.
I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.
I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.
I've got a couple of formulas in a workbook which lookup values in another workbook. When I reopen the initial workbook the formulas results come back as errors (i.e. #VALUE!) unless the workbook it is looking at is also open. This is not desirable, does anyway know how I can lookup values in another workbook which are not open? The formula which is causing me problems is as follows:
Basically I want to point my Excel sheet to a bunch of external Excel files. To get data from an specific cell (in this case, cell F10) of the external file, all I have to do is this: ...
I am importing data from a csv file into another program. For some reason this particular cell is causing everything past it in the same row to be thrown off. It is moving bits and pieces to the next column, then the next, etc....all in the same row though.
Can you see anything in this data that would cause issues in a csv file when importing. The data in this cell is html code for an ecommerce item description.
How can I limit the number of lines when importing a file? Limit the import to the first 53 lines of a file that has 50,000+ lines.
Want to import the first "XX" set of lines, then I want to skip a set of lines, then continue to import the rest of the lines. The second import needs to be inserted 2 or 3 lines down from the first import.
There is the function:
Workbooks.OpenText Filename:=
But it only has a StartRow and can not specify the "EndRow". Also can not specify where to start in the worksheet, i.e. Cell A5
I have made a macro to import the a excel file into a new file after truncating few initial rows. Here is the code for it:
Sub Import() Dim Prompt1, Prompt2, Prompt3, myDir, myFile, mySheet As String Dim ClCnt As Long 'column counter Prompt1 = "Enter the location of input file:" myDir = InputBox(Prompt1) Prompt2 = "Enter filename:" myFile = InputBox(Prompt2) Prompt3 = "Enter sheetname:" mySheet = InputBox(Prompt3) With Workbooks.Open(myDir & myFile).Sheets(mySheet) ClCnt = .Range("A33:CB33").SpecialCells(xlConstants).Count ' Count the number of columns present..................
I have a multilevel XML file. Attached my1.zip (XML files cannot be uploaded as is, hence I had to zip it). When I import the XML into excel, it creates multiple columns for the multilevel data. However, I need the multilevel data as additional rows. I have attached another excel file sample.xlsx which shows how excel imports the XML and how I actually need the data to be.
Screen shot attached for your quick reference sample.xlsx.jpg
What's the technique for opening a .pdf file and copying its contents into Excel for subsequent analysis by VBA?
On my Mac version of Excel there doesn't seem a facility to import it or even copy and paste it. Is this something that's available in Windows versions of XL?
I'm trying to create a geography project for my upcoming primary class but my Excel expertise is limited. I am trying to import small jpg's from a folder and then make them appear in a userform or Msgbox. Is it possible?
So far I've created a very simple way to display the text in a message box but I would also like to be able to display the flag. Macros 2 & 3 are initiated by clicking on images while macro1 displays the information:
I have file name "WM" from where I want to run macro and want that It open a File let say "test" & do the rest of the functions. like if I have following code how can it possible.
"TML" sheet is on file "WM" and "MD" sheet is on file "test".
Code:
Sub Copy_on_backup_file() Workbooks.Open Filename:="C:UsersBinDesktop est.xls" '*** Select the destination cell With Sheets("TML") LR = .Range("A" & Rows.Count).End(xlUp).Row Range(.Cells(3, 1), .Cells(LR, 3)).Copy Sheets("MD").Range("B" & Rows.Count).End(xlUp).Offset(1) End With
I want to do is import a TXT file that has about 70000 rows of numbers into excel. I know it has a max of 65536 so I would like it to import the numbers and put them into 2 columns C and the rest in D.
Sub ImportLargeFile() 'Imports text file into Excel workbook using ADO. 'If the number of records exceeds 65536 then it splits it over more than one sheet.
Dim strFilePath As String, strFilename As String, strFullPath As String Dim lngCounter As Long Dim oConn As Object, oRS As Object, oFSObj As Object
'Get a text file name strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...") ........................
I am trying to create a macro that imports a text file that has a changing name (it has date/time info in it). The file name will have some consistent parts and the rest I hope to be able to use wildcards on. In the code below, the path will be constant and the file name will always have "Heavy Water" in it. How can I change the code to import any version of the file?
