This is my 1st time using macro and would like to create and excel which can locate the files name based on the serial number. However the serial numbers(highlighted in green) are part of the title of the excel workbook. The time and date changes also (highlighted in green) which I don't think matters.
Example of file workbook name:
Within the workbook, in sheet 1 from cell (A47:H53) is the data which needs to be shown. The data in here are all based on formulas. Would like this to be shown as text.
So far I have come up with 2 separate macros but can't figure out how to combine them.
To look up for serial number:
Private Sub Start_Click() Dim SerialNo As String Dim MyObj As Object, MySource As Object, file As Variant SerialNo = InputBox("") file = Dir("\\pena-dsk-003\Upload\DATA\Crane\FT Reports\F1\") While (file <> "") If InStr(file, SerialNo) > 0 Then MsgBox "Found" & file If InStr(file, SerialNo) < 0 Then MsgBox "Not Found" & file End If End If file = Dir Wend End Sub
To copy from closed work book and paste. This however i have to set EnableCalculation to False.
Sub CopyFromClosedWB(strSourceWB As String, _ strSourceWS As String, strSourceRange As String, _ rngTarget As Range) ' copies information from a closed workbook, no input validation! ' use like this to copy information to the active worksheet: ' CopyFromClosedWB "C:\Foldername\Filename.xls", "Sheet1", "A1:D100", Range("A1") Dim wb As Workbook Application.ScreenUpdating = False ' turn off the screen updating Application.StatusBar = "Copying data from " & strSourceWB & "..." On Error Resume Next ' ignore errors ' open the source workbook, read only Set wb = Workbooks.Open(strSourceWB, True, True) On Error GoTo 0 ' stop when errors occur If Not wb Is Nothing Then ' opened the workbook On Error Resume Next ' ignore errors With wb.Worksheets(strSourceWS).Range(strSourceRange) .Copy rngTarget End With On Error GoTo 0 ' stop when errors occur wb.Close False ' close the source workbook without saving changes Set wb = Nothing ' free memory End If Application.StatusBar = False ' reset status bar Application.ScreenUpdating = True ' turn on the screen updating End Sub Sub TestCopyFromClosedWB() CopyFromClosedWB "\\pena-dsk-003\Upload\DATA\Crane\FT Reports\F1\80-178111-12_282214-1868_FTP-Part1_20160428_131241.xls", _ "Sheet1", "A47:H53", Range("A1") End Sub
Appreciate the advice as I would like to learn up on macros.