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
ADVERTISEMENT
Jan 20, 2012
So I have a few thousand .html-files that i need two lines from. It is line 43 and line 46. I want those lines in each cell and the dokument name in the last cell. After that it goes 1 row down and takes the next html-file.
So it would be something like:
Row 1 | line43 | line46 | Dok1
Row 2 | line43 | line46 | Dok2
View 3 Replies
View Related
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
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
Jun 17, 2013
I have a folder with 8 files which have different names like John-s, Joe-K, Mary-j,....
There are 14 sheets in each file. One of the sheets in the files named Daily contain information in Range A5:G16 that I need to import from all 8 files into a separate file called Import.
View 4 Replies
View Related
Oct 25, 2007
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 Related
Jul 30, 2009
I've got a single folder for the year 2009 that contains multiple files, identical in nature but updated for each business-day. In other words, the same report is generated daily with updated info. The naming convention is the same for every file (i.e. Daily Net Debt Report 02.2.2009.xls)
My ultimate goal is to have a macro that is dynamic enough that if its run on any given day of the year, it pulls the data (specifically from the worksheet "Detailed Cash" cells C1:E26) from every file included in the folder and places it in a single worksheet in the master file. So, for example, data from 1/2/09 would go into cells A1:C26, data from 1/3/09 would go into cells D1:F26, etc...
View 7 Replies
View Related
May 9, 2012
My application has a information that is constantly displayed to operators. In one of the non-active sheets, is a log of what is happening and I need to export this log in a (prefereably) .html format to display on an Apache web server.
The monitored sheet always have to be visible, and I do not want any dialog boxes popping up, so the Sheets("log") has to be exported under this criteria... Only need the first 1010 rows exported to the html file.
Would all of that be possible?
View 7 Replies
View Related
Jan 29, 2008
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
View 8 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
Jul 17, 2013
I have several word Files which has standard letter format and contains some tables which needs to be extracted / imported to Excel sheet
The File name is "employee name" which also needs to be extracted , if possible in same excel file.
Attached is the sample of word file. There are 3 tables which needs to be extracted
I have several files and I want to avoid doing it manually.
View 2 Replies
View Related
Feb 21, 2008
I would like to create a macro that looks in a specific directory "h:data"
It finds the 6 most recently modified files in this directory (csv files)
Then imports these 6 files into seperate worksheets in the same workbook "sorter.xls"
View 9 Replies
View Related
May 13, 2008
1. I've recorded a macro that imports values from 6 different .csv files at once. The problem is that not all of these files are going exist upon import. Is there a way to tell the macro to only import if the file exists? And to skip if the import of that file if it doesn't exist? Right now the debugger stops the macro because of file not found.
2. I want to press a button on Sheet 1 that copies Sheet 1 into a new Sheet that's named with today's date. E.g. I press the "Archive"-button, the sheet is copied to a new sheet with the name "051308".
View 9 Replies
View Related
