Re: How to limit the number of imported rows

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: tina (nospam_at_address.com)
Date: 04/24/04

  • Next message: Ken Snell: "Re: merging data" 
    Date: Sat, 24 Apr 2004 16:32:11 GMT

    if you're just trying to eliminate the blank rows at the "bottom" of
    spreadsheet data, which Access "sees" and imports, then try this:

    choose a field (column) in the import sheets that is never blank. in the
    Access table's design view, set a validation rule for that field, as

    Is Not Null

    this is quicker and easier than importing to a Temp table. if you can't find
    a common "not blank" field in your spreadsheet data, then a Temp table is
    probably the easiest way to go.
    you can create a Delete query with criteria Is Null on *every* field in the
    Temp table, to pull only the empty rows. use DoCmd.RunQuery in your VBA
    code, or run the SQL statement directly with DoCmd.RunSQL. if you want to
    block the system warning about deleting records, use DoCmd.SetWarnings False
    before the delete action. make sure you remember to follow the action with
    DoCmd.SetWarnings True.

    hth

    <diverdon99@yahoo.co.uk> wrote in message
    news:0glk80lsp3bclt0cd7kg667gmaj1heskqs@4ax.com...
    > John,
    >
    > I apprecite the advice re Excel but as this will be an ongoing import
    > of up to 300 sheets a day then I need an automated Acces answer. The
    > users are not sophisticated and if they are asked to delete rows we
    > may end up up with no data!
    >
    > Surely the approach would be to import into temp table and manipulate
    > data from there before placing in the main table/. If you have any
    > ideas of how to do this it would be great help
    >
    > Don
    >
    >
    >
    >
    > >Hi Don,
    > >
    > >Supplementing what Joe said, here are a couple of approaches that may
    > >help:
    > >
    > >1) In Excel, define a named range on the DBReccData sheet including
    > >however many rows you want to import. Then specify that range in the
    > >TransferSpreadsheet statement.
    > >
    > >2) When editing the table in Excel, if you have to remove data don't
    > >just select it and hit the Delete key (which does the same as Edit|Clear
    > >Values). Instead, select the entire row(s) and use Edit|Delete. Doing it
    > >this way removes the deleted rows from the UsedRange of the worksheet -
    > >which is the area that Access tries to import. If Access still tries to
    > >import too many rows, go back to Excel and select all the blank rows
    > >below the data and use Edit|Delete on them.
    > >
    > >
    > >On Fri, 23 Apr 2004 23:20:46 +0000 (UTC), diverdon99@yahoo.co.uk wrote:
    > >
    > >>Hi All
    > >>
    > >>The code used to import the excel sheets is below, my problem is that
    > >>the table DBReccData may have from 1 to 33 rows of data. How do I
    > >>determine the point where no data in the row exists and stop
    > >>importing?
    > >>
    > >>Thanks in advance
    > >>Don
    > >>
    > >>Extract ............................................
    > >>For z = 1 To sheetcnt
    > >>Set sheet = xlapp.ActiveWorkbook.Sheets(z)
    > >>
    > >>If (sheet.Name = "DBSiteData") Then
    > >> DoCmd.TransferSpreadsheet transfertype:=acImport, _
    > >> tablename:="Maj_Import", _
    > >> Filename:="C:\Documents and Settings\Don\Desktop\Nevil
    > >>Current\OA_DataEntryv2.xls", Hasfieldnames:=False, _
    > >> Range:="DBSiteData!a2:ap2",
    > >>SpreadsheetType:=acSpreadsheetTypeExcel9
    > >>End If
    > >>
    > >>
    > >>If (sheet.Name = "DBReccData") Then
    > >> DoCmd.TransferSpreadsheet transfertype:=acImport, _
    > >> tablename:="Min_Import", _
    > >> Filename:="C:\Documents and Settings\Don\Desktop\Nevil
    > >>Current\OA_DataEntryv2.xls", Hasfieldnames:=False, _
    > >> Range:="DBReccData!a2:aa33",
    > >>SpreadsheetType:=acSpreadsheetTypeExcel9
    > >>End If
    > >>Next z
    >

  • Next message: Ken Snell: "Re: merging data"

    Relevant Pages

    • Re: URGENT! How to make it to run faster
      ... quicker to run a presentation macro in Excel (possibly created from a .NET ... I also use thread technology on higest value to run the report. ... Imports Excel.XlHAlign ... Dim originalCulture As System.Globalization.CultureInfo = ...
      (microsoft.public.dotnet.general)
    • Re: how to create/use temp table on the fly?
      ... Using the SPID idea, use: ... Columnist, SQL Server Professional ... data to a temp table to compare a date and ID fields to ... >Excel - on the fly. ...
      (microsoft.public.sqlserver.programming)
    • Import HTML Form Data in Multiple Email (multiple text files)
      ... Field 1: User Entry 1 ... I need a way to automate importation of the data from the text inside these ... I'm competent in Excel, but I'm no expert by any means. ... But this only imports one email at a time; ...
      (microsoft.public.excel.misc)
    • Re: Location of Temp File(s) - If any
      ... If you've looked at the temp folder before you load a excel (and a workbook), ...
      (microsoft.public.excel.misc)
    • Re: how to create/use temp table on the fly?
      ... Analysts get a Word Doc file with a tab ... Excel - on the fly. ... sent up to SqlSrv using ADO. ... I am thinking about temp ...
      (microsoft.public.sqlserver.programming)