idlewyld89
New Member
- Joined
- Jun 10, 2018
- Messages
- 23
Hi everyone, most likely a simple explanation here but I'm not seeing it...
I get a "Compile Error - Loop without Do" message on the first line of the code below. I've got a do in there... obviously it's placed in such a way that it doesn't recognize it, but I'm not comfortable with what needs to be changed in order to get it working.
Ultimately, the routine asks the user to select an external file. Additionally, the user is asked to confirm their selection, and the loop is meant to recycle the same routine in the case that the user selects "No" at the confirmation dialog.
I get a "Compile Error - Loop without Do" message on the first line of the code below. I've got a do in there... obviously it's placed in such a way that it doesn't recognize it, but I'm not comfortable with what needs to be changed in order to get it working.
Ultimately, the routine asks the user to select an external file. Additionally, the user is asked to confirm their selection, and the loop is meant to recycle the same routine in the case that the user selects "No" at the confirmation dialog.
Code:
Private Sub ImportData()' References: TestWSName,
' Dependents: NoREV_tool,
' ~~> Prompts user to select the source file to be used for reporting, opens the file, and copies it's contents.
' ~~> Error Handling: File Selection (on Cancel, END Sub) - File Confirmation (on No, RESET FileDialog / on Cancel, END Sub)
Dim fd As FileDialog
Dim DataWbk As Workbook
Dim DataPath As String
Dim ReportWbk As Workbook
Dim MsgResponse As Integer
Dim FileSelection As Integer
Set ReportWbk = ActiveWorkbook
If TestWSName(MainSheet) = True Then
Sheets(MainSheet).Delete
End If
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Title = "Import File"
fd.InitialView = msoFileDialogViewSmallIcons
fd.Filters.Clear
fd.Filters.Add "Excel files", "*.xlsx;*.xls;*.xlsm"
fd.ButtonName = "Open VAW File"
Do
FileSelection = fd.Show
If FileSelection <> -1 Then
MsgBox "No File Selected" & vbNewLine & vbNewLine & "Please Download Your File From:" & vbNewLine & vbNewLine & "Intranet > VAW > Operations > Fleet Status", vbOKOnly, "Warning"
End
Else
DataPath = Right$(fd.SelectedItems.Item(1), Len(fd.SelectedItems.Item(1)) - InStrRev(fd.SelectedItems.Item(1), "\"))
MsgResponse = MsgBox("Please confirm your selected file:" & vbNewLine & vbNewLine & DataPath, vbYesNoCancel, "File Confirmation")
If MsgResponse = vbYes Then
DataPath = Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Item(1)
Set DataWbk = Workbooks.Open(DataPath)
MainSheet = DataWbk.Sheets(1)
DataWbk.Sheets(1).Copy Before:=ReportWbk.Sheets(1)
DataWbk.Close False
ReportWbk.Sheets(MainSheet).Activate
ElseIf MsgResponse = vbCancel Then
MsgBox "No File Selected" & vbNewLine & vbNewLine & "Please Download Your File From:" & vbNewLine & vbNewLine & "Intranet > VAW > Operations > Fleet Status", vbOKOnly, "Warning"
End
End If
Loop While MsgResponse = vbNo