Copy data from one sheet to another (1 Viewer)

[Kiron]

Hi Champs

I request a code to be written in access where on pressing a button it asks the user to select an excel file . It then copies a specific sheet from that file and pastes it to another excel workbook specific sheet.
Data can be text or charts or pics, basically whatever is in the selected file should be pasted to another workbook sheet. Code should be able to define sheetnames so i know whats getting colied form where to where.
I have done this in excel vba. But am having a challenge when the same is to be done via access using excel in the background.

Pls advise if this is possible and how.
Thanks heaps

 

[Gasman]

Well I would have thought it would be pretty much the same except you would prefix each command with the name of the Excel object?

In fact, why not just run your macro in Excel from Access?

Run an Excel Macro from Access

I have a linked Excel file that has to be cleared at month end and updated with a .csv file downloaded from a credit card company. I have all of that covered but currently start the process from Access and I manually run a macro while in Excel to import the .csv file do a bunch of formating and...

www.access-programmers.co.uk

 

[vba_php]

In fact, why not just run your macro in Excel from Access?

is that even possible? I've actually never tried that. Kiron, you should look up how to do this. But here are some things that will guide:

=> create an excel instance:

Dim xl As Excel.Application
Set xl = New Excel.Application

=> set the windows file picker:

dim fso as filedialog
set fso = Application.FileDialog(msoFileDialogFilePicker)

=> declare your sources and targets:

dim varFile as variant
Dim wb_src As Excel.Workbook
Dim ws_src As Excel.Worksheet
dim wb_trgt as excel.workbook
dim ws_trgt as excel.worksheet

set your source book, source sheet, target book, and target sheet after picking a file from the fso:

With fso
    If .Show Then
        .AllowMultiSelect = false
        .InitialFileName = "C:\"
        .Filters.Add "EXCEL Files", "*xlsx, *xls"
           varFile = .selecteditems(1)
              Set wb_src = xl.Workbooks.Open(cstr(varfile))
              Set ws_src = wb_src.Worksheets("source sheet name here")
              Set wb_trgt = xl.Workbooks.Open("path of target book here")
              Set ws_trgt = wb_trgt.Worksheets("path of target sheet here")
    end if
end with

now write code to do your copying, which you can easily get from running the macro recorder and learning from it.