如何在excel vba中传递一个包含excel文件路径的字符串来访问vba

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.

我想传递一个字符串aPath包含活动工作簿的路径来访问vba模块或子。下面是我的excel vba代码,它将打开访问db表单。如何传递字符串值以访问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

我的Access VBA代码将内容写回excel。我希望在conWKB_NAME中拥有aPath值

   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

2 个解决方案

#1

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

考虑Application.Run方法(有关详细信息,请参阅Access帮助主题)。

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.

我认为这就是你的问题所要求的。然而,这个操作链(从Excel,打开Access,再从Access再次打开Excel)对我来说似乎很复杂。我认为将Access数据拉入第一个Excel实例中工作簿中的目标工作表应该更简单。

#2

Try this out. Dim aPath as String. Currently only exePath is declared as a String. Not sure it will work but worth a try. what error are you getting?

试试吧。将aPath作为字符串调暗。目前只有exePath被声明为String。不确定它会起作用,但值得一试。你遇到了什么错误?

#1