Open Workbook

#BoB# · Dec 5, 2008

Hi,

Can anyone tell me the code to open a workbook from a specific path!!!

eg: I want to open the workbook "Invoice.xls" from the path: D:\2008\December\Workflow

After opening this workbook, I would like to go to the sheet "Raw Data" and then copy the entire Raw 10. And close the workbook.

Please help me with a code to perform this action.

Thanks in advance.

Regards,

Jon von der Heyden · Dec 5, 2008

Hello again Bob

Try:

Workbooks.Open("D:\2008\December\Workflow\Invoice.xls")

You can also call the open file dialog with:

Application.Dialogs(xlDialogOpen).Show

And the you can navigate to the desired workbook.

What data range do you want to copy, and where do you want to paste it? And what do you want to paste? That is, values only, formats, formulas or everything?

#BoB# · Dec 5, 2008

Hey,

Thanks for the quick reply.

Now, I have files like Invoice_SDunn, Invoice_AWhite, Invoice_SStuart, and so on...(Invoice_USERNAME) around 100 files.

I need to open each of the files and go to the Sheet "Raw Data" and copy the entire "Row 10" and paste it in the file where the macro is (paste as value, one row after another). And then close the Invoice file and move on to the next invoice file.

Hope I am making sense

Thanks a ton for your help again

Regards,

Jon von der Heyden · Dec 5, 2008

Hi Bob

Try this, but please test this on a copy of your original wrkbook first:

Sub ForBob() With Application .ScreenUpdating = False .EnableEvents = False End With Dim fso As Object, fld As Object, fil As Object, fldPath As String, wbSrc As Workbook, wbCur As Workbook fldPath = "D:\2008\December\Workflow\" Set wbCur = ActiveWorkbook Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.getfolder(fldPath) On Error GoTo ErrHandler For Each fil In fld.Files If InStr(LCase(fil.Name), "invoice") > 0 Then Set wbSrc = Application.Workbooks.Open(fil.Path) wbSrc.Sheets("Raw Data").Range("10:10").Copy wbCur.Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Offset(1) wbSrc.Close False End If Next fil ErrHandler: Application.EnableEvents = True End Sub

NOTE: This pastes the data into a sheet called "Raw Data" in your current workbook. So assumes that both the "invoice_username" files have a sheet caled "Raw Data", but that your consolidation workbook has the same sheet too.

#BoB# · Dec 5, 2008

Thnks Jon.

I am sure that now I'm very close to solving this problem.

Let me incorporate your codes in the existing program, and see if I can do the rest myself.

Thanks again. Much appreciated

~ChillaX!!!

#BoB# · Dec 6, 2008

Hey Jon,

Finally I am able to put my codes together to work with your fantastic codes

.

Thanks a TON.

~ChillaX!!!

PATSYS · Apr 18, 2009

Jon von der Heyden said:
Hi Bob

Try this, but please test this on a copy of your original wrkbook first:

Sub ForBob() With Application ****.ScreenUpdating = False ****.EnableEvents = False End With Dim fso As Object, fld As Object, fil As Object, fldPath As String, wbSrc As Workbook, wbCur As Workbook fldPath = "D:\2008\December\Workflow\" Set wbCur = ActiveWorkbook Set fso = CreateObject("Scripting.FileSystemObject") Set fld = fso.getfolder(fldPath) On Error GoTo ErrHandler For Each fil In fld.Files ****If InStr(LCase(fil.Name), "invoice") > 0 Then ********Set wbSrc = Application.Workbooks.Open(fil.Path) ************wbSrc.Sheets("Raw Data").Range("10:10").Copy wbCur.Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Offset(1) ************wbSrc.Close False ****End If Next fil ErrHandler: Application.EnableEvents = True End Sub

NOTE: This pastes the data into a sheet called "Raw Data" in your current workbook. So assumes that both the "invoice_username" files have a sheet caled "Raw Data", but that your consolidation workbook has the same sheet too.

Hi Jon

How can I reuse your code above if I were to:

1. Search the folder "C:\Documents\" for all PDF files
2. List all of them in Sheet2 of File1.xls, starting from cell A1
3. I only want the Filenames to be listed, without the entire path and file extension.

Thanks

Jon von der Heyden · Apr 19, 2009

Hello PATSYS

Will you be running it from File1.xls or do you want it to open File1.xls and write the names to Sheet2?

You can place this into a module in File1.xls and run it to return a name of all PDF files in a folder:

Code:

Sub ForPATSYS()

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Dim fso As Object, fld As Object, fil As Object, fldPath As String, i As Long

fldPath = "C:\Documents\"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.getfolder(fldPath)
i = 1

On Error GoTo ErrHandler

For Each fil In fld.Files
    If InStr(LCase(fil.Name), ".pdf") > 0 Then
        Sheets("Sheet2").Range("A" & i) = fil.Name
        i = i + 1
    End If
Next fil


ErrHandler:
Application.EnableEvents = True

End Sub

Pius · Oct 13, 2009

Dear Jon,

I'd like to have a question similar to that of Bob.
My problem arises because FileSearch was removed from Excel VBA 2007. I had a perfect macro, but it has to be restructured.
My question is pointing to how I can look for and open a file.

The aim is to open a file from " V:\ " every day. The files are named NAVmmddyy e.g. NAV101309 (today). I set mmddyy as the variable "date". After opening, only simple copy/paste is needed.

I modified the code you wrote here for Bob, but no success. It stops already at Set fld = fso.getfolder(fldPath).

Could you give a peek at what the problem can be?

Here is the code:

Code:

Dim fso As Object, fld As Object, fil As Object, fldPath As String, wbSrc As Workbook

fldPath = " V:\ "
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.getfolder(fldPath) ' that's where the program stops :(

On Error GoTo ErrHandler

For Each fil In fld.Files
    If fil.Name = "NAV  " & Date & " .xls" Then  ' I want to open a file of today, e.g. NAV101309.xls
        Set wbSrc = Application.Workbooks.Open(fil.Path)
    End If
Next fil
       
        For i = 1 To 600
            nav(i) = Cells(i + 5, 5).Value
        Next i

        Workbooks("TARGET.xls").Sheets("NAV").Activate

        For j = 3 To 94
            Cells(Row, j).Value = nav(i)
        Next j

Workbooks("NAV " & datum & ".xls").Close
ErrHandler:
Application.EnableEvents = True
End Sub

I'm a beginner, so stupid errors may be in the code. I'm looking forward to your answer with a BIG thank you in advance.

Best regards,
Pius from Hungary

chinna9649 · Oct 18, 2009

Hi
I have some problem to be solved can you help me in writing a macro

Its related to timesheet consolidation

Open Workbook

#BoB#

Active Member

Excel Facts

Jon von der Heyden

MrExcel MVP, Moderator

#BoB#

Active Member

Jon von der Heyden

MrExcel MVP, Moderator

#BoB#

Active Member

#BoB#

Active Member

PATSYS

Well-known Member

Jon von der Heyden

MrExcel MVP, Moderator

Pius

New Member

chinna9649

New Member

Similar threads

Forum statistics

Share this page

Open Workbook

Active Member

Excel Facts

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

Active Member

Well-known Member

MrExcel MVP, Moderator

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock