访问 - Excel集成 - 源码之家

问题描述：

嘿所有，一直在努力设计一个新的工作数据库。他们一直在使用Excel进行日常报告，并将所有数据存储在那里，所以我决定在Access中使用数据库的后端，在Excel中使用前端，因此任何分析工作都可以轻松完成数据已导入到Excel中。访问 - Excel集成

现在，我是相当新的VBA，慢慢地习惯使用它，写了一些代码来传输计算的一个表从Access到Excel：

Option Explicit 

Public Const DataLocation As String = "C:\Documents and Settings\Alice\Desktop\Database\TestDatabase21.accdb" 

Sub Market_Update() 
    Call ImportFromAccessTable(DataLocation, "Final_Table", Worksheets(2).Range("A5")) 
End Sub 

Sub ImportFromAccessTable(DBFullName As String, TableName As String, TargetRange As Range) 

    Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer 

    Set TargetRange = TargetRange.Cells(1, 1) 
    ' open the database 
    Set cn = New ADODB.Connection 
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBFullName & ";" 
    Set rs = New ADODB.Recordset 
    With rs 
     ' open the recordset 
     ' .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable 

     ' all records 
     .Open "SELECT * FROM Final_Table", cn, , , adCmdText 
     ' filter records 

     For intColIndex = 0 To rs.Fields.count - 1 ' the field names 
      TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name 
     Next 
     TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data 

    End With 
    rs.Close 
    Set rs = Nothing 
    cn.Close 
    Set cn = Nothing 
End Sub 

Sub Company_Information() 

    Dim companyName As String 

On Error GoTo gotoError 

    companyName = Application.InputBox(Prompt:="Enter Company Name", _ 
          Title:="Company Name", Type:=2)     

    Exit Sub 'Don't execute errorhandler at end of routine 

gotoError: 
    MsgBox "An error has occurred" 

End Sub

上面的代码工作正常，并拉将所需的计算表放在Excel中的正确单元格中。

我有两个问题，我遇到了麻烦;首先，我已经为Excel中将要粘贴数据的单元格设置了一些单元格格式;我希望它将格式设置应用于Excel中的值。

其次;我有一个Excel附加组件，用于更新一些每日股市价值;这些值需要在每个工作日结束时转移到Access中，以保持数据库的维护，我尝试了一些代码，但一直在运行时遇到一些问题。

这部分的代码可以看出以下几点：

Sub UPDATE() 

    Dim cnt As ADODB.Connection 
    Dim stSQL As String, stCon As String, DataLocation As String 
    Dim stSQL2 As String 

    'database path - currently same as this workbook 
    DataLocation = ThisWorkbook.Path & DataLocation 
    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & DataLocation & ";" 
    'SQL code for GL Insert to Access 
    stSQL = "INSERT INTO Historical_Stock_Data SELECT * FROM [Portfolio] IN '" _ 
    & ThisWorkbook.FullName & "' 'Excel 8.0;'" 

    'set connection variable 
    Set cnt = New ADODB.Connection 
    'open connection to Access db and run the SQL 
    With cnt 
     .Open stCon 
     .CursorLocation = adUseServer 
     .Execute (stSQL) 
    End With 
    'close connection 
    cnt.Close 

    'release object from memory 
    Set cnt = Nothing 

End Sub

我碰到下面的错误与此有关。

Run-time Error '-2147467259 (80004005)' 

The Microsoft Jet database engine cannot open the file 'Cocuments and Settings\Alice\Desktop\Database'. It is already opened exclusively by another user or you need permission to view its data.

我对数据库，VBA和Access相当新，所以任何帮助将不胜感激。

此外，我被告知，不建议使用Excel前端和Access后端的上述方法，但他们进行的分析很多都是通过Excel来完成的，Excel中的图表功能要好得多以我的经验获得至少;这也是这个项目的要求之一。

谢谢提前！

为什么不链接到数据本身，而不是导入它的副本？ – 2010-09-08 22:01:55

第一批代码（定义为公共常量字符串）中的DataLocation是用作第二批代码中的数据位置（您将其重新分配的位置）的数据位置？ – barrowc 2010-09-09 02:32:27

是的，在这种情况下，我已经在第一种情况下将DataLocation定义为公共常量。 – md85 2010-09-10 12:22:38

答

解决您的第一个问题：
对不起，是坏消息，但您的整个第一个模块是不必要的。相反，尝试：

转到数据 - >导入外部数据 - >导入数据，选择您的Access文件，选择你的表，和急！完成了！
右键单击新的“外部数据范围”可查看多个选项，其中一些与格式相关。您甚至可以保留原始单元格格式并更新值。我一直这样做。
稍后要更新Excel数据表时，会出现一个“外部数据范围”工具栏，该工具栏允许您刷新它以及“刷新全部”选项以刷新Excel文件中的每个表格。（您也可以直通代码自动完成这一点。这将需要一些试验和错误，但你绝对能够胜任工作）

关于你提到的第二个问题
我从来没有使用过它，但那里也有一个“新的Web查询”选项。我认为它可以用相同的方式进行操纵和更新。

最后一点
你选择了Excel前端和访问后端听起来不错，适合您的需求。它将数据以熟悉的媒体（Excel）的形式提供给分析师，同时保持Access中的计算不受阻碍。从技术上讲，你可以尝试把所有的计算都放在Excel中，但这可能会导致Excel文件更大更慢地打开。

alt text

嘿，这是非常有用的，但唯一的是，如果您检查，我需要能够轻松输出单个公司相关数据，例如，如果有人在表单中输入公司名称，那么只有那些特定的公司股票市场数据，财务数据和许多不同的图表显示业绩出现。再一次，我对Access和VBA相当陌生，所以也许解决方案需要一些时间来处理，无论哪种方式，它都必须在某种代码中完成，特别是如果我想自动化图形/图表一代也是如此。 – md85 2010-09-13 09:24:36

如果你做了上面的方法，然后选择** Data-> Filter-> AutoFilter **，你可以得到一些或全部列标题的下拉菜单（你会看到一个向下的箭头在每列）。通过这种方式，他们得到了一个熟悉的接口选择公司的界面（如果你有很多公司，比如100+，有点问题） – PowerUser 2010-09-13 13:10:08

至于使用代码，它确实可以让你做任何事情。所以去做吧。 – PowerUser 2010-09-13 13:12:22

答

在Access中进行数据输入/更新/检查。 Access的一个优势是使用表单，允许你在没有任何代码的情况下更新表格。然后允许用户轻松地将数据导出到Excel，例如通过点击一些命令按钮。

Modules: Sample Excel Automation - 通过细胞的细胞是缓慢

Modules: Transferring Records to Excel with Automation

事情是托尼，我试图在Excel中制作一种仪表板，这样用户就可以从那里做所有事情，甚至不用担心在后面发生的事情。另一件事是，更新需要从Excel中的值中进行，因为我拥有的附加组件可以直接将最新的StockPrice值存入电子表格，我不会熟悉链接表的工作方式，但是可以通过链接特定的电子表格，可以在Access端做到这一点？问题是，Excel中的值需要在他们访问Access之前刷新。 – md85 2010-09-13 15:31:27

啊，好吧，你在最初的帖子里说过。这是彭博资料吗？如果是这样，我已经直接使用API将数据导入到表中。 – 2010-09-13 19:20:24

其实它并不是彭博数据，老板们想要一个便宜的解决方案，所以我发现了Excel的附加组件，它从雅虎财务和MSN那里获得它的价值，这些值在每个工作日结束时被拉到Excel中，因为数据库只需要存储当天的股价和成交量，我认为这样做是一个合理的选择。第二段代码应该将这些数据提取到特定的Access表中，现在我的VBA又不是那么强大，但是我正在学习，所以在这方面我们将不胜感激。 – md85 2010-09-14 11:50:50

答

无可厚非使用Excel /接入配对原则。我不熟悉ADO（我使用DAO），但是您的错误消息似乎表明数据源的路径没有完全形成;或者你已经打开它并锁定它。

访问 - Excel集成

相关推荐