i want to do is import multiple text files,with fixed arrays (luckily it's standar )
I have the piece of code that i currently automatically importing these .txt files,although it's for one per turn.Here it is though,to see the arrays
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
Is there a formula to look in multiple arrays and return whatever it finds i.e. text or numeric values?
Sheet1  ABCDEFGH1Cat0 CatFive Donkey32   Mouse2 Wolf43Â
4WolfFour CatFive Donkey35   Mouse2 WolfFourSpreadsheet FormulasCellFormulaB1=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,VLOOKUP(A1,$D$1:$E$2,2,0),
VLOOKUP(A1,$G$1:$H$2,2,0)))B4=LOOKUP(REPT("z",255),CHOOSE({1,2,3},0,
VLOOKUP(A4,$D$4:$E$5,2,0),VLOOKUP(A4,$G$4:$H$5,2,0)))
I have about 200 text files each with 2 columns. I want to create a single excel spread sheet where the data comprises of the second column from each text file. I do not know any thing about macros and so need help on writing a macro that will automatically import data from the whole of the second column of each text file into one single spread sheet.
View 7 Replies View RelatedI have about 10k poorly formatted txt files that I need to grab 4 items from each and put in a spreadsheet. How can this be done in Excel or should it be done in Access instead?
Here's a link to what each txt file looks like: MTRP88PF700721.txt
I only need the number next to Posting Journal on line 3 (12686 in this case), the 3 digit number next to Class Total on line 11 (101 in this case), the Extended Cost Final Total and the Extended Retail Final Total (23.77 and 39.30 in this case)
because one of my staff members is currently opening each txt file and typing these figures into a spreadsheet!
Need to import multiple text files in one shot. (Excel only allows 1 at a time)
Most file names are similar just numbered. ex. JoeBlowfile 1.txt, JoeBlowfile 2.txt, JoeBlowfile 3.txt, etc......
All text files have 30 lines of text
For example: JoeBlowfile 1.txt will start at A1 and end at A30, then JoeBlowfile 2.txt starts at A31 and ends at A60 and so on.....
May need to import 1 to 10000 files at a time.
all in 1 row single line starting at A1
once all files are imported need to export to 1 single text file file saved in XXXX folder with XXXX.txt name.
rearrange this macro for me that I could import multiple txt (200 files) files from selected folder. Could You insert appropriate comments in Your altered macro
- where I should change how many columns I need to import from files,
- which column/columns I want to import from files
- macro should automatically import data from the whole of the selected column or columns of each text file into one single spread sheet and import it to different columns
I have found this:
Sub test()
Dim myDir As String, fn As String, ff As Integer, txt As String
Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer
myDir = "c: est" '
i am looking for a macro that would import chosen multiple text files and append them all into one worksheet into column B (one textfile row into one cell), with column A displaying the imported filename next to every entry.
View 5 Replies View RelatedI found the code below on the internet and adjusted it to my needs.It is working perfectly fine except for the fact that it opens a new workbook to import the data. I run this code from a workbook named "InstronImport.xlsm" and would like it to add the data in this workbook instead of a new one. I have been trying for a day now and can't seem to figure out how to do it.
Sub ImportRawFilesInstron()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String
On Error Goto ErrHandler
Application. ScreenUpdating = False
sDelimiter = ","
FilesToOpen = Application. GetOpenFilename _
(FileFilter:="Text Files (*.raw), *.raw", _
MultiSelect:=True, Title:="Text Files to Open")...................
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 RelatedTrying to import mutliple text files from one folder and save them into multiple tabs in the workbook. Found this piece of code on forums but can't respond to thread since its expired. Whenever i choose a file from the folder, it would say no files exist. Is there something wrong or am i missing libaries to run this?
Sub Test()
'First off, this will prompt where the text files are saved
filepath = Application. GetOpenFilename("Text Files (*.txt), *.txt", , "Where are your text files saved")
'this will strip the filename from your selection, leaving just the folder
Do While Right(filepath, 1) <> ""
filepath = Left(filepath, Len(filepath) - 1)
Loop
'This will search for all of the files within the folder
Set fs = Application.FileSearch
With fs...........................
Import Mulitple Single Column Text Files
The code by Jindon works but only for the text files that do not contain a header. Can someone please telll me what I have to change in the code to make the code skip the header information (1st three line) when importing? Currently, if I use Jindon's code, it fails once it tries to import one of these text files. Here is Jindo's
Sub test()
Dim myDir As String, fn As String, txt As String, x
myDir = "c: est" '<- change to actual folder path
fn = Dir(myDir & "*.txt")
Do While fn <> ""
txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Application.Transpose( Split(txt, vbCrLf))
Sheets(1). Range("a" & Rows.Count).End(xlUp)(2).Resize(UBound(x,1)).Value = x
fn = Dir()
Loop
End Sub
I am new to VBA coding. My boss recently handing me a task where I have to take data which is stored in thousands of fwd files and import it into spread sheets. This can be read with notepad as plain text. This data has about 35 lines of heading info i need to skip. The data i need transfered is stored in columns and each data row is tagged at the beginning with the letter D and comment lines begin with the letter C. The heading info has no such tags.The data is space delimited. Further, some of the columns of data need to be omitted in the spreadsheet. Each data.fwd file gets its own spreadsheet meaning each time i import data i start with a fresh template.xls to import into. The format of the spreadsheet is the same as the format the data is presented in the text file with ten columns, except the text has twelve and two of them need to be omitted.
One more thing, I need to be able to push a button and query the file to import.
I am trying to an excel macro that will automatically import data from specific files. The basic information is this:
1. There is a MySQL script that runs every day and creates tab-delimited text file named as the date.Example:
OutputDIR
02-14-2014.txt
02-15-2014.txt
02-16-2014.txt
2. I need grab data from each of the files for a rolling 30 day period from the date specified in the sheet. The data needs to go onto the same sheet in Excel.Example: B2 on the "Settings" worksheet says 02-14-2014. So take data from 02-14-2014 minus 30 days (01-15-2014.txt) all the way up to today and consolidate it onto an existing worksheet called "Data."
3. If the date in cell B2 is changed, overwrite the data that was already retrieved and replace it with the new import from the new date.Example: B2 is changed to 01-31-2014. Now take data from 01-31-2014 minus 30 days and overwrite what was already imported in worksheet "Data."
I have many text files ~5000 and I am searching for a macro that can import multiple files and also search the text file only importing two specific rows (error files may will not have 42 rows) while listing the file name in an adjacent cell.
So in column A I would like the file name, in column B any data in line 42 of the text file and in column C any data in line 43 of the text file.
Here is an example of the data in rows 42 & 43:
11 Waratah Street Mona Vale(2103) - Australie
-33.68 (-33°40') | 151.30 (151°18')
So far I have found the code below that will import all text files into one sheet but it does not satisfy my requirements.
I am a complete novice with VBA and would like to write a macro that will allow me to import multiple text files stored in a folder on my directory into a single excel worksheet. I would also like the name of each text file to be output in a separate column for each row of data in the worksheet i.e. each row is 'named' with the title of the file it comes from.
The text files are comma delimited and have 5 fields e.g.
.000071 ,93018.3,53.3583924, -6.3578328,Differential
Import Multiple Text Files & List Names
I would like to use FileDailogFolderPicker object to prompt the Excel User (me!) to browse to the location of the folder which is required to execute the code below; at the moment I have to change the file path in the VBA code each time I run the macro. The code is run on a Click Event button at present.
Private Sub GetGPCFiles_Click()
test
End Sub
Sub test()
Dim myDir As String, fn As String, ff As Integer, txt As String, a()
Dim x, i As Long, n As Long, b(), t As Long
myDir = "Y:DCCRSP07RSPWE191007131007GPC" 'Have to input this path manually
fn = Dir(myDir & "*.gpc")
Do While fn <> ""
ff = FreeFile
Open myDir & "" & fn For Input As #ff
Do While Not EOF(ff)
Line Input #ff, txt
x = Split(txt, ",")
n = n + 1
Redim Preserve a(1 To n)
a(n) = x
Loop
I get about 10 text files at the beginning of each month that I need to import into Excel. The file layout does change slightly from month to month so I have an Excel spreadsheet with the layout as the first worksheet. Each text file is in fixed layout form rather than delimited. The structure of that page is that the name of the first text file is in cell A1. The cumulative field widths are below that and the data formats are next to the field widths (in the adjacent column “B”). T=Text, G=General, D= Date with 3 flavors of Date: YMD, DMY and MDY. For the second text file, it repeats the structure in columns “C” and “D”, the third text file in columns “E” and “F” etc. What I would like is for the macro to open the first text file (based on the name in cell A1) and import it into a new worksheet (same file) using the cumulative field widths in A2 through A8 and the formats from B2 through B8, then import the next text file to a new worksheet (same file) using the cumulative field widths in C2 through C8 and the formats from D2 through D8 and keep going until all the files have been imported. The sheet names should be the same as the file that is imported (the “.txt” part of the file name isn’t important). I’ve attached a copy of the Excel spreadsheet that has the file names, field widths (cumulative) and field formats and a 2 row sample of the text file for the first import.
View 6 Replies View RelatedI am trying to determine a way to quickly import data from text files into Excel and place data in suitable columns (under correct headings). I am thinking I could be asked which file to read and import doing them 1 by 1, or if there is an automated way to cycle through all the files that would be more efficient (filenames are variable).
From the text files I have attached I can tell the column headers and what data should go under each. Not sure how you would describe the delimiting on these files? Are these files in a format that VBA could be used to reduce manual copy and paste approach? I have about 300 of these files I want to extract the data from.
Note: the attached files are from a public access website.
WELLS0214.TXT WELLS0106.TXT
I have around 50 text files with similar design per attached file. I need to import the text files with criteria below:-
1. include file name
2. let user choose the folder
3. exclude data from "work in process summary" to "work in process cost totals"
4. only have one title in the excel files which all text files is combined "Item, Line ....."
5. If the text files do not have title like "Item, Line, ..." do not import
Is it possible to have all criteria listed above by running a macro?
A.txt
I am importing a text file that has a lot more than 256 columns. The data will be updated weekly with a different text file. I know how to get past the 256 column limit when importing a text file like this. This link explains the method I am using to import the text file. [url] Since I am importing onto several sheets I get the dialogue box asking me to choose a file to import from for every sheet when I use RefreshAll. The same file that is used by the first sheet will be used by the rest of the sheets. Each sheet just imports a different section of columns from the text file. I want to avoid choosing the same file 30 plus times (once for each sheet) when I click refresh all. Is there a code that would allow me to choose the file for the first sheet, and the other sheets would follow suite?
View 9 Replies View RelatedI found following code on this site to extract txt files. As mentioned this code can extrack huge txt data to multiple sheets.
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.
'If a file in a different format is to be imported amend the extension at line 10
Dim strFilePath As String, strFilename As String, vFullPath As Variant
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object.............
However I am getting Run time error '-2147467259(80004005)': could not find installable ISAM for section 'Open an ADO connection to the folder specifiedin above code
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
I have two column on two seperate worksheets. One worksheet has both columns filled out. On the second worksheet, I want to be able to type in info in once column and have excel pull the other column from the other worksheet.
WKSHT 1 WKSHT 2
ID Name ID Name
1 Bill 3 ?VLookup?
2 Steve
3 Mike
Ive tried using Vlookup('worksheet1'!A2:A4,'worksheet1'!B2:B4,2) but can not get it to work.
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))
I have attached a sample from aspeadsheet I am working on. The option reply sheet has '1' entered if the student has opted to take this course. On the other subject sheets I would like the names who have opted for that subject to appear. Stuidents can opted for multiple subjects. I have tried Match and Index functions but it is difficult as students can opt for more than one.
View 1 Replies View Relatedwhat I'm looking at doing is counting the number of reccuring unauthorised absences in a list. We have the persons name in column B, the absence type in column C and the date in column E. Basically I need to send out an AWOL notice when 5 days of unauthorised absence for the person in column B has occurred, so I need some sort of indication that this has happened in order for me to stick some conditional formatting in there to flag it.
View 9 Replies View RelatedI'm trying to create a template that will be able to return a sales persons call target based on the category they sell and the current level they are. It works when I only ask it to look for one or the other, but I'm getting stuck trying to make it use both. I've attached a small sample. My original equation is as follows
=INDEX($C$2:$Q$51,MATCH($B2,$A$2:$A$51,0),MATCH(G$1,$C$1:$Q$1,0))
and this works perfectly fine. The problem I've now got is I need to add in a second criteria for setting the row_num. I've tried the following but I get a ref error. =INDEX($C$2:$Q$51,AND(MATCH($B3,$A$2:$A$51,0),MATCH(D3,$B$2:$B$51,0),MATCH(G$1,Telesales!$C$1:$Q$1,0)))
I have a procedure that I want to pass two arrays to. When I pass a single array it works fine but as soon as I try to pass two arrays I get a syntax error. Both arrays are declared exactly the same way and are used in the same way. Am I limited to passing only one array per procedure?
View 8 Replies View RelatedI need creating a formula for the following situation.
I need to link multiple arrays from different sheets. But am having a hard time concocting a formula that will work.
I have 2 columns of data on sheet1. For example:
A-------------B
apples-----1245
oranges----456
nuts--------384
etc.
Now, I need a formula that will sum the entire column B, by looking up the array of column A on another sheet and only summing the numbers on sheet 1 that also have a value of "West" on sheet 2. Sheet 2 looks like this:
A-------------B
apples------West
oranges-----East
nuts---------West
This needs to be functional as a template to be used each month - the entire point of the formula is to save time and eliminate the current (and lengthy method that is in use). I can tweak the formulas as needed. But am trying to avoid having to concatenate anything or do any modifications to the format of the sheets.