How to pass a string contains excel file path in excel vba to access vba

Question

I want to pass a string aPath contains path of the active workbook to access vba module or sub. Below is my excel vba code that will open access db form. How do i pass the string value to access vba.

    Dim aPath, aDbase, aDSource, aTable, exePath As String
    Dim fileParam As String

    aPath = ActiveWorkbook.Path
    aDbase = "near_14.accdb"
    aDSource = aPath & "\" & aDbase

    Set appAccess = CreateObject("Access.Application")
    appAccess.Visible = True
    appAccess.OpenCurrentDatabase aDSource
    appAccess.DoCmd.OpenForm "Import_From_P"

    appAccess.CloseCurrentDatabase

My Access VBA code write contents back to excel. in which i want to have the aPath value in conWKB_NAME

   Public Sub sCopyResultstoexcel(conSHT_NAME As Variant, conWKB_NAME As Variant,  qrytable As String)
     'Copy records to first 20000 rows
     'in an existing Excel Workbook and worksheet

     Dim objXL As Excel.Application
     Dim objWkb As Excel.Workbook
     Dim objSht As Excel.Worksheet
     Dim db As Database
     Dim rs As Recordset
     Dim rs_Attribute As Recordset
     Dim intLastCol As Integer
     Const conMAX_ROWS = 20000
    Set db = CurrentDb
    Set objXL = New Excel.Application
    Set rs = db.OpenRecordset(qrytable, dbOpenSnapshot)

    With objXL
     .Visible = False
      Set objWkb = .Workbooks.Open(conWKB_NAME)
 On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
  Set objSht = objWkb.Worksheets.Add
  objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht

    .Range(.Cells(2, 1), .Cells(conMAX_ROWS, _
        intLastCol)).CopyFromRecordset rs
        .Range(.Cells(1, 1), _
        .Cells(1, rs.Fields.Count)).Font.Bold = True
       '.Cells.Range(1, rs.Fields.Count).WrapText = True
       .Range(.Cells(1, 1), _
       .Cells(1, rs.Fields.Count)).WrapText = False
       'Formatting
With objSht.Range("A1:AP1")
.HorizontalAlignment = xlCenter
.ColumnWidth = "8"
.Font.Italic = False
.Font.Bold = True
.EntireColumn.ColumnWidth = 15
End With
      'Adding fields
    With rs
 For i = 1 To .Fields.Count
     objSht.Cells(1, i) = .Fields(i - 1).Name
 Next i
 objWkb.Save
End With
End With
 End With
 objWkb.Close
 objXL.Quit
        Set objSht = Nothing
        Set objWkb = Nothing
        Set objXL = Nothing
  Set rs = Nothing
  Set db = Nothing
  End Sub

Sorry are you asking the question?.Yes,how do i make access uses the string value. — vuyy1182
I meant what will Access do with aPath after it gets that string. — HansUp

HansUp HansUp · Accepted Answer · 2014-03-20T18:38:10

Consider the Application.Run Method (see the Access help topic for details).

Try it like this ...

appAccess.OpenCurrentDatabase aDSource
appAccess.Run "sCopyResultstoexcel", "YourSheetName", aPath, _
    "your qrytable string"

I think that's what your question asks for. However that chain of operations (from Excel, open Access, and then from Access open Excel again) seems convoluted to me. I think it should be simpler to just pull the Access data into the target sheet in the workbook in the first Excel instance.

How to pass a string contains excel file path in excel vba to access vba

2 Answers