ripkin900
12-22-2011, 07:45 PM
Ok guys, I am a newbie, made a simple macro, it works ok on my laptop home, its a little slow...my laptop which has windows 7, but at my work it just freezes up whenever i click the button to run macro(I have it assigned toa button)...its one of two macros in my spreadsheet....
We only have excel 2003 at work and I built it home on excel 2010 and saved it as a 2003 workbook....I do not know if this matters or not but just some info...
any thoughts??????
here is code:
Sub importMerlin()
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
Sheets("Merlin Dispatch").Select
Columns("A:M").Select
Selection.Clear
Range("A1").Select 'paste the text data here
Dim DestBook As Workbook, Sourcebook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell
'set up list of file types first
MyFilter = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn), *.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"Excel Files (*.xls),*.xls," & _
"All Files (*.*),*.*"
'Display *.txt by default: this is number 1 on the above list
FilterIndex = 6
MsgBox "Make sure the MERLIN report has ALL COLUMNS included, and you have to import MERLIN first"
'Set the dialog box caption
Title = "Select the Merlin Dispatch Report Report File to Import..."
'now get the file name
Filename = Application.GetOpenFilename(MyFilter, FilterIndex, Title)
'Exit if the Dialog Box is cancelled
If Filename = False Then
MsgBox "Hey,You didn't select a file!..."
Sheets("Macros").Select
Exit Sub
End If
Workbooks.Open Filename:=Filename
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy
Set Sourcebook = ActiveWorkbook
DestBook.Activate
DestCell.PasteSpecial Paste:=xlValues
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(7, 1)), TrailingMinusNumbers:=True
Range("B1").Select
ActiveCell.FormulaR1C1 = "Job Name"
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
Range("a1").Select
Sourcebook.Close False
End Sub
We only have excel 2003 at work and I built it home on excel 2010 and saved it as a 2003 workbook....I do not know if this matters or not but just some info...
any thoughts??????
here is code:
Sub importMerlin()
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
Sheets("Merlin Dispatch").Select
Columns("A:M").Select
Selection.Clear
Range("A1").Select 'paste the text data here
Dim DestBook As Workbook, Sourcebook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell
'set up list of file types first
MyFilter = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn), *.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"Excel Files (*.xls),*.xls," & _
"All Files (*.*),*.*"
'Display *.txt by default: this is number 1 on the above list
FilterIndex = 6
MsgBox "Make sure the MERLIN report has ALL COLUMNS included, and you have to import MERLIN first"
'Set the dialog box caption
Title = "Select the Merlin Dispatch Report Report File to Import..."
'now get the file name
Filename = Application.GetOpenFilename(MyFilter, FilterIndex, Title)
'Exit if the Dialog Box is cancelled
If Filename = False Then
MsgBox "Hey,You didn't select a file!..."
Sheets("Macros").Select
Exit Sub
End If
Workbooks.Open Filename:=Filename
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy
Set Sourcebook = ActiveWorkbook
DestBook.Activate
DestCell.PasteSpecial Paste:=xlValues
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(7, 1)), TrailingMinusNumbers:=True
Range("B1").Select
ActiveCell.FormulaR1C1 = "Job Name"
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
Range("a1").Select
Sourcebook.Close False
End Sub