#  Other Applications & Softwares  > Access Tables & Databases >  >  Excel VBA Macros to Push / Export Data from excel sheet1 range("A1:Z50") TO Access (.accdb

## milu

Hi Team,

I receive reports on daily basis in excel file. I want to push data from excel sheet to access .accdb
Is it possible to Excel VBA Macros to Push / Export Data from excel sheet1 range("A1:Z50") TO Access (.accdb)
It is very importent for me. Can you please help me about this.


Thanks and Regards,
Milind Kale.

----------


## romperstomper

You can use code like this:




```
Please Login or Register  to view this content.
```

----------


## milu

Hi Sir,

Thank You Very much for your help but I am getting error because i dont know wheare i have to make changes in below line.

.Execute "INSERT INTO tblTest (C1,C2,C3) SELECT * FROM [Excel 12.0;HDR=YES;Database=" & strWorkbook & "].[Sheet1$A1:Z50]"  :Confused: 

I have made below changes in given macro. Can you please help to resolve this issue.

Sub AddDataFromWorkbookToAccess()

' Sample demonstrating how to update data in an Access database from a workbook
   Dim cn As Object
   Dim strWorkbook As String
   Dim strDatabase As String

   ' change these paths
   strDatabase = ThisWorkbook.Path & "\Order.accdb" ' In Access file my table name is "Table1"
   strWorkbook = ThisWorkbook.FullName

   Set cn = CreateObject("ADODB.Connection")

   With cn
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & strDatabase
      .Open
      ' change table and field names here (Issue with below line.)
      .Execute "Table1 (C1,C2,C3) SELECT * FROM [Excel 12.0;HDR=YES;Database=" & strWorkbook & "].[Sheet1$A1:Z50]"
      .Close
   End With

End Sub

----------


## romperstomper

Please use code tags.

You can't put *26* columns from Excel into *3* columns in your table.

----------


## Muzammil Siddique

I have used the following code which works fine for me but I have a slight problem with this the code you taught in the video is below which I have modified according to my requirements by changing the path and file name:

Sub copyDataReqtoDayCount()
Dim FolderPath As String, Filepath As String, Filename As String
FolderPath = "F:\London\IT\IT Administration\Requisitions Orders Deliveries Quotes\Requisitions\"
Filepath = FolderPath & "Reqs-List-14.xlsx*"
Filename = Dir(Filepath)
Dim lastrow As Long, lastcolumn As Long
Do While Filename <> ""
Workbooks.Open (FolderPath & Filename)
lastrow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
erow = Sheet1.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
lastcolumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, lastcolumn))
Filename = Dir
Loop
Application.DisplayAlerts = True
End Sub 

                The code above exports all the data from file Reqs-List-14.xlsx to the master file, I need to achieve the following :

Export all the data from Reqs-List-14.xlsx (Column A,B,K,O,P,L,M,N & U) 
                                     to 
                                            Master File.xlsx   (Column A,C,D,E,F,G,H,J & L)

Can you please help me with the code  I will be very very thank full to you.

----------


## bigroo1958

Romperstomper,

Thanks for the simple code, works beautifully!!

----------


## alansidman

@Muzammil Siddique 

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------

