import only sheet in file regardless of name

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
401
Office Version
  1. 2016
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

VBA Code:
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,099
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
    With wb2
        .Sheets(1).Range("A2:n300").Copy Destination:=wb1.Worksheets("IPAC PROCESSED").Cells(erow, 1)
        .Close savechanges:=False
    End With
 

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
401
Office Version
  1. 2016
How about
VBA Code:
    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!
 

khotso

New Member
Joined
Sep 6, 2021
Messages
14
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jun 10, 2016
Messages
401
Office Version
  1. 2016
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,099
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

if Count = 1 then rename that sheet to " IPAC PROCESSED"
What's the point when the workbook is being saved without any changes?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,099
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,121
Messages
5,768,241
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top