Excel 2007: Find Sring and Import Range

[JimLes]

Hello,

I have an excel 2007 spreadsheet that imports data from a PeopleSoft Query output (excel). The code works perfectly when the headers are in the same place (row 3) but depending on how you run the query (straight to excel or scheduler) you will get extra rows for the prompts (5).

What I need to do is build in code to find the first header which is "EE ID" and then import the row to 500 after that. Any ideas on how I can accomplish this??



Here is my code:

Private Sub cmdImport_Click()
Dim ImportFile As String
Dim ImportTitle As String
Dim TabName As String
Dim ControlFile As String

'Open common dialog and get filename
ImportFile = Application.GetOpenFilename( _
"Excel Files, *.xls,All Files, *.*")
ImportTitle = _
Mid(ImportFile, InStrRev(ImportFile, "\") + 1)

'Check cancel wasn't clicked
If ImportFile = "False" Then
Exit Sub
End If

'Import file
TabName = "DataEntry"
ControlFile = ActiveWorkbook.Name
Workbooks.Open FileName:=ImportFile
ActiveSheet.Name = TabName
    Range("A3:W500").Select
    Selection.Copy
    Windows(ControlFile).Activate
    Sheets("DataEntry").Select
    ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select 'starts at row 65536, goes up to the last row of data and then offsets one row
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Windows(ImportTitle).Activate
Application.CutCopyMode = False
ActiveWorkbook.Close SaveChanges:=False
Windows(ControlFile).Activate
Sheets("DataEntry").Select
    'Range("A5:AA60000").Select
    'Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        'False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Application.CutCopyMode = True
MsgBox "Data has been successfully imported!"
UserForm2.Hide
End Sub

 

[Gavona]

This forum is not intended for VBA issues - Please post in forum707.

However the non-vba solution in 2003 is (optionally select the column in which to search then) Edit, Find ....
And there is a macro recorder to show what code that generates.
And forum707 to help you to refine it.

If your data has a blank row above the heading row and does not include blank rows then the CurrentRegion property on the results of the find might select all you want to import. (Non-VBA in 2003 = Edit,GoTo,Special,CurrentRegion or Ctrl-A)

Think about whether that text string might appear anywhere before the Header Row. If so you will need to think of some logic to identify the correct occurrence.

Also you don't need to select and then do something with the selection. There are advantages to shortening e.g.
Range("A3:W500").Select
Selection.Copy

to Range("A3:W500").Copy

By the way, I just looked at one of your previous posts thread707-1546580. I suspect that you missed the opportunity Tek-tips gives at the bottom of each post to:
[red]*[/red]Thank SkipVought
for this valuable post!
It is not too late!

Regards,

Gavin

 

[JimLes]

Gavin, you are always so helpful!!

I will do so on my other posts to give credit where credit is due!!

I understand using the macro recorder to find the text (sorry for my brain lapse) but how will I code it to find the row after that??

Should I move this post??

 

[SkipVought]

Just post these questions in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}