Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to insert the result of excel code to access

Posted on 2016-09-28
6
Medium Priority
?
78 Views
Last Modified: 2016-11-03
I need to insert the results from the below code  in Access file because of the excel sheet limitation.

Regards,

Dallag

Sub ReArrangeDataVersion4()
Dim sws As Worksheet, dws As Worksheet
Dim lr As Long, lc As Long, i As Long, dlr As Long
Dim x, y
Dim TimeTaken As Date
TimeTaken = Now
Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
Set dws = Sheets("Output")

lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
lc = sws.Cells(1, Columns.Count).End(xlToLeft).Column

dlr = dws.Cells(Rows.Count, 2).End(xlUp).Row
If dlr > 1 Then dws.Range("A2:G" & dlr).Clear
y = sws.Range("A4:A" & lr).Value

For i = 2 To lc Step 8
   DoEvents
   dlr = dws.Range("B" & Rows.Count).End(3)(2).Row
   dws.Range("B" & dlr).Offset(0, -1) = sws.Cells(1, i)
   dws.Range("B" & dlr).Resize(UBound(y, 1)).Value = y
   x = sws.Range(sws.Cells(4, i), sws.Cells(lr, i + 7)).Value
   dws.Range("C" & dlr).Resize(UBound(y, 1), 8).Value = x
Next i
dlr = dws.Cells(Rows.Count, 2).End(xlUp).Row
dws.Range("A2:A" & dlr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
dws.Range("A2:A" & dlr).Value = dws.Range("A2:A" & dlr).Value
dws.Columns.AutoFit
dws.Range("A1").CurrentRegion.Borders.Color = vbBlack
dws.Activate
Application.ScreenUpdating = True
MsgBox "Time taken to process data was " & Format(Now - TimeTaken, "hh:mm:ss")
End Sub

Open in new window

0
Comment
Question by:Mohammed Dallag
5 Comments
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 41819869
Hi,

what do you expect should be the answer for this question?

Nobody knows what your Excel sheet contains. Because of that nobody knows what data is manipulated in which way. And nobody knows what's in your Access file and how the data from the Excel file has a logical relationship to it or what should be done in the Access file.

If you want to get an answer it's on your turn to give all needed information.

Cheers,

Christian
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41820300
you need to
1. create an ODBC connection to the database
2. declare and open an ADO connection (using the ODBC connection) to the Access database, (outside the loop)
3. then run INSERT statements in your loop
4. then close the connection
0
 

Author Comment

by:Mohammed Dallag
ID: 41820358
I attached the excel sheet and the access file.

Regards,

Dallag
ReArrangeData_V41.xlsm
1.accdb
0
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 41832279
This code will do it:

Public Sub ImportXLData()
'Created by Helen Feddema 6-Oct-2016
'Last modified by Helen Feddema 6-Oct-2016

On Error GoTo ErrorHandler

   Dim appExcel As New Excel.Application
   Dim strWorkbook As String
   Dim strTable As String
   Dim wkb As Excel.Workbook
   Dim strSQL As String
   Dim strSpec As String
   
   strWorkbook = Application.CurrentProject.Path & "\ReArrangeData_V41.xlsm"
   Debug.Print "XL file: " & strWorkbook
   Set wkb = appExcel.Workbooks.Open(strWorkbook)
   strTable = "txlsOutput"
   strSQL = "DELETE * FROM " & strTable
   CurrentProject.Connection.Execute strSQL
   
   strSpec = "Import-ReArrangeData_V41"
   Application.CurrentProject.ImportExportSpecifications(strSpec).Execute

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in ImportXLData procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window


I first made the export spec manually.  

You will need to delete the first row, with the imported headings -- there is an error because one of the Excel column headings is not a valid Access field name, so I redid the names.  I am attaching the database with a module containing this code.
HBF-Modified-1.accdb
0
 

Author Closing Comment

by:Mohammed Dallag
ID: 41871836
Thank you
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question