Import Kzn & database file into Excel sheets

[wwj]

Processing for K & S databases

Code: Select all

Sub import_all_Kzn_Kdb_Sdb()
    
    Dim sheetK  As String
    Dim sheetKno  As String
    Dim sheetKdb  As String
    Dim sheetKgp  As String
    Dim sheetSdb  As String
    
    Dim in_Kzn  As String
    Dim in_Kdb  As String
    Dim in_Sdb  As String
    
    ThisWorkbook.Activate
    
    '*********************************************************
    sheetK = "K-Table"
    sheetKno = "K_RCLzoneNo_Template"
    sheetKdb = "K-DB_Template"
    sheetSdb = "S-DB_Template"
    tmpSht = "temp4macro"
    '*********************************************************
    
    in_Kzn = Sheets(sheetK).Cells(14, "N")
    in_Kdb = Sheets(sheetK).Cells(15, "N")
    in_Sdb = Sheets(sheetK).Cells(16, "N")
    
    Call import_Kzn_numbers(in_Kzn)
    Call import_Kbd_Sdb_file(in_Kdb, sheetKdb)
    Call import_Kbd_Sdb_file(in_Sdb, sheetSdb)
    
    ' Clear
    Sheets(tmpSht).Select
    Sheets(tmpSht).Cells.ClearContents
    
    Sheets(sheetK).Select
    Sheets(sheetK).Range("L21").Select
    
    ' Done
    MsgBox ("All - Done!")
    
End Sub

Code: Select all

Sub import_Kzn_numbers(in_Kzn As String)
    ' Read and import K zone numbers exported from GWV.
    
    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet
    
    Dim sheetK  As String
    Dim sheetKno  As String
    Dim sheetKdb  As String
    Dim sheetKgp  As String
    Dim sheetSdb  As String

    '*********************************************************
    'sheetK = "K-Table"
    sheetKno = "K_RCLzoneNo_Template"
    'sheetKdb = "K-DB_Template"
    'sheetSdb = "S-DB_Template"
    tmpSht = "temp4macro"
    '*********************************************************
    
    ThisWorkbook.Activate
    
    'in_Kzn = Sheets(sheetK).Cells(14, "N")
    'in_Kdb = Sheets(sheetK).Cells(15, "N")
    'in_Sdb = Sheets(sheetK).Cells(16, "N")
    
    ' Import Kzone numbers.
    filePath = ActiveWorkbook.Path & "\tmp_Kzn_Szn\"
    
    Set wbI = ThisWorkbook
    Set tmpShtI = wbI.Sheets(tmpSht)  'Sheet which an input data is imported to.
    tmpShtI.Cells.ClearContents

    filePath = ActiveWorkbook.Path & "\tmp_Kzn_Szn\"
    inputFile = filePath & in_Kzn
    
    Set wbO = Workbooks.Open(inputFile, Format:=5)
    wbO.Sheets(1).Cells.Copy tmpShtI.Cells
    wbO.Close SaveChanges:=False
    
    ' Convert single-column texts to columns.
    tmpShtI.Select
    tmpShtI.Range("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
    
    ' Copy the columns into a new sheet.
    tmpShtI.Range("B:E").Select
    Selection.Copy
    
    Sheets(sheetKno).Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select

    'MsgBox ("Done!")
    
End Sub

Codes for K and S database

Code: Select all

Sub import_Kbd_Sdb_file(inDat As String, dbSht As String)
    ' Read and import K zone numbers exported from GWV.
    
    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet
    
    Dim sheetK  As String
    Dim sheetKno  As String
    Dim sheetKdb  As String
    Dim sheetKgp  As String
    Dim sheetSdb  As String

    '*********************************************************
    'sheetK = "K-Table"
    'sheetKno = "K_RCLzoneNo_Template"
    'sheetKdb = "K-DB_Template"
    'sheetSdb = "S-DB_Template"
    tmpSht = "temp4macro"
    '*********************************************************
    
    ThisWorkbook.Activate
    
    ' Import the database file - Text.
    Set wbI = ThisWorkbook
    Set tmpShtI = wbI.Sheets(tmpSht)  'Sheet which an input sheet is imported to.
    tmpShtI.Cells.ClearContents

    filePath = ActiveWorkbook.Path & "\tmp_Kzn_Szn\"
    inputFile = filePath & inDat
    
    Set wbO = Workbooks.Open(inputFile, Format:=5)
    wbO.Sheets(1).Cells.Copy tmpShtI.Cells
    wbO.Close SaveChanges:=False
    
    ' Convert single-column texts to columns.
    tmpShtI.Select
    tmpShtI.Range("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
    
    ' Copy the columns into the database sheet.
    tmpShtI.Range("B:F").Select
    Selection.Copy
    
    Sheets(dbSht).Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    
    'MsgBox ("Done!")
    
End Sub