import only sheet in file regardless of name

[jordanburch]

Hey Guys

I have the below. I just want it to open the file and then import the sheet regardless of the file name. Basically it has one sheet and they client keeps changing the name and they arent going to be consistent so I need a solution. Any ideas? The sheet is called IPAC PROCESSED in this example.

Jordan

Sub DCASIMPORTIPACPROCESSED()
Worksheets.Add().Name = "IPAC PROCESSED"
Dim myfile As String
Dim erow As Long
Dim filepath As String
Dim wb1 As Workbook, wb2 As Workbook

    Dim fn As String

fn = Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6)
    
Application.ScreenUpdating = False
Set wb1 = ThisWorkbook
filepath = "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\DCAS\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & fn & Right(ThisWorkbook.Worksheets("Variables").Range("a2").Value, 2) & "\"
myfile = Dir(filepath)
Do While Len(myfile) > 0 And myfile <> "suspense automation.xlsm"

    erow = wb1.Sheets("IPAC PROCESSED").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Set wb2 = Workbooks.Open(filepath & myfile)
    With wb2
        .Sheets("IPAC Processed").Range("A2:n300").Copy Destination:=wb1.Worksheets("IPAC PROCESSED").Cells(erow, 1)
        .Close savechanges:=False
    End With
    myfile = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[Fluff]

How about

    With wb2
        .Sheets(1).Range("A2:n300").Copy Destination:=wb1.Worksheets("IPAC PROCESSED").Cells(erow, 1)
        .Close savechanges:=False
    End With

 

[jordanburch]

How about

    With wb2
        .Sheets(1).Range("A2:n300").Copy Destination:=wb1.Worksheets("IPAC PROCESSED").Cells(erow, 1)
        .Close savechanges:=False
    End With

worked sweet! I assume it just imports the first sheet right? so if there is a second sheet I could just move the 1 to a 2?

THANKS FLUFF!

 

[Fluff]

I assume it just imports the first sheet right?

That's right.

 

[khotso]

I think the best way is to count the number of sheets in the workbook. if Count = 1 then rename that sheet to " IPAC PROCESSED" . In that way you will always have one consistent name

count = wb2.sheets.count

then maybe,

For each ws in wb2.sheets
ws.name = "IPAC PROCESSED "
next ws

 

[jordanburch]

I think the best way is to count the number of sheets in the workbook. if Count = 1 then rename that sheet to " IPAC PROCESSED" . In that way you will always have one consistent name

count = wb2.sheets.count

then maybe,

For each ws in wb2.sheets
ws.name = "IPAC PROCESSED "
next ws

ok thanks bud!

 

[Fluff]

if Count = 1 then rename that sheet to " IPAC PROCESSED"

What's the point when the workbook is being saved without any changes?

 

[khotso]

What's the point when the workbook is being saved without any changes?

we can easily add wb2.save

 

[Fluff]

That assumes the OP wants it saved.
If there is only one sheet in the workbook, the name is irrelevant. If there is more than one sheet, then there are better way of ensuring the correct sheet is used.