Posts by Fergie

I have a macro enabled workbook which enables the user to select a .csv file and then edits it before saving the edited version alongside the original, in the original file location. This works fine. I am now trying to modify it so the user can select multiple files and have each processed in exactly the same way. Unfortunately this is stalling at the end of the macro module and I can't understand why. I should point out that I am very new to this. The macros were originally written by a colleague who is no longer with us. I was involved but only as a user, not actually writing the code. I have had to learn this from textbooks and online to try and understand his code and modify it. This is my first attempt at a multi selection version and I have clearly messed it up somewhere. Any help would be most appreciated.
This is the relevant code from the single selection which works.

### TO OPEN FILE ###
vfile = Application.GetOpenFilename("Excel Files (*.csv*), *.csv", 1, "Select Excel File", "Open", False)
If TypeName(vfile) = "Boolean" Then 'If Cancel then exit
Exit Sub
End If
'Open the selected file
Workbooks.Open vfile, Local:=True
‘ Identify source of file to call appropriate module
‘1 CT24
Application.Goto Reference:="R1C2"
If ActiveCell.Value = 0 Then
 ' This identifies the file as from a CT24 as being the only file type with no data at R1C2 and calls the appropriate macro
Call Module1.ct24
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
End If
 
'2. Datong Rapids or GC-101
 
Application.Goto Reference:="R1C7"
z = ActiveCell.Value
If Mid(z, 1, 4) = "AUTO" Then
Call Module2.GC101
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
Else
Call Module3.Datong
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
End If
 
End Sub
### TO SAVE at end of Module 1 macro ###
MyFileName = vfile
length = Len(MyFileName) - 4
MyFileName = Mid(MyFileName, 1, length)
MyFileName = MyFileName & "- Stopped.csv"
Application.DisplayAlerts = False
ActiveSheet.Columns("A:O").EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:=MyFileName
Application.DisplayAlerts = True
 
MsgBox "All Done - Next Please"
ActiveWorkbook.Close False
Exit Sub
Exit Sub
Err1:
    MsgBox "Unexpected error - In " & info, , "Tell Fergie!"
    Exit Sub
End Sub

And this is my attempt at modifying it for a multiple selection, which doesn't

v### TO OPEN ###
file = Application.GetOpenFilename("Excel Files (*.csv), *.csv", _
              Title:="Select files", MultiSelect:=True)
    If TypeName(vfile) = "Boolean" Then 'If Cancel then exit
    Exit Sub
    End If
    If IsArray(vfile) Then
    For i = LBound(vfile) To UBound(vfile)
            Set wbkToCopy = Workbooks.Open(Filename:=vfile(i), Local:=True)
Application.Goto Reference:="R1C2"
            If ActiveCell.Value = 0 Then ' the CT24 file has all data in Column A
            Call Module1.ct24
            Application.ScreenUpdating = True
            Unload UserForm1
            Application.Goto Reference:="R1C1"
            Exit Sub
            End If
 
'2. Datong Rapids or GC-101
 
            Application.Goto Reference:="R1C7"
            z = ActiveCell.Value
            If Mid(z, 1, 4) = "AUTO" Then
            Call Module2.GC101
            Application.ScreenUpdating = True
            Unload UserForm1
            Application.Goto Reference:="R1C1"
            Exit Sub
            Else
            Call Module3.Datong
            Application.ScreenUpdating = True
            Unload UserForm1
            Application.Goto Reference:="R1C1"
            Exit Sub
            End If
    Next i
    End If
'MsgBox "All Done - Next Please"
 
Exit Sub
End Sub
 
### TO SAVE ###
'Save file as filename + Stopped as csv
MyFileName = vfile
length = Len(MyFileName) - 4
MyFileName = Mid(MyFileName, 1, length)
MyFileName = MyFileName & "- Stopped.csv"
Application.DisplayAlerts = False
ActiveSheet.Columns("A:O").EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:=MyFileName
Application.DisplayAlerts = True
ActiveWorkbook.Close False
Exit Sub
Exit Sub
Err1:
    MsgBox "Unexpected error - In " & info, , "Tell Fergie!"
    Exit Sub
End Sub

Wow! That is so helpful of you I will cetainly try that, and the explanations are just what I needed to try and get my head around it. Thank you so much.

[SIZE=11px]Using a macro enabled work book to process a number of files and have them in a desktop folder together with the result.

What I’m trying to do

1 User selects one or more.csv files and clicks “Copy”

2 Macro creates new desktop folder using

MkDir "C:\Users" & Environ("UserName") & "\Desktop\Matches" & " " & Format (Now (), "DD-MMM-YYYY hh mm ss")

(Time format so there will never be an issue with the folder already existing)

3 Macro pastes copies of selected files intact into newly created folder.

4 Macro takes Sheet2 of each from row 2** and combines them into new sheet "Matches.csv", which is then opened.
**(So headers are lost. This is because I am sure there is a way of merging them using the headers from the first sheet but I don’t know how to do this, so I have cheated and get the macro to populate the headers afterwards)[/SIZE]

[SIZE=11px]From that point on I know what I'm doing and can run the macro over the sheet and save the resulting summary alongside the sheets that have been summarised. I have pieced together the code below which works in conjunction with the rest of my macro, but of course does everything within the macro enabled workbook. I am very much a beginner at this so have no real idea how to modify this to do what I have set out above. All I seem to be able to find is based on moving files between pre-determined folder locations, where I need something dynamic.[/SIZE]

[SIZE=11px]I should also explain that these were originally written by a colleague who is no longer with us. Although I was involved in this it was only as a user, advising what was required. I had nothing to do with writing the actual code. I have had to sit down with text books and Google to try and learn what the code does and how it works. I have learned to modify the body of the macros reasonably well but the initial download and saving areas are still a bit of a mystery to me. So far I have been able to use the code he wrote previously. What I’m trying to do here is outside of that and so I am struggling. Suffice to say I am not anything like as proficient at this as some of the code might lead you to think!
Any help would be greatly appreciated.[/SIZE]

Dim CurrentBook As Workbook
Dim info As String
info = "UserForm 1 Matches"


Dim lastrow As Long
Dim length As Integer
Dim MyFileName As Variant
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("Sheet2")
Dim IndvFiles As FileDialog
Dim FileIdx As Long
Dim i As Integer, x As Integer
Dim r As Range
Dim Sheet As Variant
Set IndvFiles = Application.FileDialog(msoFileDialogOpen)
With IndvFiles
    .AllowMultiSelect = True
    .Title = "Multi-select target data files:"
    .ButtonName = ""
    .Filters.Clear
    .Filters.Add ".csv files", "*.csv"
    .Show
End With


Application.DisplayAlerts = False
Application.ScreenUpdating = False


For FileIdx = 1 To IndvFiles.SelectedItems.Count
    Set CurrentBook = Workbooks.Open(IndvFiles.SelectedItems(FileIdx))
    For Each Sheet In CurrentBook.Sheets
        Dim LRow1 As Long
        LRow1 = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
        Dim LRow2 As Long
        LRow2 = CurrentBook.ActiveSheet.Range("A" & CurrentBook.ActiveSheet.Rows.Count).End(xlUp).Row


        Dim ImportRange As Range
        Set ImportRange = CurrentBook.ActiveSheet.Range("A2:Z" & LRow2)
        ImportRange.Copy
        WS.Range("A" & LRow1 + 1).PasteSpecial 'Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Next
    CurrentBook.Close False
Next FileIdx

Kenneth thanks again I will try it as soon as I get a chance and let you know the result. Very pleased that you replied as some of your past posts have proved invaluable to me as a learner, and were the reason I chose to join this forum.

Thanks Kenneth. The overall workbook contains multiple modules each of which relates to the file format output by a different device. The bit of code that was unclear to you is to identify the device type from its particular file format so the correct macro can be called. In this instance the ct24 is the only one which has no data in column 2, so this check identifies any data with an empty column 2 as ct24 and calls the appropriate macro. If that makes sense?

Hi, this is my first post so please bear with me if I have not done it correctly!
The below code allows the user to browse for a file and then applies the relevant macro from modules depending on the file format. What I want to do is to enable selection of multiple workbooks and then have the macro applied to each in turn. Never tried doing this before so not sure how to go about it, any help would be appreciated.

'Showing Excel Open Dialog Form
vfile = Application.GetOpenFilename("Excel Files (*.csv*), *.csv", 1, "Select Excel File", "Open", False)
If TypeName(vfile) = "Boolean" Then 'If Cancel then exit
Exit Sub
End If

'Open the selected file
Workbooks.Open vfile, Local:=True

'Check to see if correct converter is being used

'1. Check to see if data is from CT24 tracker
Application.Goto Reference:="R1C2"
If ActiveCell.Value = 0 Then ' the CT24 file has all data in Column A
Call Module1.ct24
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
End If

'2. Datong Rapids or GC-101

Application.Goto Reference:="R1C7"
z = ActiveCell.Value
If Mid(z, 1, 4) = "AUTO" Then
Call Module2.GC101
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
Else
Call Module3.Datong
Application.ScreenUpdating = True
Unload UserForm1
Application.Goto Reference:="R1C1"
Exit Sub
End If

End Sub

Private Sub OptionSet1_Click()

End Sub

Private Sub UserForm_Click()

End Sub