Hi All,
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:
80-178111-12_282214-3801_FTP-Part1_20160308_195958.xls
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 SubTo 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.