How do i code a VBA to ask the user to select which file to use

**kenadams378** · 05-15-2012, 04:45 AM

The current VBA i have is as follows;

Workbooks.Open Filename:= _
        "\\Via.novonet\dfs\LIFE\F\test\test\test\Source Files\test\test\test 300412.xls"
    Range("A1:DK47073").Select
    Selection.Copy
    Windows("test.xlsm").Activate
    Sheets("test Data").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("test pivot").Select
    Range("D15").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh

I want to be able to code it so the macro asks the user which file to use but will use the same steps following this?

Any ideas

**kenadams378** · 05-15-2012, 05:25 AM

I have tried this but there is an error somewhere as it pastes all the data from a summary sheet to the next sheet in the workbook.

I need it to open a new file, copy all the data and paste it to "test sheet" then close the opened sheet.

NewFN = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    'Workbooks.Open Filename:=NewFN
'
    
    Range("A1:DK47073").Select
    Selection.Copy
    Windows("test.xlsm").Activate
    Sheets("test").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("test").Select
    Range("D15").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh

**arlu1201** · 05-15-2012, 05:46 AM

It will be good if you try explaining your problem with the help of an attached excel file. Can you clearly state what you require the macro to do?

Also, you can do away with the select & activate statements.

**kenadams378** · 05-15-2012, 06:47 AM

I required coding to prompt the user for the file, copy all the data from the data sheet of the opened file and paste it into the data tab of the summary template.

Attached is the summary document.

**kenadams378** · 05-15-2012, 08:14 AM

Can you view the attachment

**arlu1201** · 05-15-2012, 08:28 AM

I just edited the code you had to this

Sub Macro7()
'
' Macro7 Macro

    Dim wbOpen As Workbook
     
    Set wbOpen = Workbooks.Open _
   ("\\Via.novonet\dfs\LIFE\F\INVReporting\CONFIDENTIAL\Inv_Reporting_2012\Source Files\S29" & Range("FileName"))
     
          
    With wbOpen
        .Worksheets("Data").UsedRange.Copy ThisWorkbook.Worksheets("S29 Data").Range("A1")
        .Close False
End With

Add the pivot refresh code after this.

**kenadams378** · 05-15-2012, 08:36 AM

Thanks, i get the following error message with this line;


Set wbOpen = Workbooks.Open _
   ("\\Via.novonet\dfs\LIFE\F\INVReporting\CONFIDENTIAL\Inv_Reporting_2012\Source Files\S29" & Range("FileName"))

Error is Method 'Range' of object _global failed

**MarvinP** · 05-15-2012, 08:36 AM

Try this line of code:

Application.FindFile

**kenadams378** · 05-15-2012, 08:45 AM

Still not happy.

Application.FindFile
    
    With wbOpen
        .Worksheets("Data").UsedRange.Copy ThisWorkbook
        Window.Worksheets("S29 Data").Range ("A1")
        .Close False
        
    Windows("Exposure template Macro.xlsm").Activate
    Sheets("S29 Data").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("S29 Pivot").Select
    Range("D15").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh

Once the file has been opened it needs to copy the workbook and then switch back to the previous workbook and paste the copied data into the sheet.

I'm not sure this is doing those things.

**arlu1201** · 05-15-2012, 08:56 AM

Ok hold on. I have already provided you the code to do the copy paste. What is currently not working is the workbooks.open part. Can you give me the path from where you open the file ? Also, give me the name of the file.

**kenadams378** · 05-15-2012, 09:01 AM

The filepath of the data is;

\\Via.novonet\dfs\LIFE\F\INVReporting\CONFIDENTIAL\Inv_Reporting_2012\Source Files\S29

File name will be S29 Data then DDMMYY (this will be a different date each month, which is why i wanted to prompt the user to select which file

**arlu1201** · 05-15-2012, 09:40 AM

There is no drive letter before the path? Like C or D?

**kenadams378** · 05-15-2012, 09:43 AM

It's a network drive so doesn't need to be?

**arlu1201** · 05-15-2012, 09:50 AM

Try this

dim FName as variant

chdir "\\Via.novonet\dfs\LIFE\F\INVReporting\CONFIDENTIAL\Inv_Reporting_2012\Source Files\"
FName = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the file")
If FName = "False" Then
    MsgBox "You have not selected a file."
    Exit Sub
Else
    Workbooks.Open Filename:=FName
    FName = ActiveWorkbook.Name
End If
With workbooks(Fname)
        .Worksheets("Data").UsedRange.Copy ThisWorkbook.Worksheets("S29 Data").Range("A1")
        .Close False
End With

**kenadams378** · 05-15-2012, 09:57 AM

I have updated and re run


.Worksheets("Data").UsedRange.Copy ThisWorkbook.Worksheets("S29 Data").Range("A1")

Error message is object required.

**arlu1201** · 05-15-2012, 10:04 AM

Sorry, i have made the changes to the above code.

**kenadams378** · 05-15-2012, 10:16 AM

Spot on, works a treat, thank you for help, really apprecaite it!

**MarvinP** · 05-16-2012, 07:26 PM

Hi,

Did you ever try "Application.FindFile"??

How do i code a VBA to ask the user to select which file to use

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Re: How do i code a VBA to ask the user to select which file to use

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions