VB.NET coding with access and excel I need help finishing this coding, this is t

Question

VB.NET coding with access and excel

I need help finishing this coding, this is the assignmet:

For this project in addition to you form, you will need to make 2 separate classes. one for manipulating your Excel file and the other for reading your access file. This project combines the ability to read from an Access file and the ability to write to an Excel file. The point here is to transfer a table to your datagridview box and display the database. You need to open an excel file or make a new one and then populate it given a file directory and a Boolean stating if a file exist or not. I will leave that last part for you to figure out. hint: The dialog box gives it to you

this is my code so far

CLASS1: Reading Access table

Imports System.Data.OleDb
Public Class access

Dim MyConnection As OleDbConnection

Dim MyDataAdapter As OleDbDataAdapter

Dim myDataSet As DataSet

Dim Mytables As DataTableCollection

Dim mySource As New BindingSource

Function GetDatabase(Address As String, SetName As String) As DataView

MyConnection = New OleDbConnection

MyConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Address

myDataSet = New DataSet

Mytables = myDataSet.Tables

Dim Comand As String = "Select * from [" & SetName & "]"

MyDataAdapter = New OleDbDataAdapter(Comand, MyConnection)

MyDataAdapter.Fill(myDataSet, SetName)

Dim ViewofDatabase As New DataView(Mytables(0))

Return ViewofDatabase

End Function
End Class

CLASS2: Opening and saving an excel file

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Excel
Dim myApplication As Excel.Application

Dim myWorkBook As Excel.Workbook

Dim myWorkSheet As Excel.Worksheet

Sub openSaveAndPopulate(MyFileName As String, exists As Boolean, DGVmyView As DataGridView)

CheckifReal(MyFileName, exists)

Poplulate(DGVmyView)

SaveExcelFile(MyFileName, exists)

End Sub

Private Sub CheckifReal(MyFileName As String, exists As Boolean)

On Error Resume Next

'create Excel object

myApplication = CreateObject("Excel.Application")

'if file exists, place file name in FileCheck

If exists Then

'Workbook exists, open it

myWorkBook = myApplication.Workbooks.Open(MyFileName)

myWorkSheet = myWorkBook.Worksheets(1)

Else

'Workbook doesn't exist, create new workbook

myWorkBook = myApplication.Workbooks.Add

myWorkSheet = myWorkBook.Worksheets(1)

End If

End Sub

Private Sub Poplulate(DGVmyView As DataGridView)

'do this on your own

End Sub

Private Sub SaveExcelFile(MyFileName As String, exists As Boolean)

If exists Then

'Save existing workbook

myWorkBook.Save()

Else

'Save new workbook

myWorkBook.SaveAs(MyFileName)

End If

'Close Excel

myWorkBook.Close(SaveChanges:=False)

myApplication.Quit()

myApplication = Nothing

myWorkBook = Nothing

myWorkSheet = Nothing

End Sub
End Class

it's suppose to look like this:

Explanation / Answer

Approach would look something like this. Before we begin, you'll need to add a reference (Tools -> References) to Microsoft ActiveX Data Objects 6.1

Program :

Public Sub Test()
    Dim strSQL_Query As String
    Dim oCN As ADODB.Connection
    Dim oCMD As ADODB.Command
    Dim oRecords As ADODB.Recordset
    Dim strCN As String
    Dim strDBPath As String
    Dim varValues As Variant
    Dim wksTarget As Excel.Worksheet
    Dim lngRows As Long
    Dim lngCols As Long

    '' Replace with the path to your DB. You could also use a dialog box to let the user choose a DB, if
    '' it moves around or isn't found.
    strDBPath = "C:myFoldermyAccessFile.accdb"
    strCN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBPath & ";" & _
            "Persist Security Info=False;"

    '' Replace this with a query that does what you need it to
    strSQL_Query = "SELECT TOP 10 FROM [MyTable] WHERE <Conditions> ORDER BY [Column] DESC"

    Set oCN = New ADODB.Connection
    oCN.ConnectionString = strCN
    oCN.Open

    Set oCMD = New ADODB.Command
    oCMD.ActiveConnection = oCN
    oCMD.CommandText = strSQL_Query
    Set oRecords = oCMD.Execute

    If oRecords.BOF And Not oRecords.EOF Then
        varValues = oRecords.GetRows
        Set wksTarget = ThisWorkbook.Worksheets(1)

        '' You might need 0 and 1 instead of 1 and 2 - I forget
        lngCols = UBound(varValues, 1)
        lngRows = UBound(varValues, 2)
        wksTarget.Range("A1", wksTarget.Range("A1").Offset(lngRows, lngCols)) = varValues
    End If

    Set oRecords = Nothing
    Set oCMD = Nothing
    oCN.Close
    Set oCN = Nothing

    Dim strDir as String
    strDir = "C:SomePathHere"
    If Dir(strDir, vbDirectory) = "" Then MkDir(strDir)
    ThisWorkbook.SaveAs strDir & "YourWorkbookName.xlsm", 52 '' 52 is a constant indicating a macro enabled format
End Sub

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.