Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Posts
    8

    Unanswered: Import Template for Spreadsheets

    Hello All,

    Sorry if this is a repost, but I tried to search and it refused to give me any results...

    I have a spreadsheet that I get from one of my managers on a weekly basis. There's data in this spreadsheet I would like to manipulate into several reports, so I import the information into Access. However, the report has more info than I need/want in the database, so when I import the data I choose to skip certain columns and decide to index (no dups) on a particular field.

    So now my question: Is there a way to create a template or something so that I can point to the file for the current week, and select an import template so it knows exactly which fields to bring in and how?

    It would get really annoying to either edit the Excel report prior to import or manually select the fields each time. I tried the TransferSpreadsheet Macro, but it brings in the whole spreadsheet and not just certain fields.

    Any help is greatly appreciated. Thanks.

  2. #2
    Join Date
    Sep 2006
    Posts
    8
    bump for help...

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Importing Specific Spreadsheet Data

    Hi DaCurryman

    I import a number of sets of data from spreadsheets (or transfer between Access DBs). My method of choice is to use ADO in Excel.

    Needless to say I do considerable amount of data validation before import.

    As with you case I need to determine the column numbers for the various fields (just in case someone changes the column order!).

    This is the function I use for this

    Code:
    Function SetColumnsIndexOK() As Boolean
        SetColumnsIndexOK = False
                
        CodeCol = ColumnNo(cCodeHdg, HdgRow)
        If CodeCol > 200 Then
            CodeCol = ColumnNo(cJobNoHdg, HdgRow)
            If CodeCol > 200 Then
            HdgRow = HdgRow + 1
                CodeCol = ColumnNo(cJobNoHdg, HdgRow)
                If CodeCol > 200 Then
                    MsgBox "The column heading " & cCodeHdg & " or " & cJobNoHdg & " could not be found!" & vbLf & vbLf & _
                            "A WIP Sheet must be open AND visible.", vbCritical, msgTitle
                    Exit Function
                End If
            End If
        End If
        
        DscrptnCol = ColumnNo(cDscrptnHdg, HdgRow)
        If DscrptnCol > 200 Then
            MsgBox "The column heading " & cDscrptnHdg & " could not be found!", vbCritical, msgTitle
            Exit Function
        End If
        
        MTDCostCol = ColumnNo(cMTDCostHdg, HdgRow)
        If MTDCostCol > 200 Then
            MsgBox "The column heading " & cMTDCostHdg & " could not be found!", vbCritical, msgTitle
            Exit Function
        End If
        
        MTDSalesCol = ColumnNo(cMTDSalesHdg, HdgRow)
        If MTDSalesCol > 200 Then
            MsgBox "The column heading " & cMTDSalesHdg & " could not be found!", vbCritical, msgTitle
            Exit Function
        End If
        
        YTDCostCol = ColumnNo(cYTDCostHdg, HdgRow)
        If YTDCostCol > 200 Then
            MsgBox "The column heading " & cYTDCostHdg & " could not be found!", vbCritical, msgTitle
            Exit Function
        End If
        
        YTDSalesCol = ColumnNo(cYTDSalesHdg, HdgRow)
        If YTDSalesCol > 200 Then
            MsgBox "The column heading " & cYTDSalesHdg & " could not be found!", vbCritical, msgTitle
            Exit Function
        End If
        
        JTDCostCol = ColumnNo(cJTDCostHdg, HdgRow)
        If JTDCostCol > 200 Then
            MsgBox "The column heading " & cJTDCostHdg & " could not be found!", vbCritical, msgTitle
            Exit Function
        End If
        
        JTDSalesCol = ColumnNo(cJTDSalesHdg, HdgRow)
        If JTDSalesCol > 200 Then
            MsgBox "The column heading " & cJTDSalesHdg & " could not be found!", vbCritical, msgTitle
            Exit Function
        End If
        
        MaxCol = MaxColValue
        
        MarginCol = MaxCol + 1
        ROSCol = MarginCol + 1
        
        SetColumnsIndexOK = True
    End Function
    This function first finds the heading row and then the heading title in that row
    If it hasn't found them in the first 2 rows or 200 columns then its the wrong sheet!

    The Column No function used is

    Code:
    Function ColumnNo(ColTitle As String, HdgRow As Integer) As Integer
        Dim i As Integer
        
        ColumnNo = 0
        For i = 1 To 200
            If Trim(Cells(HdgRow, i)) = ColTitle Then Exit For
        Next i
        ColumnNo = i
    End Function

    All constants and variable are declared at a module level

    I find writing code in Excel is much easier than Access if you are looking at spreadsheets. It also seems to work faster than opening an Excel instance in Access (don't ask me why).

    Once the required columns have been established you loop down the sheet and import it record by record using ADO. It 'only' takes about 1 minute for 12000 line on my machine !?

    I realise this will be slower than the transfer spreadsheet method, but if data validation is necessary then...


    I am not sure if that is of any help at all, but perhaps it is a start ?

    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •