ACCESS FEED: Re: [MS_AccessPros] Removing junk from Excel files for import

Senin, 02 Juli 2012

Re: [MS_AccessPros] Removing junk from Excel files for import

Hi Sam,

you can look for column labels that won't change -- that will be nRow1

'~~~~~~~~~~~~~~~~~
   Dim xlSht As Excel.Worksheet

   Dim nRow1 As Long _
      , nRow2 As Long

   Set xlSht = ActiveSheet 'CHANGE THIS

   nRow1 = 1 'or wherever you figure it out to be

    With xlSht

       ' Find the last row of data in column 1 (A)
       nRow2 = .Cells(Rows.Count, 1).End(xlUp).Row

      'add range name
      .Names.Add Name:="myDataRange", RefersTo:="=SheetName!A" & nRow1 & ":H" & nRow2

   End With 'xlSht

   'save and close workbook

   Set xlSht = Nothing

WHERE
xlSht is an object variable for the Excel worksheet
SheetName is the name of the worksheet
A is the first column
H is the last column (you can detect this too)

hope this gets you off to a good start :)

Warm Regards,
Crystal

*
   (: have an awesome day :)
*

________________________________
From: sarmbraugh
Subject: Re: [MS_AccessPros] Removing junk from Excel files for import

Thanks John and Crystal.

So really what the client wants is something that will programatically detect junk and ignore it. I can think of some kind of goofy ways to do this, like go through row by row -- or maybe the first 20 rows or something -- see how many columns are populated going across, and decide the row containing column headers is the first one where the most columns are populated?

Can't say I really want to do this, but...

Sam

--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace> wrote:
>
> Hi Sam,
>
> without writing code to open it and detect the range, the best thing to do is this:
>
> 1. open the Excel Workbook
> 2. select the sheet with your data
> 3. click in a data column that has values all the way down
> 4. press Ctrl-Shirt-* to select the range
>    if it is not right, then manually select all your data
>
> 5. click in the Address Box (where it says B1, C12, whatever is your active cell)
> 6. create a Range name --: ie: MyImportData --> and press ENTER
>
> on the Formulas ribbon, choose Name Manager to change what a Name refers to.
> A Name can refer to a value or range of cells.
>
>
> In Access, instead of listing Sheets, list Range Names
>
>
> Warm Regards,
> Crystal
>
> Learn Access on YouTube
> http://www.YouTube.com/LearnAccessByCrystal
>
> *
>    (: have an awesome day :)
> *
>
>
> ________________________________
> From: sarmbraugh <sarmbraugh@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Monday, July 2, 2012 2:38 PM
> Subject: [MS_AccessPros] Removing junk from Excel files for import
>
> More questions!
>
> Does anyone have ideas about simple ways to detect and ignore extraneous data in an Excel file that's being imported, like say if there's extra text in the first few rows before the column headings?
>
> Thanks,
> Sam
>
>

[Non-text portions of this message have been removed]