get files and consolidate ranges

[Brandonien]

Hi I've concluded VBA is probably my best bet for efficiency here and really only need it this once. If you guys could help me I would greatly appreciate it! So I want to make an excel file (*ConDatalog.xlsx*) which will hold the consolidated tables on a single sheet.

This is the location of the unconsolidated datalogs which are in .csv format
C:\Users\Gnomish Fire\Desktop\RX-8 Tuning\Datalogs\Tune 1

This is the location of the consolidated excel file (*ConDatalog.xlsx*)
C:\Users\Gnomish Fire\Desktop\RX-8 Tuning\Datalogs

This is the range I need copied from EVERY excel file in the Tune 1 folder:
Sheet1!A2:Sheet1!U <---whenever thecells have no values so it could be 2 rows or 30000...

This is the range to copy them to
Sheet1!A1:Sheet1!U <---same as above

I need the data to be consecutive so that each table gets added to the bottom of the last

I also need it to clear all the data when the file is closed

Thank you for your time!

 

[kpark91]

In ur Tune1 Directory, are there only the datalogs (.csv)? or are there other files you dont' need?

 

[Brandonien]

its only the datalogs

 

[kpark91]

Try this?
Do it on your copy of the workbook.
It goes in your worksheet module

Sub compileCSVs()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    Dim openPath As String
    Dim lineNumber As Double, elementNumber As Double, arrayOfElements As Variant, line As String
    
    lineNumber = 0
    
    openPath = Dir("C:\Users\Gnomish Fire\Desktop\RX-8 Tuning\Datalogs\Tune 1\*.csv")
    Do While openPath <> ""
        elementNumber = 0
        csvarianceLine = 1
        Open openPath For Input As #1
            Do While Not EOF(1)
                lineNumber = lineNumber + 1
                Line Input #1, line
                arrayOfElements = Split(line, ",")
                elementNumber = 0
                For Each element In arrayOfElements
                    elementNumber = elementNumber + 1
                    Cells(lineNumber, elementNumber).Value = element
                Next
            Loop
        Close #1 ' Close
        openPath = Dir()
    Loop
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
End Sub

 

[Brandonien]

Open openpath for input as #1

when I try to run the module it says runtime error 52 file not found
and for debug it highlights this line

 

[kpark91]

ugh..

How about just doing some semi-automatic work?
It's much faster than writing the actual code.

http://www.rondebruin.nl/csv.htm
Merge all data from the csv files into a text file

Note: with a few small changes you can also use this for txt files. Replace *.csv for *.txt

1) Windows Start Button | Run
2) Type cmd and hit enter ("command" in Win 98)
3) Go to the folder with the CSV files (for help enter "help cd")
4) Type copy *.csv all.txt and hit enter to copy all data in the files into all.txt.
5) Type exit and hit enter to close the DOS window

Now we must import the text file all.txt into Excel.

1) Open Excel
2) When you use File Open to open all.txt the Text Import Wizard will help you import the file
3) Choose Delimited
4) Next
5) Check Comma
6) Finish

 

[Brandonien]

Edit: I just tried it and it worked fine, but it also leaves the first line of every file mixed in which would be a pain to get rid of every time as well as its just the headers for each column...

I will use that for now, however I would like to get the vba working as it will be a constantly changing file (*every few minutes*) and I have to refer to it in every other excel table I use

 

[kpark91]

Ok. I should probably write that cuz it's so useful later on...
Won't have time today so somebody else can take this.

 

[kpark91]

Put this in your module and see what it does.
Gotten from
http://www.rondebruin.nl/csv.htm

Declare Function OpenProcess Lib "kernel32" _
                             (ByVal dwDesiredAccess As Long, _
                              ByVal bInheritHandle As Long, _
                              ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
                                    (ByVal hProcess As Long, _
                                     lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103


Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
    Dim hProg As Long
    Dim hProcess As Long, ExitCode As Long
    'fill in the missing parameter and execute the program
    If IsMissing(WindowState) Then WindowState = 1
    hProg = Shell(PathName, WindowState)
    'hProg is a "process ID under Win32. To get the process handle:
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
    Do
        'populate Exitcode variable
        GetExitCodeProcess hProcess, ExitCode
        DoEvents
    Loop While ExitCode = STILL_ACTIVE
End Sub


Sub Merge_CSV_Files()
    Dim BatFileName As String
    Dim TXTFileName As String
    Dim XLSFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim DefPath As String
    Dim Wb As Workbook
    Dim oApp As Object
    Dim oFolder
    Dim foldername

    'Create two temporary file names
    BatFileName = Environ("Temp") & _
            "\CollectCSVData" & Format(Now, "dd-mm-yy-h-mm-ss") & ".bat"
    TXTFileName = Environ("Temp") & _
            "\AllCSV" & Format(Now, "dd-mm-yy-h-mm-ss") & ".txt"

    'Folder where you want to save the Excel file
    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If

    'Set the extension and file format
    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007
        FileExtStr = ".xlsx": FileFormatNum = 51
        'If you want to save as xls(97-2003 format) in 2007 use
        'FileExtStr = ".xls": FileFormatNum = 56
    End If

    'Name of the Excel file with a date/time stamp
    XLSFileName = DefPath & "MasterCSV " & _
    Format(Now, "dd-mmm-yyyy h-mm-ss") & FileExtStr

    'Browse to the folder with CSV files
    Set oApp = CreateObject("Shell.Application")
    Set oFolder = oApp.BrowseForFolder(0, "Select folder with CSV files", 512)
    If Not oFolder Is Nothing Then
        foldername = oFolder.Self.Path
        If Right(foldername, 1) <> "\" Then
            foldername = foldername & "\"
        End If

        'Create the bat file
        Open BatFileName For Output As #1
        Print #1, "Copy " & Chr(34) & foldername & "*.csv" _
        & Chr(34) & " " & TXTFileName
        Close #1

        'Run the Bat file to collect all data from the CSV files into a TXT file
        ShellAndWait BatFileName, 0
        If Dir(TXTFileName) = "" Then
            MsgBox "There are no csv files in this folder"
            Kill BatFileName
            Exit Sub
        End If

        'Open the TXT file in Excel
        Application.ScreenUpdating = False
        Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
        :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False

        'Save text file as a Excel file
        Set Wb = ActiveWorkbook
        Application.DisplayAlerts = False
        Wb.SaveAs Filename:=XLSFileName, FileFormat:=FileFormatNum
        Application.DisplayAlerts = True

        Wb.Close savechanges:=False
        MsgBox "You find the Excel file here: " & vbNewLine & XLSFileName

        'Delete the bat and text file you temporary used
        Kill BatFileName
        Kill TXTFileName

        Application.ScreenUpdating = True
    End If
End Sub

 

[Brandonien]

Thats pretty fancy! Ok still need to get rid of the first row from each file, Its just headers and it messes up my formulas unfortunately.. almost there ^_^ Edit: is it possible to get it to run this vba when I open the file with it, and can it be changed to ask where I want to save my master file

Thank you again for your help!