opening external files with VBA

[richanor]

Hi

I was wondering if somebody could help me out here:

On the first worksheet of my workbook I have a list of file names in cells I11:I27. The filenames have been 'compiled' using the concatenate function, so for example, although cell I11 displays:

Richard 2007-09.xls,

the cell contents are really:

=CONCATENATE(G20," ",L9,"-",J9,".xls")

Using VBA, I want to be able to open each respective file in cells I11:I27, copy the contents into this workbook (sheet=raw), and close it. My problem is that I don't know how to tell excel the filename in VBA, because the cells contents are not really the filename - they are a formula.

I hope I have explained this ok, and if anyone can help me out I'd appreciate it.

thanks

Rich

 

[stanleydgromjr]

Rich,

Here you go. This code has been tested in my environment, but, without the:
Application.Run "'Sales data viewer.xls'!FormatMyData"

Please save/copy your original "formatter.xls" to another location for safekeeping/backup.

The new workbook, for testing, will be "formatter.xls", with the following sheets:
front
cash
share
annual
raw

Copy sheet "front" from the original "formatter.xls" (that has been saved/copied to another location for safekeeping/backup) to the new "formatter.xls" sheet "front".


Please TEST this FIRST in a COPY of your workbook.

Copy the below code, and paste it into the Module1.

Option Explicit
Sub OpenCopyCloseFormatSaveNewFiles()

    Dim wbToOpen As Workbook
    Dim wbCodeBook As Workbook          'formatter.xls
    Dim lngLoopCtr As Long
    Dim strFileNameToOpen As String
    Dim strEmployeeFileName As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    On Error Resume Next

    Set wbCodeBook = ThisWorkbook
    wbCodeBook.Worksheets("raw").Select
    
    With wbCodeBook
        For lngLoopCtr = 11 To 27 Step 1
            strFileNameToOpen = wbCodeBook.Worksheets("front").Cells(lngLoopCtr, "I").Value
            With Application.FileSearch
                .NewSearch
                '******************************************************************************
                'Change path to suit
                .LookIn = "c:\my documents\data"

                '******************************************************************************

                .FileType = msoFileTypeExcelWorkbooks
                .Filename = strFileNameToOpen
                Workbooks.Open Filename:=.LookIn & "\" & strFileNameToOpen
                Set wbToOpen = Workbooks.Open(Filename:=.Filename)
                Windows(.Filename).Activate
                wbToOpen.Worksheets("Sheet1").Select
                Cells.Select
                Selection.Copy

                Windows("formatter.xls").Activate

                Worksheets("raw").Select
                Cells.Select
                Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=False
                Range("A1").Select
                Windows(.Filename).Activate

                Range("A1").Select
                Application.CutCopyMode = False
                Windows(.Filename).Close savechanges:=False

            End With

            strEmployeeFileName = wbCodeBook.Worksheets("front").Cells(lngLoopCtr, "G").Value

            'Your code to manipulate/format data in sheets "raw", "annual", "share", "cash".
            Application.Run "'Sales data viewer.xls'!FormatMyData"

            Workbooks.Add
            Sheets("Sheet1").Select
            Sheets("Sheet1").Name = "raw"
            wbCodeBook.Worksheets("raw").Cells.Copy Sheets("raw").Cells
            Application.CutCopyMode = False

            Sheets.Add
            Sheets("Sheet2").Select
            Sheets("Sheet2").Name = "annual"
            wbCodeBook.Worksheets("annual").Cells.Copy Sheets("annual").Cells
            Application.CutCopyMode = False

            Sheets.Add
            Sheets("Sheet3").Select
            Sheets("Sheet3").Name = "share"
            wbCodeBook.Worksheets("share").Cells.Copy Sheets("share").Cells
            Application.CutCopyMode = False

            Sheets.Add
            Sheets("Sheet4").Select
            Sheets("Sheet4").Name = "cash"
            wbCodeBook.Worksheets("cash").Cells.Copy Sheets("cash").Cells
            Application.CutCopyMode = False

            '******************************************************************************
            'You can change the path to where you want the new employee files are to be saved
            '  "c:\my documents\data\"
            ActiveWorkbook.SaveAs Filename:="c:\my documents\data\" & strEmployeeFileName & ".xls", FileFormat:= _
                xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
                , CreateBackup:=False
            '******************************************************************************

            ActiveWorkbook.Save
            ActiveWorkbook.Close

            'Clear sheets cash, share, annual, raw for next file to open/employee file to save
            wbCodeBook.Worksheets("raw").Cells.Clear
            wbCodeBook.Worksheets("cash").Cells.Clear
            wbCodeBook.Worksheets("share").Cells.Clear
            wbCodeBook.Worksheets("annual").Cells.Clear

        Next lngLoopCtr

    End With

    On Error GoTo 0

    wbCodeBook.Worksheets("front").Select

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

End Sub

You can adjust the areas (see the notes) in the code that begin and end with:
'******************************************************************************

Please TEST this FIRST in a COPY of your workbook.

Have a great day,
Stan

 

[richanor]

Hi Stan

Thanks a lot for doing that for me - it does exactly what I wanted! Unfortunatley, there is now a problem with my code. Before I imported your code into the spreadsheet, I was able to manually paste my raw data onto sheet=raw, and then run my macro, and it would sort the data no problem. Unfortunatley, when I run your code - at the point where it calls my macro (FormatMyData), it stalls, and give the following error:

"Compile Error: Variable not defined"

and it highlights the "r" in the second line of the following code.

    Sheets("raw").Select
    For Each r In Sheets("postcodes").Range("C:C")
    If r <> "" Then
    Findvalue = r.Value
    Replvalue = r.Offset(0, 1)
    Sheets("raw").Select

    Cells.Replace What:=Findvalue, Replacement:=Replvalue, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False

    End If
    Next r

This code worked fine before I imported your code, and if I remove your code it works again. I can't work out what the problem is. Do you have any ideas?

many thanks

Rich

 

[stanleydgromjr]

Rich,

All of the code I write uses "Option Explicit", which requires me to define each variable.

Try this:

    Dim r
    Sheets("raw").Select
    For Each r In Sheets("postcodes").Range("C:C")
        If r <> "" Then
            Findvalue = r.Value
            Replvalue = r.Offset(0, 1)
            Sheets("raw").Select
            Cells.Replace What:=Findvalue, Replacement:=Replvalue, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
        End If
    Next r

Have a great day,
Stan

 

[richanor]

Ah - I wondered what that was. I tried a few things and couldn't get it to work, so I tried commenting out the "option explicit" line, and it seemed to work fine.

Is it likely to cause any problems if I leave it commented out?

There is just one small problem, and I'm not sure if there's a simple solution? The first bit of code runs fine: the raw data file opens, copies the raw data into the formatter.xls, and then runs my code to format it.

One of the things my code does is to create a pivot table on the "raw" sheet (top left of the pivot table is in cell AI2000). It then creates a pivot chart based on the pivot table, and places the chart on the "annual" sheet.

When your last peice of code runs:

Workbooks.Add 
            Sheets("Sheet1").Select 
            Sheets("Sheet1").Name = "raw" 
            wbCodeBook.Worksheets("raw").Cells.Copy Sheets("raw").Cells 
            Application.CutCopyMode = False 

            Sheets.Add 
            Sheets("Sheet2").Select 
            Sheets("Sheet2").Name = "annual" 
            wbCodeBook.Worksheets("annual").Cells.Copy Sheets("annual").Cells 
            Application.CutCopyMode = False 

            Sheets.Add 
            Sheets("Sheet3").Select 
            Sheets("Sheet3").Name = "share" 
            wbCodeBook.Worksheets("share").Cells.Copy Sheets("share").Cells 
            Application.CutCopyMode = False 

            Sheets.Add 
            Sheets("Sheet4").Select 
            Sheets("Sheet4").Name = "cash" 
            wbCodeBook.Worksheets("cash").Cells.Copy Sheets("cash").Cells 
            Application.CutCopyMode = False

It copies the "raw" sheet, and the "annual" sheet, but the source data becomes unlinked - so the chart is now a "static chart". Is there anyway to copy both sheets into the new workbook whilst keeping the chart linked with it's source data?

If not don't worry - it's working great, and I really appreciate your help.

Rich

 

[stanleydgromjr]

Rich,

I would leave the "Option Explicit" line not commented out.

Just add the one line of code to your new lines of code:
Dim r

It copies the "raw" sheet, and the "annual" sheet, but the source data becomes unlinked - so the chart is now a "static chart". Is there anyway to copy both sheets into the new workbook whilst keeping the chart linked with it's source data?

I am not sure how to accomplish the above quote.

But, if you could send me "Formatter.xls", and, this file and macro "Application.Run "'Sales data viewer.xls'!FormatMyData", and one of the latest customer files, I will give it a try.

I could send you a Private Message with my e-mail address.

Have a great day,
Stan