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 RepliesI have a website (National Lacrosse League - 2013 Regular Season - Standings), that I want to pull information off and automatically into Excel.
I have been using web queries on other websites, and they have worked beautifully, however the tables that I want aren't recognised by the web query wizard (no yellow/black arrows). I can import the whole page through a query, however that's not really practical.
Is there a way I can import just specific tables on this site?? I'd like both the 'east' and 'west' tables.
Actually i've downloaded Tracking report of my SPO and this report is on 4-5 csv files (1 file per week). I have to gather data from these 4 files into one Worksheet. I have to do this with command button on my worksheet; with Open file dialogue box (i want to locate the csv files).
CSV files are something like that (I need only first 4 columns):
ABCDE1Name MSISDN Date Location MapLink 2M. Younus Safi "923***550577" "2011-10-07 20:36:18" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 3M. Younus Safi "923***550577" "2011-10-07 19:36:26" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 4M. Younus Safi "923***550577" "2011-10-07 16:39:58" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 5M. Younus Safi "923***550577" "2011-10-07 15:37:23" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 6M. Younus Safi "923***550577" "2011-10-07 14:37:05" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com"
and my Worksheet is like that (With a command Button):
I want to import first 3 columns of csv file on first 3 columns on my worksheet; leave 2 columns blank and then import the 4th column... now for example i have done importing data from 1st csv file and the data is on 50 rows.. i click the command button again, locate the 2nd csv file.. do the same thing (import 1st 3 columns, 2 blank, then 4th) --from 51st Row-- and so on...
ABCDEF1NameMSISDN DatedBlankBlank Location2
3
4
5
6
7
I have a .txt file (comma separated) that updates daily, which I need to import into Excel. The .txt file contains data from many years, but I only need the data from 2012. Is there a way to only import rows where the data in column "yearID" equals 2012? Since this is a daily operation, I'd rather not import all the rows and then sort/delete rows every time.
View 4 Replies View Relatedimporting some selective data from txt file to excel. Only the following rows I need to pull out from text file into the Excel i.e. Project row, installation row, and two rows immediately below the installation row. This is my logical procedure..
1. Macro look for the rows that contains the word "PROJECT"
2. Open an excel file
3. Pull the row from txt file to this new excel file.
4. Macro look for the rows that contains the word "INSTALLATION"
3. Pull that row with INSTALLATION AS WELL AS Two rows immediately below the INSTALLATION row.
I have an Excel workbook with 2 worksheets in it. One of the worksheets imports a txt file and then the other worksheet displays data that formulas I have written have collected.
I usually import the txt file by running through the many steps with the "import data" option. This is good but I want it to be faster.
I used the macro recorder and ended up with the following code
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\DesktopImport FolderTest.txt" _
, Destination:= Range("A1"))
.Name = "Test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
I have a variable list. Each column will be 250 digits and numbers of rows will be variable. I want a macro to import a text file from a specific folder into a sheet without split it to columns. So we will work only in column A
Then macro will find B1002 wording in A1. if it is exist, it will copy the next 36 digits after B1002 wording if not then it will search A2 row. The next step will be to search and find another wording "B1001" if it finds it will replace copied 36 digits text. If it can not find B1001 wording it will go to upper row and search B1001 wording here and paste the text. This will go on till row shows #END. This means it reached the end of the list. And then macro will save this file as text file to another folder.
So macro will go to beginning to open other file in the folder and this will go on till last file in the folder.
This is the logic of the macro. Here is the sample of what I want.
Original Data: ....
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 RelatedI'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 RelatedI 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
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.
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.
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
I'm Importing Columns from one sheet to another in the same workbook. imports good But:
1- Some columns with first records/rows blank do not import at all.
2- I have a column with some letter/numbers combinations and single numbers at times. Those single numbers are not imported. And half my data in that column are single numbers.
I have the following code for importing a query from Access to Excel.
Sub LoadAdditionalData()
Application.Run Range("AUTOSAVE.XLA!mcs02 .OnTime")
With ActiveSheet.QueryTables.Add(Connection:= Array(Array( _
"ODBC;DBQ=S:NBHD_RENEWALDatabaseNR DB_current.mdb;DefaultDir=S:NBHD_RENEWALDatabase;Driver={Driver do Microsoft Access (*.mdb)};" _
), Array( _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _ ...............
I currently have filters set to 990 (finance code) and 3 (quarter) but I would like to be able to enter these parameters myself everytime the macro is run.
I have a text file with no discernable format ( can't import into excel) that is too large to put all the data into an excel worksheet. This file is made to print out on a network printer.
I don't need all the info in the file, I would like to specify a variable, search the text file for the variable, then specify the amount of rows down to look for the data value and input the result into an excel spreadsheet.
I am wanting to use Excel to perform a web query into an online database our company uses. One of the columns I am wanting to import contains numbers that are 20 digits long & they begin with 4 zero's. The problem is that when Excel imports the query, if I fprmat the cells & preserve the formatting, it cuts off the first 4 zero's & replaces the last number with a zero. Basically, it looks as if it is only importing 15 numbers. Is there a way around this ; is there a way to force Excel to import all 20 numbers? If I don't format the cells, it imports in the following format - 4.20017E+15
View 4 Replies View RelatedI am auto-importing statistics from the web via web query. This information imported has a number in the form of a decimal stated as either x.1, or x.2. x.1 represents x-1/3 (one-third) and x.2 represents x-2/3 (two-thirds). I need to use these numbers in a calculation after importing them, but using x.1 or x.2 obviously does not give me accurate results as the numbers should actually be x.3333333 and x.6666666 respectively. What is the easiest way to convert the imported numbers to their actual decimals?...
View 2 Replies View RelatedI have a workbook with many spreadsheet named Sheet1, Sheet2 and so on. Each sheet is filled completely upto 65536 rows. This data is being picked up from a CSV file. In this file there are sites with each site there is a assciated set of data. What happens is the data that extracts data does not differntiate between sites and when it reaches to the end of worksheet it splits the data into next sheet. So I am trying to create a macro to check each spreadsheet starting with the last sheet in the work book for example last sheet in the workbook is seven it should go to sheet6 and if there is a blank row after row 64000 it should cut all the rows and move them to sheet7. Then it should goto sheet5 and do the same and keep on doing it until it reaches sheet1.
View 9 Replies View RelatedI have an existing application that uses DAO sql queries to populate a form. It has always worked fine. Earlier this week, I password protected the spreadsheet. Now my DAO query fails. I get a run-time error '3161'. Could not decrypt file.
' Const stExtens As String = "Excel 8.0;HDR=Yes;"
Const stExtens As String = "Excel 8.0;HDR=Yes;PWD=money16;"
Private Sub Execute_Query(SQL)
'Instantiate the DAO objects.
strDb = ThisWorkbook.FullName
Set DAO_ws = DBEngine.Workspaces(0)
' code fails on the line below
Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens)
Set DAO_rs = DAO_db.OpenRecordset(SQL, dbOpenForwardOnly)
End Sub
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
In order to get inventory data from our Incode database, I've been creating an output file with the extension .IRP Thinking that excel would not bring in this data from the .IRP file directly, I would then create a txt file of it with notepad. I've recently discovered that I can bring it in directly from the .IRP
The file being created is in this format:
IN Stock Status Report - 6385.IRP
The number portion, 6385 in the name will vary everytime I generate a new report. However the text portion will not.
Currently, I've recorded a macro the bring in the data to my excel spreadsheet.
But it will only bring in data for the set filename.
There is an autofilter applied to worksheet A, I would like to transfer only the autofiltered range from worksheet A to worksheet B, a clear worksheet B statement would also help.
View 9 Replies View RelatedI'm fine with Web queries generally (I'm using these for returning options pricing data) and have set up the query with code to loop through the options I want the prices on, go get them, and format the data usefully on my side. No problem there.
To get a web query to work (I understand) it needs the exact web address to go to
http://finance.yahoo.com/q/op?s=AMZN
That's pretty easy. However the page I'm trying to source data from doesn't change at all: it's https://us.etrade.com/e/t/invest/quotesandresearch
I can't see how to now 1. enter the symbol (e.g AA), 2. choose a dropdown option (i.e Greeks) 3. choose a second dropdown option (i.e All Strike Prices) and get the data from the table.
I need to create a file on another computer system that I can import into specific cells on specific sheets (to fill out a "form" required by a vendor). What instructions/macros can I add to the download file to specify the sheet/cell that a value should be loaded into?
View 2 Replies View Relatedi have a question regarding the import of csv-files. I've seen lots of working import-functions/macros but none of them. So here are my questions:
1) is it possible to import data from a csv-file into specific cells in my worksheet? Let's assume i have a csv-file with 10 INT-Values and they all should be placed in different rows/colums with no regular pattern.
2) If the answer of "1)" equals "yes": how is it possible?
(i thought about importing the csv in an so called "helper-worksheet" and use formula/references inside the table, but to be honest: That's not the solution i like to have)
I'm trying to import a range of cells from one workbook to another. I've got this far with it but am getting a Runtime Error '1004'.
Sub ImportData()
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
[Code].....
I have working code to copy the matching records of an Access table into an Excel worksheet, but now I am trying to modify this code so that I can select a couple of specific fields from the table instead of just copying all of the fields (there are 15 fields in the table, but I only actually need 4 of them for this particular report).
Here is the code I am currently using to copy all of the fields over with the matching records:
Const myDB = "DSD Errors DB tester.mdb"
Private Sub CommandButton4_Click()
' Test Field Select button
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String
sSQL = "SELECT * FROM DSD_Invoice_Requests WHERE `Paid?` IS NULL"
How can I modify this so that I put specific fields from the table into columns in Excel that will not line up with the layout of the table? For the sake of the example, let's say that I need fields 2, 4, 6, and 8 of the table to go into columns 1, 2, 3, and 4 in Excel.
I can't figure out how to delete the attached files that are irrelevant and I can't figure out how to upload new ones. I also don't know how to word what I need to happen
I want the data from everything, starting from row 5 and going to row 100, and not include column B or E.
How do I grab just that data? I have different formulas in each document. This is for invoicing, so the columns with the $ numbers can't be changed and I haven't been able to figure out how to not overwrite them when importing another spreadsheet.
I have the attached xl. I want to make import from a specific folder other xl files and after that I want the sheet 1 to find if the values in column g and m (together in the same time) can find it in other xl files. If yes then this values in sheet 1 must turn to red. Can this done?
View 1 Replies View Related