Hey guys,
I'm writing a macro which allows the user to select a set of text files, create a workbook, and then the macro copies each text file into a separate sheet in the created workbook. While running this from the VBA editor everything works perfectly, but when run with the Hotkey from within the workbook, it snags after opening the first file and stops completely. The macro is posted below, and I sincerely appreciate any help, as this has me totally stumped.
Option Explicit
Public Sub OpenDataFile()
'
' OpenDataFile Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Dim FileNames As Variant
Dim Msg As String
Dim I As Integer
Dim OutputFile As String
Dim FileCounter As Variant
Dim Workbook1 As Variant
Dim Current As String
On Error Resume Next
FileNames = _
Application.GetOpenFilename(MultiSelect:=True)
If IsArray(FileNames) Then
Msg = "You Selected:" & vbNewLine
For I = LBound(FileNames) To UBound(FileNames)
Msg = Msg & FileNames(I) & vbNewLine
Next I
MsgBox Msg
Else
MsgBox "No files were selected."
End If
Application.DisplayAlerts = False
OutputFile = InputBox("Which filename would you like to store the" & _
"compiled files under?")
Workbooks.Add
ActiveWorkbook.SaveAs FileName:=OutputFile
FileCounter = 0
Do While FileCounter < (I - 1)
FileCounter = FileCounter + 1
Workbooks.Open FileName:=FileNames(FileCounter)
Current = FileNames(FileCounter)
Current = Mid(Current, InStrRev(Current, "\") + 1)
MsgBox Current
Workbooks(Current).Activate
Set Workbook1 = ActiveWorkbook
ActiveSheet.Range("A1:I16").Select
Selection.Copy
Workbooks(OutputFile).Activate
Worksheets(FileCounter).Activate
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Name = (FileCounter)
Workbook1.Close
Worksheets.Add After:=Sheets(Sheets.Count)
Loop
End Sub
Bookmarks