OK...
these are the desitnation columns:
A BC D e f g h I J K L M N O P
the source columns A
need to go as follows. The first letter set is the
source column, the second is the destination column, some of them are in
order respectively as follows:
D to E
G to F
I:K to G:I
M:O to J:L
S:W to M:Q
A:C in the source file contains bogus data. I wrote code to fill A:C with
the correct data. I copy and paste the file as a whole unit. I added a loop
because the user needs to import more than one file.
If you see an opportunity for improvement, please let me know. I am fairly
new to this.
Sub Get_SAP_Filename()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Dim Station As String * 3
Dim Invnum As Variant
Dim Unit As Integer
Dim Fromdate As String * 11
Dim Todate As String * 11
'Dim currentWB As Workbook
'Dim filenameb As Workbook
currentWB = ThisWorkbook.Name
Do Until answer = vbNo
answer = MsgBox("Do you have more SAP files to Import?", vbYesNo)
If answer = vbNo Then Exit Sub
Filt = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"Excel Files (*.XLS),*.XLS," & _
"All Files (*.*),*.*"
FilterIndex = 5
Title = "Select a File to Import"
' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)
' Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If
' Open the SAP File
Workbooks.Open FileName:=FileName, ReadOnly:=True
' get file name unit name, etc
filenameb = Right(FileName, 7)
Station = Left(filenameb, 3)
' Get unit number
If Station = "CMH" Then
Unit = "126"
Else
If Station = "EWR" Then
Unit = "119"
Else
If Station = "MEM" Then
Unit = "132"
Else
If Station = "STL" Then
Unit = "153"
Else
If Station = "IND" Then
Unit = "165"
Else
If Station = "SAT" Then
Unit = "153"
Else
If Station = "ORD" Then
Unit = "240"
Else
If Station = "HNL" Then
Unit = "244"
Else
If Station = "OGG" Then
Unit = "731"
Else
If Station = "SAN" Then
Unit = "734"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Workbooks(filenameb).Activate
Invnum = Cells(10, 1)
Columns("E:F").Delete shift:=(xlToLeft)
Columns("F").Delete shift:=(xlToLeft)
Columns("I").Delete shift:=(xlToLeft)
Columns("L:N").Delete shift:=(xlToLeft)
Rows(1).Delete shift:=(xlUp)
' Edit Column A so it is now the unit name
Cells(1, 1).Select
Do While ActiveCell.Value <> ""
ActiveCell.Value = Station
ActiveCell.Offset(1, 0).Select
Loop
' Change Miscellaneous to C&I
Cells(1, 8).Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value = "Miscellaneous" Then ActiveCell.Value = "C&I"
ActiveCell.Offset(1, 0).Select
Loop
' Populate the Unit #
Cells(1, 2).Select
Do While ActiveCell.Value <> ""
ActiveCell.Value = Unit
ActiveCell.Offset(1, 0).Select
Loop
Columns("C").Insert shift:=xlToRight
' Populate FromDate
Cells(1, 3).Select
Fromdate = Mid(Cells(1, 6), 5, 2) & "/" & Right(Cells(1, 6), 2) & "/" &
Left(Cells(1, 6), 4)
Do While ActiveCell.Offset(0, -1).Value <> ""
ActiveCell.Value = Fromdate
ActiveCell.Offset(1, 0).Select
Loop
' populate the ToDate
Cells(1, 4).Select
Todate = Mid(Cells(1, 6).End(xlDown), 5, 2) & "/" & Right(Cells(1,
6).End(xlDown), 2) & "/" & Left(Cells(1, 6).End(xlDown), 4)
Do While ActiveCell.Offset(0, -1) <> ""
ActiveCell.Value = Todate
ActiveCell.Offset(1, 0).Select
Loop
Range("A1").CurrentRegion.Copy
Workbooks(currentWB).Sheets("Detail").Range("A50000").End(xlUp)
Workbooks(filenameb).Close
Sheets("Invoice").Select
If Station = "CMH" Then
Cells(25, 2).Value = "CMH"
Cells(25, 1).Value = Invnum
Else
If Station = "EWR" Then
Cells(24, 2).Value = "EWR"
Cells(24, 1).Value = Invnum
Else
If Station = "MEM" Then
Cells(26, 2).Value = "MEM"
Cells(26, 1).Value = Invnum
Else
If Station = "STL" Then
Cells(27, 2).Value = "STL"
Cells(27, 1).Value = Invnum
Else
If Station = "IND" Then
Cells(28, 2).Value = "IND"
Cells(28, 1).Value = Invnum
Else
If Station = "SAT" Then
Cells(29, 2).Value = "SAT"
Cells(29, 1).Value = Invnum
Else
If Station = "ORD" Then
Cells(30, 2).Value = "ORD"
Cells(30, 1).Value = Invnum
Else
If Station = "HNL" Then
Cells(31, 2).Value = "HNL"
Cells(31, 1).Value = Invnum
Else
If Station = "OGG" Then
Cells(32, 2).Value = "OGG"
Cells(32, 1).Value = Invnum
Else
If Station = "SAN" Then
Cells(33, 2).Value = "SAN"
Cells(33, 1).Value = Invnum
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
'Clear Station
'Clear Unit
'Delete Invnum
'Delete FileName
'Delete filenameb
Loop
Sheets("invoice").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
