opening external files with VBA

richanor

Active Member
Joined
Apr 8, 2006
Messages
291
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Rich,

"I want to be able to open each respective file in cells I11:I27".

What is the full path to the folder where the above files reside?

Are there multiple worksheets in each workbook?

Or, do you want to copy only one worksheet?

If you want to copy only one worksheet, what is the sheetname? And, what is the range to copy?

Have a great day,
Stan
 
Upvote 0
Hi Stan

sorry - i could have been clearer!

I have loads of raw data files (all filenames in the format: employee yyyy-mm.xls). I don't know the path to where the files will be stored eventually - do I need to put this in if all of the workbooks I'm using are in the same folder? If so, lets say it's "c:\my documents\data\filename.xls".

The workbook containing the list of filenames is called "formatter.xls", and the idea it to open each respective file, select the entire sheet of "sheet1", paste it into "formatter.xls" into cell A1 of sheet "raw", then close the raw data file.

In the workbook "formatter.xls" there are multiple sheets, which I will be using to format the raw data, but in the raw data files - there are just the standard 3 sheets Sheet1 Sheet2 & Sheet3, but only sheet1 contains data.

Hope this is a bit clearer!

Rich
 
Upvote 0
Rich,

If all the files that you want to copy from are in the same folder, then all we need is the full path to that location.

For example the full path could be:
"c:\my documents\data\"

Do you want to copy a specific range from Sheet1 in all the files?

Will the copied data go into one summary worksheet, or into their own worksheets?

Have a great day,
Stan
 
Upvote 0
Hi again Stan

the data range in the raw data files I am importing will change every time I receive them, and so I just want to select the entire worksheet (sheet1) and import it into formatter.xls onto the sheet called "raw" which will be blank.

I have cobbled together a macro which will do all the formatting I need - it formats the data onto 3 sheets (cash, share, annual). After this I will export a copy of all 4 sheets (cash, share, annual, raw) to a new workbook with the filename "employee.xls" (all employees names are in cells G11:G27 on the sheet "front", along side the raw data filenames)

This second part, I reckon I will be able to botch together, if I can get some help on the first part. i.e. if I can get the raw data files opened and the raw data pasted onto Sheet="raw", I reckon I could give the rest a shot.

many thanks for you time Stan,

Rich
 
Upvote 0
If you dont need to keep the formular in those cells just the file names then a paste special can remove the formular but leave the values as this script does, or maybe a copy paste specail into the cells next to the formular. might get you started.

script/
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'This section selects the cells in the required sheet and removes the formulars used for sorting and
'deletes column A,B and D which are no longer required.

Worksheets("Required").Activate
Range("A1:G405").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A:B,D:D").Select
Selection.Delete Shift:=xlToLeft
/Script
 
Upvote 0
Rich,

Here you go.

I have tested the code in my environment, and with test files to read from (the information on your "front" worksheet) and write to the new files.

There is a section in the code that indicates where you can place your code to format the four sheets "cash, share, annual, raw", and create new workbooks for the employees with copies of the four worksheets.


Please TEST this FIRST in a COPY of your workbook.

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

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

Code:
Option Explicit
Sub OpenCopyCloseFormatSaveNewFiles()
    
'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.
'After this I will export a copy of all 4 sheets (cash, share, annual, raw)
'  to a new workbook with the filename "employee.xls" (all employees names are in cells G11:G27
'  on the sheet "front", along side the raw data filenames).

    Dim wbToOpen As Workbook
    Dim wbCodeBook As Workbook
    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
    With wbCodeBook
        'Loop from row 11 to 27
        'strFileNameToOpen = wbCodeBook.Worksheets("front").Cells(lngLoopCtr, "I").Value
        'strEmployeeFileName = wbCodeBook.Worksheets("front").Cells(lngLoopCtr, "G").Value & ".xls"
        
        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
        
                Set wbToOpen = Workbooks.Open(Filename:=.Filename)
    
                wbToOpen.Worksheets("Sheet1").Cells.Copy wbCodeBook.Worksheets("raw").Cells
                Application.CutCopyMode = False
    
                wbToOpen.Close savechanges:=False
    
            End With

            '------------------------------------------------------------
            strEmployeeFileName = wbCodeBook.Worksheets("front").Cells(lngLoopCtr, "G").Value & ".xls"

            'Your code to format data, create additional sheets,
            '  copy all 4 sheets (cash, share, annual, raw)
            '  to a new workbook with the filename "employee.xls"
            '  (all employees names are in cells G11:G27 on the sheet "front", along side the raw data filenames)






            '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
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

End Sub


Please TEST this FIRST in a COPY of your workbook.

Then run the 'OpenCopyCloseFormatSaveNewFiles' macro.

Have a great day,
Stan
 
Upvote 0
Hi Stan,

Firstly - wow, and thanks a lot! I wasn't expecting help like that. I have insterted the code into the VBA module, and tried to run it, but the bit I thought I'd be OK with, I'm actually struggling with!

I have inserted your code, and in the section which you left for me to add my 'formatting code' I have just put this statement:

Application.Run "'Sales data viewer.xls'!FormatMyData"

because I already had that peice of code saved. After this code has run, I was hoping to be able to get excel to copy 4 worksheets (cash, share, annual & raw) to another workbook, and save it as "employee.xls", where the empolyees are listed in cells G11:G27. I thought this would be fairly easy once I saw what you had done with the first part - unfortunately, although very impressive - it is way over my head!

I feel very cheeky asking for more help after what you have already sent me, but if you have time could you show me how to incorporate this?

Thanks again -

Rich
 
Upvote 0
Rich,

I will be posting, tonight or tomorrow morning, the new code for:
"I was hoping to be able to get excel to copy 4 worksheets (cash, share, annual & raw) to another workbook, and save it as "employee.xls", where the empolyees are listed in cells G11:G27."

I have been testing my workbook with those additional sheetnames.

What file contains the "cash, share, annual & raw" sheetnames that you are referring to?

Have a great day,
Stan
 
Upvote 0
Hi Stan

Apologies for not replying sooner - my wife and son weren't too well for the last few days, and I haven't had chance to get on the computer.

Thanks once again for your reply - the cash, share, annual & raw sheets are in a file called "formatter.xls". Initially all four of these sheets are blank - the individual raw data files are copied and pasted onto sheet "raw", and then my formatting code is run (the name of the macro is FormatMyData). My code creates various tables & charts from the raw data and places them on the sheets "cash, share & annual". Once my code has run I would like to export a copy of sheets "cash", "share", "annual", and "raw" into another workbook, save it, and then clear these 4 sheets in the original file (formatter.xls) ready to import the next raw data file.

Hope I explained this OK, and thanks again,

Rich
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top