Combine VBA scripts to increase automation from SAP to Excel to Access

[audan2009]

Hello all,

So, I have to export about 7 reports from SAP varying in size, lengths and widths. I slammed together the following code to clean the excel sheets.

I'd like the code to be able to open all the excel workbooks (saved as .csv from SAP), run the code, save the workbooks as XLSX with a specific name. Then run my Access Script to import them in to Access.

I'm open to all ideas to make this process better.

So here is the code that cleans the excel. The only problem with it is that sometimes SAP prints out a header so I have to delete may be about the first 15 rows until the actual column headings. It varies. I manually do that before running the script. Then save it manually, then open the next workbook and do it again, etc.

Cleaning Code:

Sub Sample()
‘combine columns
Application.DisplayAlerts = False
    Dim LastRow As Long
    Dim Ws As Worksheet
    For Each Ws In Worksheets
    LastRow = Ws.Range("A" & Ws.Rows.Count).End(xlUp).Row
    Ws.Range("AA1:AA" & LastRow).Formula = "=a1&b1&c1&d1&e1&f1&g1&h1&i1&j1&k1&l1&m1&n1&o1&p1&q1&r1&s1&t1&u1&v1&w1&x1&y1&z1"
  Next Ws
‘paste values to new worksheet and delete old sheets
  Columns("AA:AA").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Worksheets(1).Delete
    'Sheets("proc 08-15-13").Select
    'Application.CutCopyMode = False
    'ActiveWindow.SelectedSheets.Delete
 
    For Each Ws In Worksheets
 
        Ws.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
        Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
        25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), _
        Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array( _
        38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), _
        Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array( _
        51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), _
        Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array( _
        64, 1), Array(65, 1), Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), _
        Array(71, 1), Array(72, 1), Array(73, 1)), TrailingMinusNumbers:=True
     Next Ws
'clean pages
Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$BZ$70000").AutoFilter Field:=2, Criteria1:= _
        "=Material", Operator:=xlOr, Criteria2:="="
    Range("A60").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    ActiveSheet.Range("$A$1:$BZ$300000").AutoFilter Field:=2
    ActiveSheet.Range("$A$1:$BZ$300000").AutoFilter Field:=3, Criteria1:="="
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    ActiveSheet.Range("$A$1:$BZ$300000").AutoFilter Field:=2
'trim column headings
[I]Dim arrData() As Variant[/I]
[I]Dim arrReturnData() As Variant[/I]
[I]Dim rng As Excel.Range[/I]
[I]Dim lRows As Long[/I]
[I]Dim lCols As Long[/I]
[I]Dim i As Long, j As Long[/I]
 
[I]  lRows = Selection.Rows.count[/I]
[I]  lCols = Selection.Columns.count[/I]
 
[I]  ReDim arrData(1 To lRows, 1 To lCols)[/I]
[I]  ReDim arrReturnData(1 To lRows, 1 To lCols)[/I]
 
[I]  Set rng = Selection[/I]
[I]  arrData = rng.value[/I]
 
[I]  For j = 1 To lCols[/I]
[I]    For i = 1 To lRows[/I]
[I]      arrReturnData(i, j) = Trim(arrData(i, j))[/I]
[I]    Next i[/I]
[I]  Next j[/I]
 
[I]  rng.value = arrReturnData[/I]
 
[I]  Set rng = Nothing[/I]End Sub

This is the script I run in Access to import:

Option Compare Database
Sub e2a()
Dim strSaveName As String
strSaveName = "C:\Users\username\Desktop\ITP\Current Data\awarded.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Awarded", strSaveName, True, "Sheet1$"
strSaveName = "C:\Users\username\Desktop\ITP\Current Data\sales.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Sales", strSaveName, True, "Sheet1$"
strSaveName = "C:\Users\username\Desktop\ITP\Current Data\inventory.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Inventory", strSaveName, True, "Sheet1$"
strSaveName = "C:\Users\username\Desktop\ITP\Current Data\mara.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "MARA", strSaveName, True, "Sheet1$"
strSaveName = "C:\Users\username\Desktop\ITP\Current Data\unawarded.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Unawarded", strSaveName, True, "Sheet1$"
strSaveName = "C:\Users\username\Desktop\ITP\Current Data\repair.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Repair", strSaveName, True, "repiar$"
strSaveName = "C:\Users\username\Desktop\ITP\Current Data\ID.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ID", strSaveName, True, "Sheet1$"
strSaveName = "C:\Users\username\Desktop\ITP\Current Data\DD.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DD", strSaveName, True, "Sheet1$"

End Sub

 

[Norie]

This is all in Access?

 

[audan2009]

Yup, took your advice! I think I have an idea on the filtering issue. On the loop where I say delete all rows that don't start with "| M" stuff, I could extend all the criteria to all rows. Except one sheet has like 500,000 rows. So that could take a while but maybe less than that filtering and delete loop.

I wish I really knew what I was doing...

 

[audan2009]

The excel formatting is probably the spreadsheet type. I was using like 6 or 9 but that's for excel 2003. I need 2007.