VB how execute code for each file open & open multiple files

[Closed]
Report
Posts
10
Registration date
Saturday July 3, 2010
Status
Member
Last seen
May 5, 2016
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
The top half is code for opening my file. I tried opening multiple which seems to open but not execute the code I want ran for each file. I'm thinking loop but terrible at writing them. The bottom half is code for executing the main code. The number of files to open vary 3-6 which means repeat same code x amount of times.

Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long
Sub OpenFile()

Dim Filter As String, Title As String, msg As String
Dim I As Integer, FilterIndex As Integer
Dim Filename As Variant


' File filters
Filter = "Excel Files (*.xls),*.xls," & _
"Comma Seperated Files (*.csv),*.csv," & _
"All Files (*.*),*.*"

' Default filter to *.*
FilterIndex = 3

' Set Dialog Caption
Title = "Select File(s) to Open"

' Select Start Drive & Path
'**************************************************
'******* Set Default Path Here***********************
'**************************************************
SetCurrentDirectoryA "\\tempedisk\td\Aetrium\VT\250K_VT"
'**************************************************
'**************************************************
'**************************************************

With Application
' Set File Name Array to selected Files (allow multiple)
Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
' Reset Start Drive/Path
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Not IsArray(Filename) Then
MsgBox "No file was selected."
Exit Sub
End If
' Open Files
For I = LBound(Filename) To UBound(Filename)
msg = msg & Filename(i) & vbCrLf
Workbooks.OpenText Filename(i), Origin:=xlWindows, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
Space:=False, Other:=False
Next i
MsgBox msg, vbInformation, "Files Opened"


For code to execute after it opens a file:
Sub Regular()
Dim vFile As Variant 'Open Files
Dim strNumber As String 'Open Files
Dim lngColNum As Long 'number of columns of data
Dim lngStartRow As Long 'starting row of data manipulation
Dim lngEndRow As Long 'end row of data manipulation
Dim lngRow As Long 'row index
Dim lngI As Long 'counter
Dim lngJ As Long 'counter
Dim x As Variant 'For filling in columns

'open user selected file using delimiters tab, but not space

Call OpenFile

'Copy Contents to Workbook
Range("A:AF").Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=False
ThisWorkbook.Activate
Worksheets("Sheet1").Activate
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Call Replace Data to makes changes
Call ReDData

'CHECK if M1-M4 in cell
Worksheets("Instructions").Activate
Range("I1").Select
If Cells(1, "I").Value = "M1" Then
Call MDA
Else: End If

If Cells(1, "I").Value = "M2" Then
Call MDB
Else: End If

If Cells(1, "I").Value = "M3" Then
Call MDC
Else: End If

If Cells(1, "I").Value = "M4" Then
Call MDD
Else: End If
Application.ScreenUpdating = False
'**********************************************************
' Clear Sheet 2
'**********************************************************
Sheets("Sheet2").Activate
Cells.Select
Selection.ClearContents
Sheets("Sheet2").Cells(1, 1).Select
'**********************************************************
' Format Sheet
'**********************************************************
Sheets("Sheet2").Cells(1, 1) = "Test Type"
Sheets("Sheet2").Cells(1, 2) = "Mask"
Sheets("Sheet2").Cells(1, 3) = "Lot"
Sheets("Sheet2").Cells(1, 4) = "Ring"
Sheets("Sheet2").Cells(1, 5) = "Module"
Sheets("Sheet2").Cells(1, 6) = "Voltage"
Sheets("Sheet2").Cells(1, 7) = "Device"
Sheets("Sheet2").Cells(1, 8) = "Wafer"
Sheets("Sheet2").Cells(1, 9) = "Time"
Sheets("Sheet2").Cells(1, 10) = "Socket"
Sheets("Sheet2").Cells(1, 11) = "Values"
'**********************************************************
' Determine number of data columns
'**********************************************************
lngI = 1
Do Until Sheets("Sheet1").Cells(1, lngI + 1) = ""
lngI = lngI + 1
Loop

lngColNum = lngI - 9
'**********************************************************
' Manipulate Data
'**********************************************************
lngRow = 2
lngStartRow = 2
lngEndRow = lngColNum + 1
Do Until Sheets("Sheet1").Cells(lngRow, 1) = "" 'for each row in sheet 1

'Copy the 9 columns of data for this row
For lngI = 1 To 9 '
Sheets("Sheet2").Cells(lngStartRow, lngI) = Sheets("Sheet1").Cells(lngRow, lngI)
Next

'Transpose the data for this row
For lngI = 1 To lngColNum
Sheets("Sheet2").Cells(lngI + lngStartRow - 1, 10) = Sheets("Sheet1").Cells(1, lngI + 9)
Sheets("Sheet2").Cells(lngI + lngStartRow - 1, 11) = Sheets("Sheet1").Cells(lngRow, lngI + 9)
Next

'Fill in empty rows in first 11 columns
For lngI = lngStartRow + 1 To lngEndRow 'for each row
For lngJ = 1 To 9 'for each column
Sheets("Sheet2").Cells(lngI, lngJ) = Sheets("Sheet2").Cells(lngI - 1, lngJ)
Next
Next
lngRow = lngRow + 1
lngStartRow = lngStartRow + lngColNum
lngEndRow = lngStartRow + lngColNum - 1
Loop

'Grab Data from Sheet2 and copy over to Sheet3
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Worksheets("Raw Data VT").Activate
Cells(Rows.Count, "A").End(xlUp)(2).Select
ActiveSheet.Paste
Cells.Select
Selection.Columns.AutoFit

'Clearing out sheet1 before starting to Open files
ThisWorkbook.Activate
Worksheets("Sheet1").Activate
Sheets("Sheet1").Activate
Cells.Select
Selection.ClearContents
Sheets("Sheet1").Cells(1, 1).Select
'clear Sheet2
Sheets("Sheet2").Activate
Cells.Select
Selection.ClearContents

Application.ScreenUpdating = True
MsgBox ("Macro Finished")

End Sub

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Well first code seems to be incomplete. Other thing is what exactly you are tying to accomplish ? Lets say for example there are 3 files that would be opened. Then what exactly you want to do with those file ?

any ways based on what I got. there are two changes you need (hightlighted)

Private Declare Function SetCurrentDirectoryA _ 
Lib "kernel32" (ByVal lpPathName As String) As Long 

Sub OpenFile() 

Dim Filter As String, Title As String, msg As String 
Dim I As Integer, FilterIndex As Integer 
Dim Filename As Variant 
Dim wb As Workbook 
    
   ' File filters 
   Filter = "Excel Files (*.xls),*.xls," & _ 
      "Comma Seperated Files (*.csv),*.csv," & _ 
      "All Files (*.*),*.*" 
    
   ' Default filter to *.* 
   FilterIndex = 3 
    
   ' Set Dialog Caption 
   Title = "Select File(s) to Open" 
    
   ' Select Start Drive & Path 
   '************************************************** 
   '******* Set Default Path Here*********************** 
   '************************************************** 
   SetCurrentDirectoryA "\\tempedisk\td\Aetrium\VT\250K_VT" 
   '************************************************** 
   '************************************************** 
   '************************************************** 
    
   With Application 
      ' Set File Name Array to selected Files (allow multiple) 
      Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True) 
      ' Reset Start Drive/Path 
      ChDrive (Left(.DefaultFilePath, 1)) 
      ChDir (.DefaultFilePath) 
   End With 
   ' Exit on Cancel 
   If Not IsArray(Filename) Then 
      MsgBox "No file was selected." 
      Exit Sub 
   End If 
   ' Open Files 
   For I = LBound(Filename) To UBound(Filename) 
      msg = msg & Filename(i) & vbCrLf 
     Set wb = Workbooks.OpenText(Filename(i), Origin:=xlWindows, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ 
      ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _ 
      Space:=False, Other:=False) 
       
      'file has been opened, you can run the macro there 
      Call Regular 
 
   Next I 
   MsgBox msg, vbInformation, "Files Opened" 
    
 End Sub 
  
'For code to execute after it opens a file: 
Sub Regular() 
   Dim vFile As Variant 'Open Files 
   Dim strNumber As String 'Open Files 
   Dim lngColNum As Long 'number of columns of data 
   Dim lngStartRow As Long 'starting row of data manipulation 
   Dim lngEndRow As Long 'end row of data manipulation 
   Dim lngRow As Long 'row index 
   Dim lngI As Long 'counter 
   Dim lngJ As Long 'counter 
   Dim x As Variant 'For filling in columns 
    
   'open user selected file using delimiters tab, but not space 
   ' you cannot open file file. since you want this file to be exected x number of times 
   'Call OpenFile 
    
   'Copy Contents to Workbook 
   Range("A:AF").Select 
   Selection.Copy 
   Application.DisplayAlerts = False 
   ActiveWorkbook.Close savechanges:=False 
   ThisWorkbook.Activate 
   Worksheets("Sheet1").Activate 
   Cells.Select 
   ActiveSheet.Paste 
   Application.CutCopyMode = False 
    
   'Call Replace Data to makes changes 
   Call ReDData 
    
   'CHECK if M1-M4 in cell 
   Worksheets("Instructions").Activate 
   Range("I1").Select 
   If Cells(1, "I").Value = "M1" Then 
      Call MDA 
   Else: End If 
    
   If Cells(1, "I").Value = "M2" Then 
      Call MDB 
   Else: End If 
    
   If Cells(1, "I").Value = "M3" Then 
      Call MDC 
   Else: End If 
    
   If Cells(1, "I").Value = "M4" Then 
      Call MDD 
   Else: End If 
   Application.ScreenUpdating = False 
   '********************************************************** 
   ' Clear Sheet 2 
   '********************************************************** 
   Sheets("Sheet2").Activate 
   Cells.Select 
   Selection.ClearContents 
   Sheets("Sheet2").Cells(1, 1).Select 
   '********************************************************** 
   ' Format Sheet 
   '********************************************************** 
   Sheets("Sheet2").Cells(1, 1) = "Test Type" 
   Sheets("Sheet2").Cells(1, 2) = "Mask" 
   Sheets("Sheet2").Cells(1, 3) = "Lot" 
   Sheets("Sheet2").Cells(1, 4) = "Ring" 
   Sheets("Sheet2").Cells(1, 5) = "Module" 
   Sheets("Sheet2").Cells(1, 6) = "Voltage" 
   Sheets("Sheet2").Cells(1, 7) = "Device" 
   Sheets("Sheet2").Cells(1, 8) = "Wafer" 
   Sheets("Sheet2").Cells(1, 9) = "Time" 
   Sheets("Sheet2").Cells(1, 10) = "Socket" 
   Sheets("Sheet2").Cells(1, 11) = "Values" 
   '********************************************************** 
   ' Determine number of data columns 
   '********************************************************** 
   lngI = 1 
   Do Until Sheets("Sheet1").Cells(1, lngI + 1) = "" 
      lngI = lngI + 1 
   Loop 
    
   lngColNum = lngI - 9 
   '********************************************************** 
   ' Manipulate Data 
   '********************************************************** 
   lngRow = 2 
   lngStartRow = 2 
   lngEndRow = lngColNum + 1 
   Do Until Sheets("Sheet1").Cells(lngRow, 1) = "" 'for each row in sheet 1 
    
      'Copy the 9 columns of data for this row 
      For lngI = 1 To 9 ' 
         Sheets("Sheet2").Cells(lngStartRow, lngI) = Sheets("Sheet1").Cells(lngRow, lngI) 
      Next 
       
      'Transpose the data for this row 
      For lngI = 1 To lngColNum 
         Sheets("Sheet2").Cells(lngI + lngStartRow - 1, 10) = Sheets("Sheet1").Cells(1, lngI + 9) 
         Sheets("Sheet2").Cells(lngI + lngStartRow - 1, 11) = Sheets("Sheet1").Cells(lngRow, lngI + 9) 
      Next 
       
      'Fill in empty rows in first 11 columns 
      For lngI = lngStartRow + 1 To lngEndRow 'for each row 
         For lngJ = 1 To 9 'for each column 
            Sheets("Sheet2").Cells(lngI, lngJ) = Sheets("Sheet2").Cells(lngI - 1, lngJ) 
         Next 
      Next 
      lngRow = lngRow + 1 
      lngStartRow = lngStartRow + lngColNum 
      lngEndRow = lngStartRow + lngColNum - 1 
   Loop 
    
   'Grab Data from Sheet2 and copy over to Sheet3 
   Range("A2").Select 
   Range(Selection, Selection.End(xlToRight)).Select 
   Range(Selection, Selection.End(xlDown)).Select 
   Selection.Copy 
   Worksheets("Raw Data VT").Activate 
   Cells(Rows.Count, "A").End(xlUp)(2).Select 
   ActiveSheet.Paste 
   Cells.Select 
   Selection.Columns.AutoFit 
    
   'Clearing out sheet1 before starting to Open files 
   ThisWorkbook.Activate 
   Worksheets("Sheet1").Activate 
   Sheets("Sheet1").Activate 
   Cells.Select 
   Selection.ClearContents 
   Sheets("Sheet1").Cells(1, 1).Select 
   'clear Sheet2 
   Sheets("Sheet2").Activate 
   Cells.Select 
   Selection.ClearContents 
    
   Application.ScreenUpdating = True 
   MsgBox ("Macro Finished") 
    
End Sub 


Posts
10
Registration date
Saturday July 3, 2010
Status
Member
Last seen
May 5, 2016

Hello,

What I would like to do is:
1) Open multiples .csv files (all have different names)
2) Execute sub routine called 'Regular' to each file opened
3) Close each file once routine 'Regular' completed

Currently, the code runs 1 file at a time with no problem. I just don't know how to select multiple files and write a loop to execute code on the number of files opened which can be 3 or more which will vary. If it runs fine on 1 file at a time how do I get it to do it on multiple files?

I tried dropping in code 'Call Regular' where you suggested in Openfile and I can open multiple files but does not execute the sub routinue Regular. Sorry for confusion.

Thanks,
weenie
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
From what I see the macro should have worked. There might be some thing in those routines that are called but your code here does not have them.

Try this. All it does is open file, call the macro "regular" display what is active file, closes it and display what has become the active file.

My expectation is that first msgbox will show you the name of the file that got opened, and 2nd msg box will show you the name of the file that was active when macro "OpenFile" started

It it works as per my expectation, then you know that your macro is called multiple times as you wanted and some thing else is the issue


Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long

Sub OpenFile()

Dim Filter As String, Title As String, msg As String
Dim I As Integer, FilterIndex As Integer
Dim Filename As Variant
Dim wb As Workbook
    
   ' File filters
   Filter = "Excel Files (*.xls),*.xls," & _
      "Comma Seperated Files (*.csv),*.csv," & _
      "All Files (*.*),*.*"
    
   ' Default filter to *.*
   FilterIndex = 3
    
   ' Set Dialog Caption
   Title = "Select File(s) to Open"
    
   ' Select Start Drive & Path
   '**************************************************
   '******* Set Default Path Here***********************
   '**************************************************
   SetCurrentDirectoryA "\\tempedisk\td\Aetrium\VT\250K_VT"
   '**************************************************
   '**************************************************
   '**************************************************
    
   With Application
      ' Set File Name Array to selected Files (allow multiple)
      Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
      ' Reset Start Drive/Path
      ChDrive (Left(.DefaultFilePath, 1))
      ChDir (.DefaultFilePath)
   End With
   ' Exit on Cancel
   If Not IsArray(Filename) Then
      MsgBox "No file was selected."
      Exit Sub
   End If
   ' Open Files
   For I = LBound(Filename) To UBound(Filename)
      msg = msg & Filename(i) & vbCrLf
     Set wb = Workbooks.OpenText(Filename(i), Origin:=xlWindows, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
      Space:=False, Other:=False)
       
      'file has been opened, you can run the macro there
      Call Regular
 
   Next i
   MsgBox msg, vbInformation, "Files Opened"
    
 End Sub
  
'For code to execute after it opens a file:
Sub Regular()
   MsgBox "Active file is " + ActiveWorkbook.Name
   ActiveWorkbook.Close savechanges:=False
   MsgBox "Active file is " + ActiveWorkbook.Name
End Sub
Looks like it opens the files I selected and and runs the 1st file unsing regular sub routine. No where do I have a loop written to run the regular file x amount of times so I would expect it not to run other than 1X. Is there a way to attach an excel file with code that you can look at?You stated above my code is incomplete but I did not post others since it would be very long.
When I execute the macro it calls:
1) Sub routine 'Regular'
2) Executes 'Call OpnFile'
3) runs code to copy/paste/close the file it opened
4) 'Call ReData' which replaces/removes text information and deletes a row or 2
5) 'Call MDA or MDB or MDC or MDD depending what was entered on sheet 1 before macro started.
6) Runs remaining code

I only posted what I thought was the problem area the Openfile. With your help it now opens all files I want and runs sub routine called 'Regular' Only on 1 file not the rest. Again, I have no loop wrtiiten to execute numerous times (or off the number files opened) so sorry for any confusion.Please let me know how I can post an excel file so it can be easy for you to look at all codes I have calliong to within main sub routine 'Regular'
Thanks
weenie
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You have it wrong. I suggested you changes. Based on those changes you should be running
Sub OpenFile()