Importing Multiple Text Files Into One Workbook

Apr 26, 2007

I am using excel 2003. I have more than 500 text file which are result of some numerical analysis. I have another bunches of the same file number. I can record and play around with macro.

What I want to do is
-copy selected cells from imported text file in excel and paste in new or in first opened excel file. I have recorded macro for one file including importing from text to excell, copy and paste the selected file (look the macro below). But I have more than 500 files and I want to do the macro the same thing for each file in one excell file. Do i have to make one macro for each file??, that takes much more time than manual import and copy paste. The cell position and range to copy is the same, but have to be pasted in new row (in one excel summery file). The file name of each text file is different and all are in the same folder.


Sub text_to_excel()

Workbooks.OpenText Filename:= _
"I:ResearchVALERI_germinationvaleri_slopevaleri_slope_COREL_DHPoutputDSCN2589.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _ ..................

View 9 Replies


ADVERTISEMENT

Importing Multiple CSV Files Into Workbook

Dec 3, 2012

I have a workbook that contains over 100 worksheets with stock data and price information. It uses a screenscraper to update the workbook each day with the latest day's price, and then exports each of these .csv files into a local directory. The macro for this is working. In effect it is generating a price history file automatically in my absence for me that can be read by charting software.

What I would like though is a macro to loopthrough and backfill missing price histories on each worksheet. I have a source of .csv price histories already, but would like to avoid having to cut and paste each of the 105 files manually, as it may become a regular occurence.

Each worksheet that requires backfilling has the stock code in the cell "A2", so that can be used to search for the filename Range("A2") & ".csv"

This is as far as I have got - however it results in a runtime error (91) Object variable or With block variable not set, pointing to this line:-

VB: Workbooks.Open Filename:="D:FinancialData SheetsSpreadsheetsPension" & Ws.Range("A2").Value & ".csv"

VB:
Sub BackFillData()
Dim Ws As Worksheet
For i = 1 To 105
'Sheets.Add
ActiveSheet.Name = "Fund" & i

[Code] ......

View 2 Replies View Related

Importing Multiple CSV Files Into One Workbook

Jun 15, 2013

I am troubleshooting my macro that seems to cause a data shift with the Letter "F" when I import multiple CSV files into one spreadsheet. When I go outside of the macro and record a macro and import the CSV into a workbook it works perfectly fine. But there must be something in this code that is causing the shift:

VB:
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Public Function ChDirNet(szPath As String) As Boolean
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)

[Code] ....

I have attached an excel workbook that shows how the macro imports vs. a regular import. Why "F" is causing a shift in the data.

Data.xlsx

View 3 Replies View Related

Importing Multiple Text Files

Apr 16, 2008

This is a great bit of code (I found), to import multiple text files.

I have one small issue -

currently it imports with each file going to a new column, like this: text file1 A1:A4, txt file2 B1:B4, txt file3 C1:C4 etc etc

And eventually i risk running out of columns because I'm working with a lot of text files.

Can someone tell me what to do to modify this code so it imports like this? :
text file1 A1:D1, txt file2 A2:D2, txt file3 A3:D3 ETC ETC

.e.g. first file imports across row1, second file across row2 etc


Sub test2()
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 = "\BP1MELIS001SHARED_DATAEVERYONESolutions IntranetPriceMasterlogs" '

View 9 Replies View Related

Importing Data From Multiple Text Files

Nov 22, 2006

I have about 1200 text files with data regarding different machines.These file contain a line called 'Validation date" and also the programe number on different line.

I need a macro which will extract this date for each program number and write in excel in two columns like program number and validation date.

I have tried with some of the macro help available on this site,but I have to do this one at a time.The time required doing this way is same as going in each text file and copy/pasting in excel.Is it possible to automate this process.

View 9 Replies View Related

Importing Multiple Multi-Line Text Files

Apr 30, 2013

I'm trying to create a macro that will import multiple multiline text files.

I have tried another Macro from this site however that imports the each text file into one cell, but i need each line to be in a new cell and each file to be in the same sheet.

View 10 Replies View Related

Importing Selective Lines From Multiple Text Files

Jun 18, 2009

I have many text files basically with the same data from different dates. I am trying to select specific lines from each text, one that includes ".txt" which is the date, ex: "2008_308" and one that includes the word "PIXEL" and the following 5 lines after that.

I found a very similar post to mine and someone provided the script using VB for the question. It works, but it was only for one text file. I would like to use it for multiple text files. The files are all dated, i.e. "2008_308.txt". Although some of the dates are missing.

This is the script, from another posting, for importing selective lines from one text file:

Sub test()
Dim fn As String, txt As String, delim As String, a() As String
Dim i As Long, ii As Long, iii As Long, n As Long, x, y
fn = "c: est.txt" ' İChange here (File Path)
delim = vbTab ' İChange here (delimiter)
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Split(temp, vbCrLf)
ReDim a(1 To UBound(x) + 1, 1 To 100)
For i = 0 To UBound(x)
If InStr(1, x(i), "txt", 1) > 0 Then
n = n + 1 : y = Split(x(i), delim)
For ii = 0 To UBound(y)
a(n, ii + 1) = y(ii)..........................

View 9 Replies View Related

Importing Text Files

Sep 25, 2002

I have a whole lot (hundreds) of .txt files that contain fixed width data that I need to convert to Excel format. I can open each file and step through the Text Import Wizard, then save it as an .xls file, but I'm wondering if there's a way to convert all the files in a given folder from .txt to .xls without having to open each one while (here's the kicker) still placing the data between the spaces on the .txt file into individual cells and columns like the wizard does.

View 9 Replies View Related

Importing Multiple Txt Files

Feb 27, 2008

I have part of macro to import a txt file into excel but it is not working as it should. It is giving me a message "This file is not a recognizable format" but its just a normal txt file. Also after it gives me this warning it will open in excel and with every new txt file it gives me this warning and after it opens it.

The code I am using is:

Sub su()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
sPath = "C:Historical" 'location of files
ChDir sPath
sFil = Dir("*.txt") 'change or add formats
Do While sFil "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "" & sFil) 'opens the file
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited
' do something
sFil = Dir
Loop ' End of LOOP
End Sub

In this line it gives me Runtime error 1004 Method 'Opentext' of object workbook failed:
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited

View 9 Replies View Related

Import Multiple Text Files Into Workbook

Jan 23, 2010

I 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")...................

View 4 Replies View Related

Importing Very Large Text Files

Oct 28, 2008

So I am trying to use this VBA:

http://www.cpearson.com/excel/ImportBigFiles.aspx

It´s basically so I can important a very large log file, about 800,000 lines, many of them repeating, I want to import into Excel to better control the data.

I keep trying to import the data, and I cant get it to make me new sheets.

View 3 Replies View Related

Macro For Importing Text Files

Jan 16, 2014

Below is a macro I recorded for importing the text file AJ9285 into excel from an external location.

I would like to modify it so that excel will give me a prompt and I can type the text file name in.

Sub adt()
'
' adt Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;Z:AJ9285.ADT", _

[Code] ..........

View 2 Replies View Related

Importing Large Text Files

Feb 8, 2006

With reference to Importing large text file. i need to get large text files to automatically split across Excel sheets sheets while trying to Import. Can it be done only through Macros or is there any function in Excel itself.

View 7 Replies View Related

Importing Specific Sheet Of Multiple XLS Files Into One

Nov 17, 2008

I know similar questions have been asked before, but I couldnt work out how it is done.
Basically I have several xls files with the same name, eg. "test" with an alternating number at the end. 1 - ....

each of these workbooks contain several sheets but all books are the same just with different information on it. each workbook has got a summary sheet in it, i want to import all summary sheets into a single workbook called summary. but each sheet should be imported as in individual sheet.

View 9 Replies View Related

Importing Multiple .txt Files In One Excel Sheet From One Folder?

Sep 14, 2012

I have a folder in my D drive where I used to keep my Text files after downloading it from our support centeral. I want to Import all these Text files in one excel sheet in a way that data of each file got append in excel sheet below previous file data.I am trying to do this through FSO but unable to do the same.

View 1 Replies View Related

Importing Text Files - Large Number Of Rows Blank After Each Import

Apr 5, 2012

I have a log that I regularly use to import text files, after each text file import it leaves a large number of rows blank that I have to delete before I add in the next text file import.

Any VBA method to detect this and remove the blank rows so that I dont have to keep checking to remove?

View 3 Replies View Related

Add Another Worksheet & Continue Importing The Html Files Until There Are No More Files To Import

Jul 13, 2006

I'm using the following code to import thousands of html files into my spreadsheet. The code is working fine. Since I am importing thousands of files, when there is no more space on my worksheet, the code stops with an error message. I want to make this code add another worksheet & continue importing the html files until there are no more files to import.

Sub Master_Importer()
Dim I As Long
Dim strFilename As String
Dim strPath As String
strPath = "file:///C:/Documents and Settings/c/Desktop/New Folder/"
With Application.FileSearch
.LookIn = "C:Documents and SettingscDesktopNew Folder"
.FileType = msoFileTypeAllFiles
.Execute
For I = 1 To .FoundFiles.Count
strFilename = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "") + 1)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & strPath & strFilename _ .......................

View 5 Replies View Related

Save Ranges In Multiple Files To Text Files

Dec 21, 2007

I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.

Example:
12/20/2007
Your Name
123 Somewhere St.
Here, OH 45111
Home Visit

12/20/2007
Someone Else
345 Anywhere St.
There, OH 45211
Hospital Visit

View 9 Replies View Related

Convert Multiple Files To Text Files

Sep 26, 2007

I have about 100 Excel files in one folder that need to be saved as text files. They can keep the same name, but simply need to be converted to text files. I'd like to use VBA for this and I can't find examples that do exactly that...or ones that my limited knowledge can handle.

Excel files exist in C:Source and ALL of them should be saved as text files in C:Destination. Maybe there is an easier way, but I thought for sure there was a routine I could use.

View 4 Replies View Related

Copy Multiple Columns From Multiple Excel Files & Paste Into 1 Workbook

Nov 7, 2009

I have 8 different files all have a set of data in them

each one has a long list of (column a-n) however the number of rows change by date. I need each file copied into the finalfile.xls one after another. in the files that will be merged into the final file the final row i need copied is blank.
I have all the copy formulas and everything set, I just need a range to copy that automaticaly takes cell a10 to the first blank a cell from each file and pastes it in the finalfile.xls under the last paste so they dont over write each other.

View 9 Replies View Related

How To Get Multiple Cells From Multiple Files Into One Workbook

Jan 13, 2014

So, today my manager came to me and informed me that he wanted me to do a little data entry for him... WOW... So I have about 2000 invoices to go through(all excel files, in their dated folders... 12-2-2013(folder) and the filenames are 12-2-2013(1), 12-2-2013(2) etc.)

I need to take these individual files and have certain cells from each in certain cells of a new workbook for example...

file 12-2-2013(1)... I need cell C3 from that file to go to cell A2(and this will be the same for all the other files... 12-2-2013(2) cell C3 needs to be in A3 etc etc etc.. this is the sale date)
file 12-2-2013(1)... I need cell a15 and b15(they are both "stretched out" cells) to be put in C3 of the new workbook and so on.

is there an easy way/formula to do this all? I have to have this done by friday at the latest(tomorrow preferred @@) I have to input data for 7 cells...

View 1 Replies View Related

Append Text Files To Workbook

Oct 26, 2006

I want to open multiple .csv files from a single directory and append them to one workbook. The following code partly works, but appends only the first line from each file.

Sub GetFiles()
Dim w As Worksheet, fn As String, k As Long
Application. ScreenUpdating = False
Set w = ActiveSheet
k = Cells(65536, 1).End(xlUp).Row
If Not IsEmpty(Cells(k, 1)) Then k = k + 1
fn = Dir("*.csv")
While fn <> ""
Workbooks.OpenText Filename:=fn, Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False
Rows(1).Copy w.Rows(k)
k = k + 1
ActiveWorkbook.Close False
fn = Dir
Wend
Application.CutCopyMode = False
End Sub

View 3 Replies View Related

Importing Mutiple Files

Jul 14, 2009

Which is the best way import many (thousands of) files in text format into a single excel spreadsheet. Importing files with e.g. 50 lines to create 50 columns for rows for every file

View 2 Replies View Related

Importing And Analysing Files

Nov 6, 2008

We have an online system that generates xls dump files. These essentially contain a table with lots of information about every test case we perform. From this table I am interested in three fields: TestID, Location and TestCaseStatus. With these fields I need to generate : A drop down menu to filter by location
A table containing the count of each test case status for the relevant location. Every Row represents a TestID and the columns a status.

I was hoping you would be able to give me an idea of a good way to tackle this. My initial thought is that SQL maybe useful to import the desired table columns and then further filtering would have to be done through nested for loops. I'm sure you can suggest a better way though.

Also, if possible, I would prefer the raw data not to be displayed in my analysing spreadsheet - only the summary should be shown.

View 13 Replies View Related

Importing CSV Files In A Worksheet

May 25, 2009

The Excel 2007 help menu claims that you can open a CSV file directly into a worksheet but I always get all the fields clumped-up into the first column.

After many tribulations I was able to import it through the Data import menu (text).

Could someone explain what I am doing wrong with the first method described above. It appears that the CSV converter is not working properly on my computer.

View 9 Replies View Related

Save Workbook Of Imported Text Files As .xls

Nov 20, 2007

I am using the following code to import a number of text documents into the same workbook, on seperate sheets and then to save the workbook as an excel file. the problem is that when the file tries to save (red section) I get a message telling me that Excel can not save the workbook in the requested format, I think because it is still trying to save them as text files rather than as an excel workbook. When I try to save manually, selecting .xls as the format it works fine, but I would really like this to be part of the macro to ensure the WB is saved.

Sub ImportFiles()
Dim Sfile As String
Dim count As Integer
Dim ans As String
Dim A As Integer
Dim FileSaveName As String
count = 0
Application.DisplayAlerts = False
Do
' Allow user to select the file to open, text files only
Sfile = Application. GetOpenFilename("Text Files(*.txt), *.txt")
' Check that a file has been selected
If Sfile <> "False" Then......................

View 4 Replies View Related

Combining Multiple Excel Files To One Workbook But Different Worksheets?

Jul 17, 2013

I have five excel separate excel files containing values covering more than 500,000 rows each. I want to put then in a single excel workbook without tedious work of copy/paste to sheets of this workbook.

View 2 Replies View Related

Combine Multiple Files Into One Workbook (single Worksheet)

Apr 12, 2007

I've pieced together code from a number of solutions on the forum and figured out how to find the files I need to process, but can't seem to figure out the code to actually combine/append each worksheet into a single one. I've searched the "sea of options" on this forum and managed to do one thing well. Pardon all my notes embedded in the code below, but if I don't do it that way, I'll only confuse myself further (which is easily accomplished) ... or lose my notes!

I’m going to run this code from an add-in menu, which is already created and working (yeaah!) … so (I think) I need to have the code either create a file named "Master (Combined).xls" in the target folder ... or ... have a file already created in MYFOLDER and have this code delete all but the header row when the file is opened(???) The "Master (Combined).xls" workbook needs to consist of one worksheet named "Master". I want it to open the current workbook (.foundfiles(i)), select all the data, and append it to the master workbook on the "Master" worksheet (sheet 1). The Master Worksheet in the Master Workbook needs to start out as a blank sheet ... except for the header row. I can add code to put the headers in at the start, if necessary

Sub g_CombineMultWB_AllXLSFiles()
' This Will combine all XLS files located in the
' S:DMSMSPOMSMaster POMS NIIN DataMaster EMALL Data FilesEMALL Excel Folder
' into a single worksheet in a newly created (or previously existing) workbook
'
' LOCATION OF FILES (ACTUAL):
' S:DMSMSPOMSMaster POMS NIIN DataMaster EMALL Data FilesEMALL Excel Folder

Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
' Application.CutCopyMode = False ' DO I HAVE THIS HERE FOR A REASON???

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Const MYFOLDER = "S:DMSMSPOMSMaster POMS NIIN DataMaster EMALL Data FilesEMALL Excel Folder"
With Application.FileSearch
.NewSearch
' Change path To suit.............................

View 9 Replies View Related

Skipping Empty Rows When Importing Files

Oct 30, 2006

Is there an easy way to revise the following code so that it imports ONLY the non-blank rows from files?

Sub ImportFiles()
Dim i As Integer, files As String, text As String

ChDir _
"C:Test"

Cells.Select
Range("A1").Activate
Selection.ClearContents
'Selection.QueryTable.Delete
Application. ScreenUpdating = False
i = 0
files = Dir("*.csv")
Do While files <> ""
Open files For Input As 1
Do While Not EOF(1)
i = i + 1
Line Input #1, text
Worksheets(1).Cells(i, 1) = text
Loop

View 9 Replies View Related

Searching And Importing JPEG Files And Paths Into Excel

May 23, 2012

Am creating a large medical teaching database on Excel that lists JPEG images (a few hundred) and the file paths for each image into separate Excel columns using a VBA macro.

The JPEG image filenames are numbered in ascending numerical format (i.e 1.jpeg).

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved