Open file and copy paste

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
692
Office Version
  1. 2013
Is there a way were user can select the file and once the file is selected then it will copy and paste the same in another file

I have this code and it does copy past. But need to modify it so that user can select the file and then will copy paste. Any suggestion

VBA Code:
'=====================================================================================
    ' 1. Check Raw data in folder sheet MSP Response Time With Hubtime
'=====================================================================================
    If sheets("Reference").Range("c2").Value <> "MSP Response Time With Hubtime TTs" Then
    MsgBox "MSP Response Time With Hubtime TTs Raw data file not avaliable" & vbNewLine & "Download raw file & Run the scrip again", vbCritical, "Raw File Required"
    End
    End If
 
    If Len(Dir(Fpath & "\" & "MSP Response Time With Hubtime TTs.xlsx")) = 0 Then
    MsgBox "_MSP_Response_Time_With_Hubtime_TTs_v1.0(1) in the folder" & vbNewLine & Fpath & vbNewLine & "Download raw file & Run the scrip again"
    End
    End If

'=====================================================================================
    ' 1. ImportMSP Response Time With Hubtime  MSP Response Time With Hubtime TTs.xlsx
'=====================================================================================
       
    If sheets("Reference").Range("c2").Value = "MSP Response Time With Hubtime TTs" Then
   
    sheets("MSP Response Time RawData").Visible = True
    sheets("MSP Response Time RawData").Unprotect "etmc123$"
    Windows("TicketExceeds24hours.xlsm").Activate
    ActiveWorkbook.sheets("MSP Response Time RawData").Activate
    Cells.Select
    Selection.ClearContents
    Application.CutCopyMode = False
       
    Set wbsource = Workbooks.Open(Fpath & "\" & "MSP Response Time With Hubtime TTs.xlsx")
           
    Windows("MSP Response Time With Hubtime TTs.xlsx").Activate
        ActiveSheet.Range("B8:BA200000").Select
        'Cells.Select
        Selection.Copy
       Windows("TicketExceeds24hours.xlsm").Activate
        ActiveWorkbook.sheets("MSP Response Time RawData").Activate
        ActiveSheet.Paste
        Application.CutCopyMode = False
    wbsource.Close
    End If


'=====================================================================================
    ' 2. Check Raw data in folder sheet Summary Of PR Detailed Handled By
'=====================================================================================
    If sheets("Reference").Range("c3").Value <> "Summary Of PR Detailed Handled By" Then
    MsgBox "Summary Of PR Detailed Handled By Raw data file not avaliable" & vbNewLine & "Download raw file & Run the scrip again", vbCritical, "Raw File Required"
    End
    End If
 
    If Len(Dir(Fpath & "\" & "Summary Of PR Detailed Handled By.xlsx")) = 0 Then
    MsgBox "Summary Of PR Detailed Handled By in the folder" & vbNewLine & Fpath & vbNewLine & "Download raw file & Run the scrip again"
    End
    End If

'=====================================================================================
    ' 2. ImportSummary Of PR Detailed RawData  Summary Of PR Detailed RawData.xlsx
'=====================================================================================
       
    If sheets("Reference").Range("c3").Value = "Summary Of PR Detailed Handled By" Then
   
    sheets("Summary Of PR Detailed RawData").Visible = True
    sheets("Summary Of PR Detailed RawData").Unprotect "etmc123$"

    Windows("TicketExceeds24hours.xlsm").Activate
    ActiveWorkbook.sheets("Summary Of PR Detailed RawData").Activate
    Cells.Select
    Selection.ClearContents
    Application.CutCopyMode = False
       
    Set wbsource = Workbooks.Open(Fpath & "\" & "Summary Of PR Detailed Handled By.xlsx")
           
    Windows("Summary Of PR Detailed Handled By.xlsx").Activate
        ActiveSheet.Range("B8:BA200000").Select
        'Cells.Select
        Selection.Copy
       Windows("TicketExceeds24hours.xlsm").Activate
        ActiveWorkbook.sheets("Summary Of PR Detailed RawData").Activate
        ActiveSheet.Paste
        Application.CutCopyMode = False
    wbsource.Close
    End If
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

yinkajewole

Active Member
Joined
Nov 23, 2018
Messages
281
this will allow the user to select the file
<
VBA Code:
Dim myFile as String
With Application.FileDialog(msoFileDialogFilePicker)
    .Show
    myFile = .SelectedItems(1)
End With
'myFile will be the variable for the file selected
 

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
692
Office Version
  1. 2013
Dim myFile as String With Application.FileDialog(msoFileDialogFilePicker) .Show myFile = .SelectedItems(1) End With 'myFile will be the variable for the file selected
It is not allowing to open the file
 

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
692
Office Version
  1. 2013

ADVERTISEMENT

I mean it allows to select the file.
But after selection it should open the file. That is not working.
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
247
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hello Vmjan02

Is this what you are looking for?

VBA Code:
Sub GetAndOpenFile()
   Dim fPath As String
   Dim fName As String
   Dim WB as Workbook
        fPath = "ThisWorkbook.Path\"  
        fName = Application.GetOpenFilename("Excel Files (" & fPath & "*.xlsm)," & fPath & "*.xlxs")     ' Will list any Excel file in that path with the extension of 'xlsm' or 'xlxs'.
        Set WB = Workbooks.Open(fName)
End Sub

I hope this helps.
TotallyConfused
 

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
692
Office Version
  1. 2013
Dim fPath As String Dim fName As String Dim WB as Workbook fPath = "ThisWorkbook.Path\" fName = Application.GetOpenFilename("Excel Files (" & fPath & "*.xlsm)," & fPath & "*.xlxs") ' Will list any Excel file in that path with the extension of 'xlsm' or 'xlxs'. Set WB = Workbooks.Open(fName)
Yes. This is what i was looking for. Thanks a ton
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
247
Office Version
  1. 365
Platform
  1. Windows
You're very welcome. Glad I could help and thanks for the feedback.
 

Forum statistics

Threads
1,141,415
Messages
5,706,306
Members
421,441
Latest member
VapesRub

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
Top