• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Macro for copy part of excel sheet rows and columns and paste in other file

preethi3290

New Member
Hi,
My Task is to import part of data to Access from excel sheets which are in multiple folders.

I have data in excel sheets which are in multiple folders.
I want to copy part of excel sheet data from multiple folders and save in a mastersheet. Now I have to Import this mastersheet data into MS Access.
Please give me the code.
 
Hi:

Why can't you link all the excel files to access database and run queries to extract the data you want, rather than doing it outside access. Access is basically designed to do that.

Thanks
 
Hi:

Why can't you link all the excel files to access database and run queries to extract the data you want, rather than doing it outside access. Access is basically designed to do that.

Thanks
Hi Nebu,
Thanks for the Reply.
Can you help me how to link all files and I have to import Part of excel sheet which are in multiple folders
 
Hi:

I guess with this excel format the linking is not straight forward. You have to manipulate your excel file before linking it with access.

Thanks
 
Hi:

I guess with this excel format the linking is not straight forward. You have to manipulate your excel file before linking it with access.

Thanks
That's why I want to extract necessary fields from all the sheets which are in multiple folders and save in a new master sheet and then import to access
 
I have tried this macro to extract the data and save in a new file. But every time it is showing below message
upload_2017-7-19_17-44-48.png

Macro I used

Sub copy()

Dim i As Integer, erow As Integer

'LastRow = ActiveSheet.Range(“A" & Rows.Count).End(xlUp).Row

For i = 16 To 36

Range(Cells(i, 1), Cells(i, 17)).Select
Selection.copy

Workbooks.Open Filename:="C:\Users\preet\Desktop\boss data\Customer Invoice Template\ANAHEIM HILLS-DA\mastersheet.xlsx"
Worksheets("master").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False

Next i
End Sub
 
Hi:

Before your DIM statement.

Thanks
Hi,
when I am processing Macro in second excel file it is cpoying the data into main sheet but it is changing the first sheet values(See mastersheet). If I am processing macro for the same sheet twice then the values are not changing(See sheet2). Please help me on this.
 

Attachments

  • master10.xlsx
    18.7 KB · Views: 3
Hi,
when I am processing Macro in second excel file it is cpoying the data into main sheet but it is changing the first sheet values(See mastersheet). If I am processing macro for the same sheet twice then the values are not changing(See sheet2). Please help me on this.
The values are keepon changing.
 
Hi, preethi3290!
Sorry if arriving late to this conversation makes me post yet answered questions, but I have a few doubts.
You have the Excel workbook that you uploaded at post 4. In that workbook you have a little hole in range J32:L36. Despite of this which is the exact range that you want to export to Access? And why are you trying to achieve this from Excel and not from Access? Could you also post the Access file? Thank you.
Regards!
 
Hi, preethi3290!
Sorry if arriving late to this conversation makes me post yet answered questions, but I have a few doubts.
You have the Excel workbook that you uploaded at post 4. In that workbook you have a little hole in range J32:L36. Despite of this which is the exact range that you want to export to Access? And why are you trying to achieve this from Excel and not from Access? Could you also post the Access file? Thank you.
Regards!
Hi SirJB7,
I want to import part of excel sheet to Access which are in multiple folders. and In my excel sheet the first row is not table. The table starts at A15. So I want to import data from A15.
As this not possible directly I thought to Copy the desired dataa from each excel file which are in multiple folders and paste in a master sheet. So that I can direct;y import the master sheet to Access.

Please see the sample excel sheet in attachments
 

Attachments

  • excel data1.xlsx
    112.6 KB · Views: 0
Hi, preethi3290!

Please tell me:
a) path of the Access file
b) paths of the Excel files
c) exact range start (row, col) thru end (row, col / first blank) of Excel files
d) Access table structure where ranges will be imported.

Preferrabily, upload Access file too.

Regards!
 
a) Path of Access File(Documents)
b) Path of Excel file(Desktop)
c) I want to extract Data from cells(A15, L15) to (A36,L36) condition: if Date column exists then only it has to extract that particular row
d)Aceess table structure is same as excel sheet structure.

Please tell me if you need any more details from my side.
It's very urgent task for me.
Please try to help
 

Attachments

  • excel data1.xlsx
    110.4 KB · Views: 0
Hi, preethi3290!

Please tell me:
a) path of the Access file
b) paths of the Excel files
c) exact range start (row, col) thru end (row, col / first blank) of Excel files
d) Access table structure where ranges will be imported.

Preferrabily, upload Access file too.

Regards!
Hi,
Do you understand my Issue?
 
Back
Top