VBA to open files to import into calculator

Marit88

New Member
Joined
Mar 8, 2019
Messages
5
Hello all, my first time here and beginning VBA user. I designed a calculator which compiles and interpretes the results of 9 different sets of output from image analysis software. I always name the 8 output files identical: 1 to 9 (excel workbooks). To start the calculator I use a messagebox which allows me to navigate to the particular location from which I would like to open the 9 workbooks (see below code). What currently happens is that if I don't select any files I exit the sub. What I would like is for the sub to end if I don't select 9 files (<9). As I mentioned I am a beginner but can't find the solution anywhere. Much thanks.

Dim fd As FileDialog
Dim file_was_chosen As Boolean


Set fd = Application.FileDialog(msoFileDialogOpen)


With fd
.Filters.Clear
.Filters.Add "Excel File", "*.xl*"
End With


file_was_chosen = fd.Show


If Not file_was_chosen Then
MsgBox "You didn't select a file"
Exit Sub
End If


fd.Execute
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Try
Code:
If fd.SelectedItems.Count < 9 Then
   MsgBox "You didn't select 9 files"
   Exit Sub
End If
 

Marit88

New Member
Joined
Mar 8, 2019
Messages
5
Thank you for your quick reply - I pasted it in straight away and it works! Many thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Marit88

New Member
Joined
Mar 8, 2019
Messages
5

ADVERTISEMENT

Actually I should have been more precise - which operator would I use if I want to exit the macro if the number of files + not 9? So it also doesn't run of I select 10 files? I tried the following but it doesn't work.
Thanks!
If fd.SelectedItems.Count =NOT 9
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Almost got it, the NOT should go after the if
Code:
If Not fd.SelectedItems.Count =9
 

Marit88

New Member
Joined
Mar 8, 2019
Messages
5

ADVERTISEMENT

Actually I just managed to sort it with the following

If fd.SelectedItems.Count < 9 Then
MsgBox "You didn't select 9 files"
Exit Sub


End If

If fd.SelectedItems.Count > 9 Then
MsgBox "You selected too many files"
Exit Sub

End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,353
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top