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
ADVERTISEMENT
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
Jan 20, 2014
I have this example data range:
foo 3
goo 2
blu 5
I'd like to be able to chart this (clustered columns for instance) but when I delete value 2 from goo, I'd like the chart to skip that row altogether and not display "goo" and a blank space as its value. I only want foo and blu columns showing.
Is that possible?
View 3 Replies
View Related
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
Aug 13, 2013
I have a table showing accesssibility of given fruits in some particular shops. What I need is to list the fruits available in every shop right after the shop's name, but skipping any empty spaces (i.e. unavailable fruits. See the example, it's manually entered though).
View 2 Replies
View Related
Nov 15, 2010
This was so easy to do in excel 2003...However im now using 2010 and i cant figure out how to tell excel to ignore the empty cells for a scatter plot.
View 3 Replies
View Related
Jul 16, 2014
I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.
Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.
I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.
Is there a formula/script for me to skip the correct number of rows to the next data block?
I have attached my spreadsheet to this thread. I am using Excel 2013
FeS_Kinetics.xlsx
View 1 Replies
View Related
Jan 17, 2014
I am working in the attached spreadsheet. For anyone who does not wish to open the link, let's say these sheets are titled 'Sheet1' and 'Sheet2'.
Sheet2 looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
[Code]....
Now, how may I pull this data into a summary sheet (Sheet1) that skips over any instances where column A ("Year") is blank so it looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
2013 2 Pat
2013 5 Bob
[Code]...
Note: All columns are the EXACT same across each sheet(including Sheet1, Sheet2, Sheet3), if that extra piece of information works.
PS It is fine if, in order for a formula to work, I need to copy and paste the formula for the length of rows per each Sheet to consolidate.
EDIT:
So, in my industry, any summary-level data (shown on Sheet1) must have a linked reference to a particular sheet (Sheet2, Sheet3, etc.) so the user knows where the raw data came from. This is to ensure increased accuracy of data and minimize human error (like, if for example, I accidentally didn't copy and paste all the rows correctly).
I have uploaded the actual sheet to show what the data actually look like. I am trying to pull in 2013 data into my "Summary Page" and would like to have all the data linked to the '2013' sheet but skip any blank rows.
Here is my failed formula:
=INDEX('2013'!F$3:F$338,SMALL(IF('2013'!$E$3:$E$338<>"",ROW('2013'!$E$2:$E$338)-ROW('2013'!$E$2:$E$338)+1,ROWS(B$1:B1)),""))
View 14 Replies
View Related
Dec 28, 2011
I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
View 5 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
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
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
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
Jul 16, 2009
I'm currently working on a report and what I'm trying to do is get a Row of information to pull into 4 rows. My current formula looks like this:
=INDIRECT("'Paste SAP'!H"&IF(MOD(ROW()-1,4)=1,ROUNDDOWN(((ROW())+3/4),0)," "),1)
I change the bolded number to correspond to which row (1,2,3,0) but it's not functioning. I've done it with other but for some reason this one doesn't work. I've attached the template so you can see what it looks like. The problem is with the SAP Tab and the info from the Paste SAP tab.
View 2 Replies
View Related
Dec 21, 2012
I am trying to get excel to autocount starting in a7 and ending at a600, skipping 3 rows in between, and only gng from 1-10 then repeating and inserting a page break after the 10th line i have put an example in.
SAMPLE1.xlsx
View 11 Replies
View Related
Oct 30, 2013
I am working on an inventory sheet. Its probably only complex due to the amount of data. There are several hundred locations we service and these are in a single column, and then there is the equipment we use that is several hundred columns.
Each location uses 3 rows and the 1st needs the formula to equal 2 rows beneath it. i.e. cell E28 has the formula =E30. I basically need to fill down, but only put that formula into every 3rd row, as the two rows beneath this row need to stay blank, or show number i manually put into either.
The second part of this, is there a way once that is done, to just select those cells, and fill to the right all the way? Again, i do not need to fill the two rows under that one.
I have attached part of the sheet .....
The green cells are the ones that need the formula, and those will be copied down, and right. the others need to be left alone. I have been sitting here for an hour manually entering in the =XXX for each cell.
View 4 Replies
View Related
Jan 30, 2014
I'm trying to total up some rows that are put in a repetitive order repeat. I can do this, but the method I'm using is highly inefficient. Is there some slick equation I don't know about?
Example below:
Worker 1 - Double Time - each of these has 200 columns for different job codes
Worker 1 - Over Time
Worker 1 - Regular Time
Worker 2 - Double Time
Worker 2 - Over Time
Worker 2 - Regular Time
Worker 3 - Double Time
Worker 3 - Over Time
Worker 3 - Regular Time
...and so on to 1000 workers
Total Double Time - for each job code
Total Over Time
Total Regular Time
View 1 Replies
View Related
Aug 27, 2007
i am useing colmn a only for input,
starting from cell A5 to the end of A,
so as i input data starting form cell A5 the active cell automaticly moves to the next row A6, so i could input another number but after ten rows it should skip an extra blank row and start this all again
but thats not the only prob, starting from cell B5 should count every ten rows and give it a number starting from 1,2,3,4... and no number should be on the blank row.
a b
5 223 1
6 234 1
7 987 1
8 787 1
9 976 1
10 324 1
11 345 1
12 455 1
13 247 1
14 685 1
15 ......................
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