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"
I'm trying to automate an Excel macro. I need a way to select the latest .txt file from the Import Data dialog box.
I currently have a "CHDIR" at the beginning of the macro that sets the active directory. When I make the "Import Data" pick I am in the proper place. Now if I can only sort the list of .TXT files by date, select the first file in the list and hit ok to continue I'd be off and running.
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
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 _ .......................
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".
I need to import about 1500 HTML file into a DB (SQL Server). But (here is the big problem), I only need to import some data of each HTML file (always the same line/variable).
So I thought using Excel as you can say with part of the HTML file you want to import. It works great.
But I did it with one file. Is there a way you can select a whole directory and it does it for you?
I have a question regarding importing data. What I need to do is specify a certain directory and this directory contains multiple Excel files. I would like to open each file and extract certain data from each workbook. I should be ok to figure that part out (the data extraction) but I am really confused on how to open each excel file in the directory.
View 3 Replies View RelatedI import into my website a standard .csv file that contains product details from my supplier. Columns contain info such as RRP, Wholesale Price etc and rows for each product. For some reason thats only known to my supplier the text descriptions of each of the products are held on a seperate .txt file, in a separate folder with the .txt filename recorded one of the columns cells, one for each product. There are 1000's of products, each with its own .txt file such as 38675.txt, 37828.txt. As you can imagine to copy and paste into each products cell or to manually import each .txt will take the rest of my life.
how I could automate the input of the diferent .txt contents into the required cells.
I am trying to import a several files depending on date. The first part of the name of the files that I am importing are always the same, the end changes as the date changes.
I have a user form that comes up and I pick the dates that I am looking at.
What would be the code to import those depending on the date that I pick?
When my UF comes up, I pick whatever dates, I need to import all the files in between those dates.
I need to import a number of log files into a spreadsheet, each log will go into a separate tab. The logs are text files (.log) that have the same structure but slightly differ from each other.
For example all the logs will have many lines of data all starting with a date and then a number of delimited fields. In some logs the delimiter is a coma, in others is a unique charachter such as "{" or "@". Also the delimited fields are not a given quantity, they can vary too from log to log. How can I write a macro that understands what delimiter needs to be used and then imports all the file accordingly into one spreadsheet (in separate tabs)?
I tried something with the macro recorder and browsed for directions on the internet but really am clueless on this one. I am using windows 2000, and referenced Windows Scripting Runtime.
I'm looking for a macro that will import multiple .csv files from c: emp each into their own separate worksheet.
View 5 Replies View RelatedSo 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
I've got 80 CSV files that I need to import into an Excel sheet. The filenames are sequential, eg 1APN .... 80APN). I need each chunk of data added after the previous. How can I do this using VB?
View 2 Replies View RelatedI am using the following to import a text file w/ ~86000 lines into Excel: ...
View 9 Replies View RelatedI am trying to write a macro which creates a new worksheet with the name kenmerk (i) and then import five different textfiles in this worksheet.
I was wondering how to define the name of the textfiles in the code. The code I get with record macro is: ...
My text files look like this(each has different number of rows):
2006-08-25 13:33:20 82,8 g
2006-08-25 13:34:10 58,5 g
between each column there is Tab
To import them from one directory (directories are named as month e.g. 8) I used:
Sub Consolidate()
Dim mnthNum As Integer
Dim myBook As Workbook
Dim myRows As Long
With Application
.DisplayAlerts = False
.EnableEvents = False
. ScreenUpdating = False
End With
and it works almost perfectly.. the problem is that each time it loads next file it overwrites the previous..
e.g. if i have in my directory 4 files and sum of the rows is 20 it will show only 17. The import is good because I checked it with debugger but each time a new file is copied it starts from last row of previous file.. that's the problem I can't solve...
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 five different formats of data files i.e. different columns in count as well as heading. i want to import selected six columns from every file in a single sheet for data manipulation. can this be done anyways. The format for result file is standard with six columns only.
View 4 Replies View RelatedI have around 200 files, all named from 001.xls to 200.xls, all containing the same format, the same number of colums, but a different number of lines.
Now I have the great task to actually open all these files and copy each files data into one file and start analyzing it. Well, since I personally have no interest to open, copy and paste 200 hundred times, I am wondering if there is any faster way to let excel automatically copy the data into this master file?
I am thinking about somehing like *='001.xls'Sheet2!B2:E20*
One thing is that each file never has more than 20 entries, thus i would only need excel to import the from line 2 to 20 from a certain file and then do same thing all over again with the next file.
I am trying to do a mass import of several thousand excel files into one file. The headers of all the files are the same, but the data inside each file is different. I thought about using Access but it wants me to import each individual one.
I have zero experience in writing a Macro
My associates have saved about 2,000 .zip files in a single repository. Each zip file contains a .pdf, and a file called "metadata.xml" - the metadata files are small, only 1-2Kb.
What I'd like to do is import the contents of each metadata.xml file into a single workbook so I can build an inventory of the pdf files.
The full path looks like this:
Z:PrincetonGlobal DataFinancial DataFinancial DataWCFNDL_PRODUCTION128650_TH1X0_ProTechLLC_A_Eng_BBOT__20140317132245.195_bbot.zip
Everything up to and including FNDL_PRODUCTION is exactly the same for each file. Everything after, varies, and is not predictable.
I'm thinking there is some way to say "for each file in repository, import metadata.xml content..."
-I receive files everyday by email and saved in a folder C:TEST
-each file saved by name Name+daily date
•( like: sale_20091214, sale_20091215, sale_20091216 )
-file is in csv/Comma delimiated format
-each file have 7 columns A to G and contain 256 rows
-i only need 7 or 8 rows & only need value in Column 'F'
-the rows i need are going to be same each time
--------------------------------------------------------------------------
I want to save automated data in a file name: MonthlySale located in c:
Fore each ROW data should be saved in new column next to last saved column.
.
Example:
data from file sale_20091214 should be saved in column B,
sale_20091214 should be saved in column C,
sale_20091214 should be saved in column D and onward.
I found a good piece of code to import data from text files into excel. they are delimited
I keep getting an overflow error, and then I get the error that the file is already open?
Option Explicit
Sub OpenTextFiles()
Dim strFiles() As String
Dim strFName As String
Dim strFPath As String
Dim IntFile As Integer
Dim sep As String
'define the directory
strFPath = "C:UsersXXXXXDesktopHOLDINGTEXT into Excel"
[Code] ..........
I 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.
I have some 400+ .txt tab delimit files in the same format in the same directory. All of them have 4 cols. I would like to use a VB sub code to import col1's of all .txt files to a workbook sheet1 filing from col1 to coln in excel sheet1. And col2's of all .txtx files to sheet2 of the same workbook. An so on. I found a code that can import the col1's from .txt files to excel sheet1 only. But not col2's to sheet2.
View 6 Replies View RelatedI have something like 10 folders. In each folder, I have 300 .xls files and 300 .txt files which have the same name : REF01, REF02, REF03...
Each time, I have to import the .txt file in the .xls file which has the same name in the same folder. I have some other operations to do : filter, etc...
I registered a macro to automatically import the .txt file and do the operation. But, the "register macro tool" has a problem : I asked him to import REF01 during the register, and now he is stuck to this file and import it every time I execute the macro. So, he imports REF01.txt in REF02.xls, in REF03.xls, when it should import REF01.txt in REF01.xls, REF02.txt in REF02.xls...
I don't know a thing in VBA. i tried to study the code but didn't manage to improve it. Here it is :
Sub BESTBESTMACROEVER()
'
' BESTBESTMACROEVER Macro
'
' Touche de raccourci du clavier: Ctrl+u
'
Sheets("Feuil2").Select
Sheets("Feuil2").Name = "Anerorpoin"
[Code] ......
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")...................
