Free Microsoft Excel 2013 Quick Reference

MACRO to merge multiple csv files into a Master Excel Workbook

I have the following situation. I have 20 files that update daily. The files are csv. The files update with a new row of data that goes below the previous days data. I need to then take each csv file data and paste it into a Master Workbook. Currently, I open each csv manually via the get text button and input into master Workbook. I can't figure out how to have a macro open up the 20 csv files automatically and input data into the Master Workbook. Thank you


Hi everyone. I'm just wondering if any of you know of an add in or application that will allow me to import multiple CSV files into a single Excel workbook. Basically, I have a bunch CSV files and I want them in separate worksheets in a single Excel workbook. Any help would be much appreciated. Thanks!

Is there a way to open multiple CSV files into the same excel sheet? It's only helpful if it is quicker than opening individually and copy/pasting. I need to do this daily.

Ideally, I'd like to highlite the 14 files, right click and have an option right there. Is that possible? I would like to avoid using access and keep it simple...

I have some moderate excel programming experience so any amount of detail/insight would be much appreciated.

Hello everyone!

I tried many ways to convert a CSV file into a formatted Excel (.xls) file via VBA. I have a file with 5 lines (header included) and about 10 columns (delimited by commas). How can I format it via vba on button click action? Does anyone happen to know?

Thanks everyone!

Hi All,

I dont have much expertise in VBA but trying to learn.

I am trying to import multiple csv files into excel. I have a macro
from this forum to import the files one by one. But I want to be able
to select and open the multiple files at once. Also with the macro that
i have, the names of shees are coming like Sheet1, Sheet2 etc. I want
to retain the names of the files for the sheets if possible.

Many thanx for your efforts.

Appending the macro I am using. U can suggest completely different
approach If you think that open dialog box is not best.

Using Excel 2003. Not much expertise in VBA.

Thank You,

Santosh

Sub ImportTextUsingXlDialogOpen()

' use this approach to prompt user for text wizard values

Application.ScreenUpdating = False
Application.DisplayAlerts = False

' display open file dialog and copy to new (temporary workbook)
'On error resume next - traps for user clicking cancel in the
import dialog
'Error number is 1004 - is so exit the procedure
On Error Resume Next
If Application.Dialogs(xlDialogOpen).Show("*.csv") Then
If Err.Number = 1004 Then
Exit Sub
End If
ActiveSheet.UsedRange.Select ' select imported text in
temporary workbook
Selection.Copy ' copy to clipboard
ActiveWorkbook.Close ' close temporary workbook
Worksheets.Add
Range("A1") = "Dialogs(xlDialogOpen) Method"
Range("A2").Select
ActiveSheet.Paste ' paste text into your
workbook
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

I need to be able to combine multiple csv files into one xlsx file. The csv files have 6 standard columns but I need to add a column when combining to represent the file name when combining because I need the filename has unique information for each file.

I have found a macro to combine the csv files into one xlsx file but I do not know how to append the filename of each csv into a column.

Csv sample
a,b,c,d,e,f
11/2/08,08:08,c:temp,AVD123,AVDDC,6.7
11/4/08,12:15,c:temp,AVD123,AVDDC,8.1

john.smith csv file

Csv sample
a,b,c,d,e,f
6/2/08,08:08,c:temp,AVD123,AVDDC,6.7
6/4/08,12:15,c:temp,AVD123,AVDDC,8.1

bob.jones csv file

After combination
Column A Column B Column C Column D Column E Column F Column G
11/2/08 08:08 c:temp AVD123 AVDDC 6.7 john.smith
11/4/08 12:15 c:temp AVD123 AVDDC 8.1 john.smith
6/2/08 08:08 c:temp AVD123 AVDDC 6.7 bob.jones
6/4/08 12:15 c:temp AVD123 AVDDC 8.1 bob.jones

Hello all
I’m looking for a macro that will import multiple TAB files into a single Excel spreadsheet from a folder on the desktop while retaining the selected formatting of the sheet titled “IN Import”. Each TAB file has a heading row with zero to 10000 matching data rows. Therefore, each subsequent file needs to be appended to the first. There are from 1 to 1000 TAB files with random names that will be replaced on a frequent basis. The macro should probably be versatile for TXT, TAB or other different file formats just in case the source for the files changes its format. It would also be nice if changing the delimiter on the imported files was an option as well. This appears to be a fairly simple VBA/Macro loop process but I cannot seem to figure it out. The best I could get to was an import that appeared intact at first glance but was all in A1 to A### and gibberish on the some of the cells. I am not including any of the codes I have because I do not want my errors to cloud what I am looking for represented above…Any help in this matter would be greatly appreciated……..Delta

I am creating variable amount of XL files using Access VBA (2207) and output into a directory. Each XL file has a single sheet with different columns. I wanted to copy each sheet from all the XL files in the directory into a master XL workbook with multiple sheets so there is only xl file. How can I do this in Access VBA? There is a code in the following link..but when I run that I get runtime error 2302, subscript out of range. Thanks for your comments.

http://www.ozgrid.com/forum/showthre...t=16343&page=1

I need to copy multiple .csv files into Excel so that i can analyse the
information. The .cvs files are on CD, there are many of them and i do not
wish to open each one and then have to save them. Any clues?

I am looking for a way to combine multiple XML files into a single spreadsheet. Each XML file when viewed in Excel will have the same header and a single row of information (mainly text based).

It is possible to import these XML files into a spreadsheet but it will only allow me to do this one file at a time. At this point we do not know whether we will be dealing with 10 files or 100.

I only have access to Excel 2003 and do not have access to the command prompt, a SQL server or the option to download an add on. I have the option of using VB but no real experience of how to fix it. I tried amending some code from here but I kept running into errors when running it.

I tried using the Data > Consolidate option but it didn't appear to work as I had hoped. The contents of the XML file are completed fields from a form and are mainly text strings.

Grateful for any help/suggestions on whether this is possible.

I need to import numerous .csv files into a single Excel spreadsheet.

Currently I open the .csv, press Alt & A to select all data and then copy / paste the date into the spreadsheet

Is there a method to drag & drop .csv files into a spreadsheet?

Thanks

I know there are ways out there somewhere but im having the hardest time finding it.

I have multiple CSV files.
All CSV files in the same folder.
All with different file names
All with a single tab in them, the tabs are labeled with the file name. (ABC.csv has a single tab in it called ABC).

How can i merge all the CSV files into a single XLS file where all the CSV files are on their own seperate sheet tab?

I couldn't find anything on the search option so I've decided to ask directly! I apologise in advance if theres something already on the Boards that i could use!

basically I'm trying to get a CSV file from a unix output into an Excel document in about 5 different sheets in certain parts of the sheets.

So I need to transfer the CSV file into the sheet using a macro
Then (or at the same time) shift the data into its individual parts of the spreadsheet, i'm guessing using an IF statement of some kind!

(each section of data is separated by a H*top of the file* and a T *for the bottom of the file*)

Any help would be greatly appreciated :D

I’m trying to collate three differently formatted .csv files into one
workbook. But in order to get the latest data in my master document I still
have to open the three source files which sort of spoils the point of the
master doc. I don’t know if this is because:
1. I have set the book up wrongly
b. I have stored the source files in the wrong place
iii. am forced to leave the source files as .csv
100. A.N. other reason.

Any suggestions on how to resolve this?

In Excel from the Open File window I want to select multiple csv files and
have them load into individual worksheets (tabs), not individual workbooks.
Does anyone know how to do this? It would be great if there were a check box
option on the Open File window to select individual files (workbooks) or
individual tabs (worksheets in a file).

Howdy all,

My goal is to insert data from a csv file into a new worksheet. While I've got a macro that can manage that part, it will only load one file at the time. Other snippets of code I've run across will load everything in the directory, but I want to restrict it to a list of filenames that I specify in a worksheet (i.e. Sheet 1, A1:A?).

Any help you could offer would be greatly appreciated.

Thanks,
Joe

Sub Macro3()
'
' Macro3 Macro
'

'
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:datadata1.csv" _
        , Destination:=Range("$A$1"))
        .Name = "data1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 9, 9, 1, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 1, 9, 9, 1, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9,
9 _
        , 9, 9, 9, 9, 9, 9, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveCell.Offset(5, 3).Range("A1").Select
End Sub


Hi.

I have no programming experience. I'm an intern working in a lab analyzing bat calls. These calls are in text format and I'm trying to consolidate hundreds of these files (in a single directory) into an excel spreadsheet to preform statistical analysis on later. The problem is not only do I need the files imported, I also need to format the text in each file so that only the data in rows beneath "Duration(ms), F0min(Hz), F010th(Hz).....etc." are recorded and all the other information is simply noise and deleted. This will later be copied from the "temp" tab into the "data" tab as shown in the excel document attached.

Can somebody provide a code to do this? I would be really appreciated! I've uploaded the spreadsheet with a macro I recorded and also several .RM.txt files as samples

Thanks!

I have been using the attached to pull multiple csv files into seperate sheets. This takes all files from a user specified directory and dumps them into a workbook. I only need three specific files. How do I request specific files instead of a wild card.

How can I write a macro to save the current file with a filename from cell A1 in other PC of my LAN?
I can't find the way
Please Help.

How to append multiple resulted values into a column?

Case:
Data in colA of worksheet1 should be compared with data in colB of worksheet2 and if matches retrieve value from colC of worksheet2 and paste result/value in colB of worksheet1. And most importantly for multiple results/values found how to append those values/results to colB of worksheet1?

Hi all,
I want to create a macros to load multiple text files from a directory say c:test ,ther text files are of the form a1,a2,a3.....a100 , i have to load it in excel and delete the first row and then save it again in the same directory as excel files like a1.xls,a2.xls,a3.xls..

can anyone help me with this.

thanks,
sanka

Hello,

I need to import multiple .txt files into a single excel sheet and then count the number of times a give entry / value appears.

Currently I drag each .txt file into Excel, then use a Pivot Table to sort and count the number of times a given entry / value appears.

Is there a way to import 30 or more .txt files at one time into a single excel sheet?

I'm a novice with Excel, so any help you could give would be much appreciated.

Below is a sample of the .txt files.

2008/06/01 00:02:10 2008/06/01 00:01:54 00:00:15 Zone.dsX CONTENT MultiMedia 01_one.mov
2008/06/01 00:02:10 2008/06/01 00:01:54 00:00:15 Zone.dsX CONTENT MultiMedia 02_two.mov
2008/06/01 00:02:10 2008/06/01 00:01:54 00:00:15 Zone.dsX CONTENT MultiMedia 03_three.mov
2008/06/01 00:02:10 2008/06/01 00:01:54 00:00:15 Zone.dsX CONTENT MultiMedia 04_four.mov
2008/06/01 00:02:10 2008/06/01 00:01:54 00:00:15 Zone.dsX CONTENT MultiMedia 05_five.mov
2008/06/01 00:02:10 2008/06/01 00:01:54 00:00:15 Zone.dsX CONTENT MultiMedia 06_six.mov
2008/06/01 00:02:10 2008/06/01 00:01:54 00:00:15 Zone.dsX CONTENT MultiMedia 07_seven.mov
2008/06/01 00:02:10 2008/06/01 00:01:54 00:00:15 Zone.dsX CONTENT MultiMedia 08_eight.mov
2008/06/01 00:02:10 2008/06/01 00:01:54 00:00:15 Zone.dsX CONTENT MultiMedia 09_nine.mov
2008/06/01 00:02:10 2008/06/01 00:01:54 00:00:15 Zone.dsX CONTENT MultiMedia 10_ten.mov

Hi,

I'm not a regular Macros user so have very limited experience.

I'm currently have a workbook which refers to lots of different suppliers. I use the auto filter to view specific suppliers, but if I want to send this workbook to a supplier the information on the other suppliers is confidential and therefore the whole book can't be sent.

Therefore I would like a Macro to export the filtered results into a new excel workbook which can then be sent to suppliers.

I realise that there is an advanced filter option which can do this, however I need to make it as simple and intuative as possible as there will be many people using the spread sheet over time. So I would ideally like to put an "export filter results" at the top of the spread sheet.

IS there somewhere you could point me to read up on what's required?

Thanks,

Stephen

Hi,

I have been trying to write a macro to extract data from multiple TRA files into columns in excel.

The data in the TRA file is 6000 rows text delimited with a comma as per quoted. I want to put the data from each TRA file into two columns with the filename as column title. All side by side in one worksheet.

Originally Posted by TRA File 18.082,1.37259 17.8991,1.39007 17.7279,1.40678 Originally Posted by Excel 18.082 1.37259 17.8991 1.39007 17.7279 1.40678
This is what I have so far, it does extract the data and the filename but puts it into one column and leaves one blank column. I'm sure it's something with the redim array but I can't figure it out.


	VB:
	
 
    Dim txt As String, n As Long, x, a() 
     
     ' set folder path
    myDir = "C:UsersmyuserDesktoptest" 
    fn = Dir(myDir & "*.TRA") 
     
    Do While fn  "" 
        n = n + 2 
        txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(myDir & fn).ReadAll 
        x = Split(txt, ",", , vbTextCompare) 
        Redim a(1 To UBound(x) + 1, 1 To 1) 
        For i = 0 To UBound(x) 
            a(i + 1, 1) = x(i) 
        Next 
        Cells(1, n).Value = CreateObject("Scripting.FileSystemObject").GetBaseName(fn) 
        Cells(2, n).Resize(UBound(a, 1)).Value = a 
        fn = Dir 
         
    Loop 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for any help!

I have multiple ".csv" files with different names in c:temp that I want to combine into a single workbook in different worksheets and then save the workbook as "Output"

Is it possible to save a workbook in a macro script or no? If not it's not that big of a deal. Thank you for the help.

(xposted to http://www.mrexcel.com/forum/showthread.php?t=550778)