Data analysis; Inside ACCESS or not?

A

aner1755

Registered User.
Local time
Today, 22:28
Joined
Jan 23, 2009
Messages
43
Hi All,
what do you think about data analysis inside ACCESS? Is it preferred to export data to EXCEL and do the work there or would you work inside ACCESS?

I’m working on a database that a user should control solely from ACCESS, hence no exports to EXCEL where he/she needs to do hands on. What I have in mind is to compare slopes for two graphs, i.e. take the derivative of the two. More analysis is probably added later on…

I’m not particular familiar with EXCEL but I guess this kind of operation is a simple task there. But on the other hand I think that it is quit hard to export data from ACCESS to a predefined EXCEL sheet (with functions etc.).
An analysis performed within ACCESS would need some own-defined functions, I’m I right?

So, which approach would you take?

Cheers!
 
I like to keep everything between Access and Word. But I have a couple of instances of using Access to Excel.

It is easy to have Access open Excel and insert Access data to specified cells in Excel. I do this a little bit and the Excel file has the formulas and uses the data coming from Access. I do this because I have a fair bit writing that is general descriptions and some of it is formatted for bold and coloured type etc and too much stuffing around in Access. It is also easier to email the Excel file.

I don't know about doing graphs but I guess you could have Access insert the data into the Excel cells.
 
It sounds good that it is an easy task to insert ACCESS data into Excel cells.
I know how to export a whole table to an arbitry place into an Excel sheet, but is it possible, in an easy way, to export several arbitary field or record from a table to arbitay location in an EXCEL sheet at once? Would you be kind to show some code of how to do that?
That would realy save my day... :-D
 
It is easy to get Access to write data to an Excel spreadsheet and to draw graphs there. The VBA code is very similar in both Access and Excel.

I am posting some code which is used to send out a report with graph to an Excel spreadsheet. This is done because the recipients don't have Acces but they do have Excel.

Code: 
Sub CreateCSMChart(CSM As String, Startingdate As Date)
' This subroutine formats a chart for a specific CSM
Dim recData As DAO.Recordset, rst As DAO.Recordset
Dim dbs As Database
Dim varArray As Variant, CSA As String
Dim objExcel As Object, objBook As Object
Dim objSheet As Object, objChart As Object
Dim intFields As Integer, intRows As Integer
Dim intFld As Integer, intRow As Integer, StartDate As Date, EndDate As Date
Dim strRange As String, strCriteria As String, strSaveFile As String
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objBook = objExcel.Workbooks
objBook.Add
Set objSheet = objExcel.Sheets("Sheet1")
Set objChart = objExcel.Charts
Set dbs = CurrentDb
StartDate = Startingdate - 35
EndDate = Startingdate
DoCmd.Hourglass True ' Set cursor to Hourglass while running
strCriteria = "Select * from qryCSMChartSource where CSM = '" & CSM & "' and WeekCom between #" & Format$(StartDate, "mm/dd/yyyy") & "# and #" & Format$(EndDate, "mm/dd/yyyy") & "#"
Set recData = dbs.OpenRecordset(strCriteria)
' Span all of dataset to ensure recordcount is correct
recData.MoveLast
recData.MoveFirst
' Extract data from Query into varArray
varArray = recData.GetRows(recData.RecordCount)
intFields = UBound(varArray, 1)
intRows = UBound(varArray, 2)
'objSheet.Add   'Creates a new Excel workbook
'objExcel.Visible = True ' Hides the workbook from the desktop
 
' Format the worksheet
 
objExcel.Sheets("Sheet1").PageSetup.Orientation = xlLandscape
' Copy the column headings from the data and format
For intFld = 1 To intFields
   objExcel.Cells(intRow + 1, intFld + 1).Value = recData.Fields(intFld).Name
   objExcel.Cells(intRow + 1, intFld + 1).Font.Bold = True
   objExcel.Cells(intRow + 1, intFld + 1).Font.Size = 12
Next
' Insert Heading for Column A
objExcel.Cells(1, 1).Value = "Week Commencing"
objExcel.Cells(1, 1).Font.Bold = True
objExcel.Cells(1, 1).Font.Size = 12
recData.Close ' Close the Dataset
For intFld = 0 To intFields
   For intRow = 0 To intRows
   'Populate the spread sheet Column by Column - Row by Row
      objExcel.Cells(intRow + 2, intFld + 1).Value = varArray(intFld, intRow)
      objExcel.Cells(intRow + 2, intFld + 1).HorizontalAlignment = xlCenter
   Next
Next
CSM = objExcel.Cells(2, 4).Value
' Hide the two default sheets that are not used.
objExcel.Sheets("Sheet2").Visible = False
objExcel.Sheets("Sheet3").Visible = False
 
' Select the data range
strRange = "A2:" & Chr$(Asc("A") + intFields - 1) & Format$(intRows + 2)
'strRange = "a2:A4"
objExcel.Range(strRange).Select
 
objExcel.Range(Mid(strRange, 4)).Activate
objExcel.Application.Charts.Add
 
Set objChart = objExcel.ActiveChart
With objChart
    
    .HasTitle = True
    .ChartTitle.Caption = "Percentage Usage for " & CSM & "'s Team"
    .ChartType = xlLineMarkers 'Set chart type
    .Axes(xlCategory).HasTitle = True
    .Axes(xlCategory).AxisTitle.Caption = "Week Commencing"
    On Error Resume Next
    .Axes(xlCategory).MajorUnit = 7 'Just show weekly dates
    On Error GoTo ErrLab            ' Set error trap
    
    ' Format the Chart
    
   .Axes(xlValue).HasTitle = True
   .Axes(xlValue).AxisTitle.Caption = "Usage %"
    .HasLegend = True
    .SeriesCollection(1).Name = CSM & "'s Team"
    .Legend.LegendEntries(1).LegendKey.Border.ColorIndex = 1
    .Legend.LegendEntries(1).LegendKey.MarkerBackgroundColorIndex = 1
    .Legend.LegendEntries(1).LegendKey.MarkerForegroundColorIndex = 1
    .SeriesCollection(2).Name = "=sheet1!R1C3"
    .Legend.LegendEntries(2).LegendKey.Border.ColorIndex = 5
    .Legend.LegendEntries(2).LegendKey.MarkerBackgroundColorIndex = 5
    .Legend.LegendEntries(2).LegendKey.MarkerForegroundColorIndex = 5
   End With
 ' Format data table
 objExcel.Sheets("Sheet1").Activate
 objExcel.Sheets("Sheet1").Columns("A:E").EntireColumn.AutoFit
 objExcel.Sheets("Sheet1").Columns("B").EntireColumn.NumberFormat = "0.00"
 objExcel.Sheets("Sheet1").Columns("C").EntireColumn.NumberFormat = "0.00"
 objExcel.Sheets("Sheet1").Columns("D").EntireColumn.NumberFormat = "0.00"
 objExcel.Sheets("Sheet1").Select
 objExcel.Sheets.Move Before:=objExcel.Sheets(1)
 objExcel.Sheets("Sheet1").Name = "Data Table"  ' rename sheet
 objExcel.Sheets("Chart1").Activate
 objExcel.Sheets("Chart1").Move Before:=objExcel.Sheets(1)
 objExcel.Sheets("Chart1").Name = CSM & "'s Team"
 strSaveFile = "H:\Customer Relationship\Knowledge Management (Public)\Knowledge Base Reports\Weekly Report Archive " & Format(Startingdate, "yyyy") _
                   & "\" & Format(Startingdate, "yyyymmdd") & "\" & CSM & ".xls"
 objExcel.ActiveWorkbook.SaveAs FileName:=strSaveFile
 objExcel.ActiveWorkbook.Close
 objExcel.Quit
 Set objChart = Nothing
 Set objExcel = Nothing
 DoCmd.Hourglass False ' Switch off Hourglass cursor
 Exit Sub
 
Sub CreateSDMChart(SDM As String, Startingdate As Date)
' This subroutine formats a chart for a specific SDM
 
Dim recData As DAO.Recordset, rst As DAO.Recordset
Dim dbs As Database
Dim varArray As Variant, CSA As String, BusinessUnit As String
Dim objExcel As Object, objBook As Object
Dim objSheet As Object, objChart As Object
Dim intFields As Integer, intRows As Integer
Dim intFld As Integer, intRow As Integer, StartDate As Date, EndDate As Date
Dim strRange As String, strCriteria As String, strSaveFile As String
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objBook = objExcel.Workbooks
objBook.Add
Set objSheet = objExcel.Sheets("Sheet1")
Set objChart = objExcel.Charts
Set dbs = CurrentDb
StartDate = Startingdate - 35
EndDate = Startingdate
DoCmd.Hourglass True ' Set cursor to Hourglass while running
strCriteria = "Select * from qrySDMChartSource where SDM = '" & SDM & "' and WeekCom between #" & Format$(StartDate, "mm/dd/yyyy") & "# and #" & Format$(EndDate, "mm/dd/yyyy") & "#"
' MsgBox (strCriteria)
Set recData = dbs.OpenRecordset(strCriteria)
' Span all of dataset to ensure recordcount is correct
recData.MoveLast
recData.MoveFirst
BusinessUnit = recData!BusinessUnit
' Extract data from Query into varArray
varArray = recData.GetRows(recData.RecordCount)
intFields = UBound(varArray, 1)
intRows = UBound(varArray, 2)
objExcel.Workbooks.Add   'Creates a new Excel workbook
objExcel.Visible = False ' Hides the workbook from the desktop
 
' Format the worksheet
 
objExcel.Sheets("Sheet1").PageSetup.Orientation = xlLandscape
' Copy the column headings from the data and format
For intFld = 1 To intFields
   objExcel.Cells(intRow + 1, intFld + 1).Value = recData.Fields(intFld).Name
   objExcel.Cells(intRow + 1, intFld + 1).Font.Bold = True
   objExcel.Cells(intRow + 1, intFld + 1).Font.Size = 12
Next
' Insert Heading for Column A
objExcel.Cells(1, 1).Value = "Week Commencing"
objExcel.Cells(1, 1).Font.Bold = True
objExcel.Cells(1, 1).Font.Size = 12
recData.Close ' Close the Dataset
For intFld = 0 To intFields
   For intRow = 0 To intRows
   'Populate the spread sheet Column by Column - Row by Row
      objExcel.Cells(intRow + 2, intFld + 1).Value = varArray(intFld, intRow)
      objExcel.Cells(intRow + 2, intFld + 1).HorizontalAlignment = xlCenter
   Next
Next
SDM = objExcel.Cells(2, 4).Value
' Hide the two default sheets that are not used.
objExcel.Sheets("Sheet2").Visible = False
objExcel.Sheets("Sheet3").Visible = False
 
' Select the data range
strRange = "A2:" & Chr$(Asc("A") + intFields - 2) & Format$(intRows + 2)
objExcel.Range(strRange).Select
' MsgBox (Mid(strRange, 4))
objExcel.Range(Mid(strRange, 4)).Activate
objExcel.Application.Charts.Add
 
Set objChart = objExcel.ActiveChart
With objChart
    
    .HasTitle = True
    .ChartTitle.Caption = "Percentage Usage for " & SDM & "'s Team"
    .ChartType = xlLineMarkers 'Set chart type
    .Axes(xlCategory).HasTitle = True
    .Axes(xlCategory).AxisTitle.Caption = "Week Commencing"
    On Error Resume Next
    .Axes(xlCategory).MajorUnit = 7 'Just show weekly dates
    On Error GoTo ErrLab            ' Set error trap
    
    ' Format the Chart
    
    .Axes(xlValue).HasTitle = True
    .Axes(xlValue).AxisTitle.Caption = "Usage %"
    .HasLegend = True
    .SeriesCollection(1).Name = SDM & "'s Team"
    .Legend.LegendEntries(1).LegendKey.Border.ColorIndex = 1
    .Legend.LegendEntries(1).LegendKey.MarkerBackgroundColorIndex = 1
    .Legend.LegendEntries(1).LegendKey.MarkerForegroundColorIndex = 1
    .SeriesCollection(2).Name = "=Sheet1!R1C3"
    .Legend.LegendEntries(2).LegendKey.Border.ColorIndex = 5
    .Legend.LegendEntries(2).LegendKey.MarkerBackgroundColorIndex = 5
    .Legend.LegendEntries(2).LegendKey.MarkerForegroundColorIndex = 5
   End With
   
 ' Format data table
 objExcel.Sheets("Sheet1").Activate
 objExcel.Sheets("Sheet1").Columns("A:E").EntireColumn.AutoFit
 objExcel.Sheets("Sheet1").Columns("B").EntireColumn.NumberFormat = "0.00"
 objExcel.Sheets("Sheet1").Columns("C").EntireColumn.NumberFormat = "0.00"
 objExcel.Sheets("Sheet1").Columns("D").EntireColumn.NumberFormat = "0.00"
 objExcel.Sheets("Sheet1").Select
 objExcel.Sheets.Move Before:=Sheets(1)
 objExcel.Sheets("Sheet1").Name = "Data Table"  ' rename sheet
 objExcel.Sheets("Chart1").Activate
 objExcel.Sheets("Chart1").Move Before:=Sheets(1)
 objExcel.Sheets("Chart1").Name = SDM & "'s Team"
 strSaveFile = "H:\Customer Relationship\Knowledge Management (Public)\Knowledge Base Reports\Weekly Report Archive " & Format(Startingdate, "yyyy") _
                   & "\" & Format(Startingdate, "yyyymmdd") & "\" & SDM & ".xls"
  objExcel.ActiveWorkbook.SaveAs FileName:=strSaveFile
 objExcel.ActiveWorkbook.Close
 Set objChart = Nothing
 Set objExcel = Nothing
 DoCmd.Hourglass False ' Switch off Hourglass cursor
 Exit Sub
 
ErrLab:
 ' Ignore errors
 Resume Next
 
End Sub ' CreateSDMChart
 
Here is an example. You can see further down the reference to Access text boxes. The top one has been different and if it was a number in an unbound text box then the texbox would need to be format for general number. Note also the Excel is referenced different to the normal G3 etc. The following does not require reference to MS Excel Library, well at least ir shouldn't:)

The following has Exel show and stay open. You can also have so it inserts the data and then closes the spreadsheet. You can work that part out yourself:D

Dim ObjXLApp As Object
Dim ObjXLBook As Object
Dim OSheet As Object
Set ObjXLApp = CreateObject("Excel.Application")
Set ObjXLBook = ObjXLApp.Workbooks.Open("C:\Letters\Exp.xls")
Set OSheet = ObjXLBook.Worksheets(1)
ObjXLApp.Visible = True
Set ObjXLBook = ObjXLApp.Workbooks.Open("C:\Letters\Exp.xls")
Set OSheet = ObjXLBook.Worksheets(1)
ObjXLApp.Visible = True
OSheet.Cells(2, 9).Value = Forms!GoToExcel!Text1
OSheet.Cells(5, 9).Value = Me.Text3.Value
OSheet.Cells(3, 3).Value = Me.Text5.Value
ObjXLBook.Save
 
Here is a link to a MS acticle on transferring data to Excel from Access.
Link

David
 
FANTASTIC!!!
You are the BEST, Rabbie, Mike375 and DCrake!
I'll dig in to this and see what I can get out of it. Think I got myself a lilte bit of work for the weekend... ;-)

Maybe I'll be back with some further questions later on...

Thanks again! :-D
 
Rabbie said:
It is easy to get Access to write data to an Excel spreadsheet and to draw graphs there. The VBA code is very similar in both Access and Excel...
Click to expand...
Thanks for posting this - I'm working on an excel automation project at the moment and there were some useful bits in that code (I couldn't work out how to do AutoFit).

I'm still stuck on a few things though - how do I set the borders of a selection of cells - it looks as though it ought to be:
Code: 
With objActiveWkb.Worksheets(1)
        'set up styles
        .Range("A15:P15").Select
        .Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
End With
- but that just generates an error: Object doesn't support this property or method.

I've tried recording a macro in Excel, then cribbing the code, but it's hard to know how to translate it - for example, an alternative to explicitly formatting the cells in the row I'm creating would be to invoke the format painter to copy them down from the previous row - the Excel macro VBA for this is:
Code: 
    Rows("14:14").Select
    Selection.Copy
    Range("A15").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
But I can't work out how to convert that to Access VBA - particularly the fourth line where there are additional parameters.

Any suggestions?
 
When formatting cells in Excel from Access VBA try using the AppActive Statement. This tells MS which exe is in control.

David
 
If I don't know how to do something, I generally open Excel (or Word) and turn on the macro recorder to generate the code. This isn't 100% but it usually gets me close. You will have to always modify the code so that the objects are correctly referenced since the reference within Excel or Word is slightly different from without.
 
How are you doing the Excel code? Did you set a reference to Excel or are you doing Late Binding using something like:

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")

If doing Late Binding then you have to supply the actual numbers for the Excel Constants in the PasteSpecial code (or you need to create those constants with the correct values assigned).
 
I’m working out a good way to export my data to Excel and tested some of the techniques in DCrake’s post. When it comes to the one described under Use automation to transfer an ADO recordset to a worksheet range I encounter an error; Run-time error 3706. Couldn’t find the specified provider. Maybe it isn’t properly installed.
Do I need to set a Reference? I tried to add some, at random…, but I couldn’t get it to work.
Do you know what I need to do?
 
You must log in or register to reply here.

Similar threads

H
Beginner Seeking Help: Building an Access Database for Volunteer Fire Department
Replies
9
Views
1,176
arnelgp
arnelgp
N
Solved Export data from Passthrough Query to CSV
2
Replies
20
Views
1,208
Gasman
Gasman
Isaac
PowerBI - referencing an empty string, or a Null inside of a DAX formula
Replies
5
Views
712
Isaac
Isaac
T
Solved Foxpro IDE
Replies
17
Views
1,396
arnelgp
arnelgp
R
Importing a spreadsheet to existing table where column names don't match
Replies
15
Views
912
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom