Hi Everyone,
The purpose of this macro is to import a text file from Oracle that is pretty ugly, eliminate characters, and make it usable in excel. The macro works fine. I tried to make it more robust by allowing the user to choose the text file they want (different sites will select the file from different saved network paths).
I was hoping someone might spend a few minutes to check out the code and suggest more efficient ways to do something or just point out any bad coding. I would be grateful. I've read a few books and am to the point where I can get things accomplished. But I am very far from writing anything elegant and simple.
I appreciate any suggestions in advance
The purpose of this macro is to import a text file from Oracle that is pretty ugly, eliminate characters, and make it usable in excel. The macro works fine. I tried to make it more robust by allowing the user to choose the text file they want (different sites will select the file from different saved network paths).
I was hoping someone might spend a few minutes to check out the code and suggest more efficient ways to do something or just point out any bad coding. I would be grateful. I've read a few books and am to the point where I can get things accomplished. But I am very far from writing anything elegant and simple.
I appreciate any suggestions in advance
Code:
Sub MFGVarianceFormat()
' Purpose: Import and format material variance report from oracle.
' Clear old report
Worksheets("Gentilly MFG Variance").Cells(11, 1).Resize _
(Rows.Count - 10, Columns.Count).Delete
'Set file name and path to variable to be used in text import wizard
Dim FileName As String
FileName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt", _
Title:="Select TEXT file ONLY", MultiSelect:="False")
'If no text file selected end macro. If text file selected, import file
If FileName = "False" Or FileName = " " Then
Exit Sub
Else
'Import text wizard
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & FileName & "", Destination:=Range("$A$11"))
.Name = "Material_Variance-012315"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(12, 39, 4, 15, 17, 16, 18, 16, 16, 15)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
'Find last row with data
Dim i As Integer
Dim iFinalRow As Integer
iFinalRow = Cells(Rows.Count, 1).End(xlUp).Row
'Find the starting cell for loop
Cells.Find(What:="description", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'Loop through several conditions to eliminate blank rows, headers, and characters
For i = iFinalRow To ActiveCell.Row + 1 Step -1
If IsEmpty(Cells(i, 2)) Then
Cells(i, 1).EntireRow.Delete
ElseIf (IsNumeric(Cells(i, 6))) - (IsEmpty(Cells(i, 6))) = 1 Then
Cells(i, 3).EntireRow.Delete
ElseIf Cells(i, 5).Value = "" Then
Cells(i, 2).EntireRow.Delete
ElseIf Not IsNumeric(Cells(i, 5)) Then
Cells(i, 5).EntireRow.Delete
Else
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
End If
Next i
'Turn on filter
ActiveSheet.Range("A21:K21").AutoFilter
'Format and move run date and time into rows 11-13
Rows("15:19").EntireRow.Delete
Worksheets("Gentilly MFG Variance").Range("A12:I15").ClearContents
Worksheets("Gentilly MFG Variance").Range("J15:k15").ClearContents
Range("J12:K14").Cut Destination:=Range("A11")
Application.CutCopyMode = False
With Range("A11:B13")
.HorizontalAlignment = xlLeft
.Font.Bold = True
.Interior.ColorIndex = 6
End With
'Label and size columns
Cells(16, 3).Value = "UOM"
Cells(16, 4).Value = "STD Cost"
Cells(16, 5).Value = "STD Units"
Cells(16, 6).Value = "STD Dollars"
Cells(16, 7).Value = "Actual Units"
Cells(16, 8).Value = "Actual Dollars"
Cells(16, 9).Value = "Usage Variance Units"
Cells(16, 10).Value = "Usage Variance Dollars"
Cells(16, 11).Value = "Usage Percentage %"
Columns("F:K").AutoFit
'Filter and highlight Total rows
ActiveSheet.Range("A16:K16").AutoFilter _
field:=2, Criteria1:="Total" & "*"
Range("A16").CurrentRegion.Offset(1).Interior.ColorIndex = 15
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("A16:K16").AutoFilter
'Clear format last row
Dim afterMacroFinalRow As Integer
afterMacroFinalRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("A" & afterMacroFinalRow + 1).EntireRow.ClearFormats
Cells(16, 1).Select
End Sub