New Sheets for hyperlinked files
I have a macro that will open a list of hyperlinked files, now i want
to compile information out of those files into a master file. The file names all start with the job number (e.g. Job3*.xls), and I'd like to make a new sheet for each job number. I'm pretty new to this, so I don't even know if this is possible. Once the sheets are created, I've got the data pulling macro working, but right now I have to create the new sheets manually first. Thanks |
New Sheets for hyperlinked files
here's a demo. The 'demo' procedure is the entrypoint. all it does is to
allow the user to point at a file and then it passes that filename tothe grab routine. All your code would need to do is make the same call... Option Explicit ' this grabs a workbook and copies the data in sheet1 to ' the active workbook Sub demo() Dim fn As String fn = Application.GetOpenFilename() If UCase(fn) < "FALSE" Then GrabData (fn) End Sub Sub GrabData(fn As String) Dim filename As String Dim ws As Worksheet Dim wb As Workbook Dim source As Range Set ws = Worksheets.Add() filename = GetFileName(fn) ws.Name = filename Set wb = Workbooks.Open(fn) Set source = wb.Worksheets(1).UsedRange With ws .Range("A1").Resize(source.Rows.Count, source.Columns.Count).Value = source.Value End With wb.Close False End Sub Function GetFileName(fn As String) As String Dim pos As Long pos = InStrRev(fn, "\") If pos 0 Then GetFileName = Mid(fn, pos + 1) Else GetFileName = fn End If 'strip off the extension GetFileName = Left(GetFileName, Len(GetFileName) - 4) End Function "Craig" wrote: I have a macro that will open a list of hyperlinked files, now i want to compile information out of those files into a master file. The file names all start with the job number (e.g. Job3*.xls), and I'd like to make a new sheet for each job number. I'm pretty new to this, so I don't even know if this is possible. Once the sheets are created, I've got the data pulling macro working, but right now I have to create the new sheets manually first. Thanks |
All times are GMT +1. The time now is 10:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com