I have looked at various posts but haven't found one that relates (i think).
I get the error message:
Run-time error '9': subscript out of range
Which the debugger takes me to this particular line in the VBA:
Windows("ISO - 10t qc & fs Calibration Spreadsheet").Activate
The file it is trying to import is then opened as a separate worksheet.
The VBA is supposed to import data from 6 x txt files onto separate sheets.
This works on one computer (using excel 2003), but not on other computers using excel 2003 or 2007.
The file name, as far as I can tell is correct.
The rest of the code:
Public ImportedBook As String 'filetype i.e. ImportedBook.cpd
Sub Main()
OpenCalibrationfile
CopyPaste "qc 1"
OpenCalibrationfile
CopyPaste "qc 2"
OpenCalibrationfile
CopyPaste "qc 3"
OpenCalibrationfile
CopyPaste "fs 1"
OpenCalibrationfile
CopyPaste "fs 2"
OpenCalibrationfile
CopyPaste "fs 3"
End Sub
Sub OpenCalibrationfile()
Importfile = Application _
.GetOpenFilename("All Files (*.*),*.*", , "Open calibration text file", "Select File")
If Importfile = False Then
MsgBox "Invalid file selection - job cancelled"
End
End If
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=Importfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
ImportedBook = ActiveWorkbook.Name
End Sub
Sub CopyPaste(SheetName)
Windows(ImportedBook).Activate
Cells.Select
Selection.Copy
Windows("ISO - 10t qc & fs Calibration Spreadsheet").Activate
Sheets(SheetName).Select
Cells(1, 1).Select
ActiveSheet.Paste
Windows(ImportedBook).Activate
Application.CutCopyMode = False
Windows("ISO - 10t qc & fs Calibration Spreadsheet").Activate
Workbooks(ImportedBook).Close (False)
'closes the import file without saving it to the hard disc
End Sub
Really appreciated any assistance on this. I didn't write the code, I am just trying to get it to work on other computers.
Cheers
Bookmarks