Excel 2007: Find Sring and Import Range

[JimLes]

New PostJimLes (IS/IT--Management)
1 Mar 11 17:48
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

Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!

Tek-Tips Forums is Member Supported. Click Here to donate.
New PostGavona (TechnicalUser)
2 Mar 11 3:00
This forum is not intended for VBA issues - Please post in forum707: VBA Visual Basic for Applications (Microsoft).

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: VBA Visual Basic for Applications (Microsoft) 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: Copy worksheets from multiple workbooks into specific tab in master. I suspect that you missed the opportunity Tek-tips gives at the bottom of each post to:
*Thank SkipVought
for this valuable post!
It is not too late!

Regards,

Gavin

Thank Gavona
for this valuable post!


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!

New PostJimLes (IS/IT--Management)
2 Mar 11 10:23
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??

 

[SkipVought]

hi,

I have an excel 2007 spreadsheet that imports data from a PeopleSoft Query output (excel).

Could you explain 1) the PROCESS that produces the output and 2) the format of the output from ProplsSoft. Please post the actual file name and file extension of this file.

Skip,

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

 

[JimLes]

Hey Skip,

1) Peoplesoft generates an excel query in version 2003.
2) I have code in my excel 2007 spreadsheet to import from any .xls. The file name changes with each query. Columns remain the same A thru W. Rows with data are dynamic so I need to find the last used cell. Headers are dynamic so I need to find the row that contains the header and then select the use range after that.

I think I am getting close with the code below:

Columns("A:A").Select
    Selection.Find(What:="EE ID", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Selection.Offset(1, 0).Select

I can find the header row no problem, it is just selecting the used range after that.

 

[SkipVought]

1) Peoplesoft QUERY generates an excel WORKBOOK in version 2003.

Workbooks(PeopleSoft).Sheets(1).UsedRange.Copy

ThisWorkook.Sheets(YourDestinationSheet).Range(YourDestinationRange).PasteSpecial xlpasteALL

Make sure that the workbook, worksheet and range references are correct, but that's all you really need.




Skip,

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

 

[JimLes]

Well, I am pasting them into my excel 2007 worksheet at a certain range. Plus, I only need the data after I find the header in the excel 2003 workbook.

 

[SkipVought]

UsedRange will reference ALL the header & data.

And as I implied, YourDestinationRange should be defined at whatever 'certain range' you decide.

Those two statements will accomplish ALL you need to do.

Skip,

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

 

[Gavona]

Rather than UsedRange I would use the LastCell.

Without VBA do the find as before then Ctrl-Shft-End

In code, after tidying up should be something like:

Dim r as range

Set r = Workbooks(ImportFile).Worksheets(TabName).Columns("A:A").Find(What:="EE ID", _
   After:=ActiveCell, LookIn:=xlFormulas, _
   LookAt:=xlPart, SearchOrder:=xlByRows, _
   SearchDirection:=xlNext, MatchCase:=False, _
   SearchFormat:=False).Offset(1, 0)
 
Range(r, r.SpecialCells(xlLastCell)).Copy

Set r = Workbooks(ControlFile).Sheets(DataEntry).Range("A65536").End(xlUp).Offset(1, 0)

r.Paste

Gavin

 

[JimLes]

Thanks for your assistance.

I finally pieced together the code I needed. This finds the header and then selects the row after the header. Then it selects the used range between columns A and W. Works Perfect.

Columns("A:A").Select
    Selection.Find(What:="EE ID", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    Range(ActiveCell, ActiveCell.End(xlDown).Offset(0, 22)).Copy

 

[JimLes]

Gavin, I like the way you made the code all nice and tidy!!!

 

[Gavona]

Note how instead of Activate and Selection I just specifically define which workbook and worksheet the range is in.


Gavin

 

[SkipVought]

"Excel 2007: Find Sring and Import Range"

'...
with Workbooks(ControlFile).Sheets(DataEntry)
  Set r = .[b]Cells(.rows.count, "A")[/b].End(xlUp).Offset(1, 0)
End With
'....

Skip,

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

 

[Gavona]

Ah yes, that's the second time you've had to point that out to me!
Jim, different versions of Excel have different numbers of rows. Skip's code makes sure that the code will work in any version.



Gavin