从Excel导入Access [英] Access Import from Excel

问题描述

各位大家好，非常欢迎你的帮助，非常经典的

问题...


我有一些Excell Macro背景，有点VBA的一点知识。

我每天从互联网系列数据中导入。它们的设置如下，

还有一些关于权利的栏目，如股票价格，股息以及

on：


日期代码国家/地区调整原因名称股票

25/02/2005 FR0000045072 FR CREDIT AGRICOLE

25/02/2005 FR0000054900 FR TF1


我已经设置了一个excel宏，一旦我下载了

就保存了日期，其中包含一个包含每日日期的工作表名称：

DivivendData28_Feb_05。明天它将被保存为

DivivendData29_Feb_05


这里出现了困难。


我希望存储这些数据在单一访问数据库中。因为我不是excel的专家，所以我确实以一种简单的方式开始了

。我确实创建了一个空白的

数据库，并手动导入了一个电子表格。好的。但是当我导入第二个时，我会收到错误消息，说明：

"对象''IllmexGrid''失败的方法''列''然后

文件未导入


无论如何，我想从Access或Excel自动化，如果它是

$可能。


问候

Daniel

Hello everybody, your help will be very very welcome on a very classic
question...

I have some Excell Macro background, a little bit of VBA knowledge.
I import daily from Internet series of data. They are setup as follow,
with a few more columns on the rights as stock prices, dividend and so
on as:

Date Code Country Adjust Reason Name Shares
25/02/2005 FR0000045072 FR CREDIT AGRICOLE
25/02/2005 FR0000054900 FR TF1

I have setup an excel macro that save the date once I have downloaded
them, with a sheet name including the daily date as:
DivivendData28_Feb_05. Tomorrow it will thus be saved as
DivivendData29_Feb_05

Here difficulties arise.

I wish to store these data in a single access database. I did begin in
a na?ve way as I am not an expert at excel. I did create a blank
database and did manually import one the spreadsheet. Went fine. But as
I import the second one I do receive error messages stating that:
"Method ''Columns'' of object ''IllmexGrid''failed" and then
"File was not imported"

Anyway, I would like to automatize from Access or Excel, if it is
possible.

Regards
Daniel

推荐答案

查看帮助文件中的TransferSpreadsheet方法。


使用变量作为filename参数，例如

strFileName =" DividendData" &安培;格式（日期，dd）& " _" *格式（日期，

" mmm"）& " _" &安培;格式（日期，yy）


....虽然我建议使用yyyymmdd格式。哦，我好b $ b你觉得你明天会发现不是2月29日:-)


da ***** @ gmail.com 写道：

Check out the TransferSpreadsheet method in the help file.

Use a variable for the filename argument, e.g.
strFileName = "DividendData" & Format(Date, "dd") & "_" * Format(Date,
"mmm") & "_" & Format(Date, "yy")

....although I would recommend using the yyyymmdd format. Oh, and I
think you will find tomorrow is not February 29 :-)


da*****@gmail.com wrote:

大家好，非常欢迎你的帮助，非常好的
经典问题.. 。

我有一些Excell Macro背景，有点VBA知识。
我每天从Internet系列数据导入。它们被设置为
跟随，还有更多关于权利的列，如股票价格，股息和
等等：

日期代码国家/地区调整原因名称股票 25/02/2005 FR0000045072 FR CREDIT AGRICOLE
25/02/2005 FR0000054900 FR TF1

我已经设置了一个excel宏，在我下载后保存日期
工作表名称包括每日日期：
DivivendData28_Feb_05。明天它将被保存为
DivivendData29_Feb_05

这里出现了困难。

我希望将这些数据存储在一个访问数据库中。我确实以一种天真的方式开始
，因为我不是excel的专家。我确实创建了一个空白的数据库，并手动导入了一个电子表格。好的。但是，当我输入第二个时，
我收到错误消息，说明：
"对象''IllmexGrid''失败的方法''列''然后
文件未导入

无论如何，如果可能的话，我想从Access或Excel自动化。

问候
Daniel

Hello everybody, your help will be very very welcome on a very classic question...

I have some Excell Macro background, a little bit of VBA knowledge.
I import daily from Internet series of data. They are setup as follow, with a few more columns on the rights as stock prices, dividend and so on as:

Date Code Country Adjust Reason Name Shares
25/02/2005 FR0000045072 FR CREDIT AGRICOLE
25/02/2005 FR0000054900 FR TF1

I have setup an excel macro that save the date once I have downloaded
them, with a sheet name including the daily date as:
DivivendData28_Feb_05. Tomorrow it will thus be saved as
DivivendData29_Feb_05

Here difficulties arise.

I wish to store these data in a single access database. I did begin in a na?ve way as I am not an expert at excel. I did create a blank
database and did manually import one the spreadsheet. Went fine. But as I import the second one I do receive error messages stating that:
"Method ''Columns'' of object ''IllmexGrid''failed" and then
"File was not imported"

Anyway, I would like to automatize from Access or Excel, if it is
possible.

Regards
Daniel

谢谢，我看看我回到你身边。


好​​地方我刚刚意识到我们今年是哪一年......

Thanks, I am having a look I come back to you.

Good spot I just realized what year are we ...

我不想侮辱你的善意，但我真傻，我在文件名设置上有
a问题。

以下是excel宏如何将每日文件保存在文件夹中：


ChDirC：\Documents and Settings \Daniel \ My Documents \ Dividend and

Index pricing\CAC 40 \ Divivndnd Data"

ActiveWorkbook .SaveAs文件名：= _

" C：\Documents and Settings \Daniel \ My Documents \ Dividend和

指数定价\ CAC 40 \股息数据 &安培; _

格式（日期，dd_mmm_yy）& "&的.xls QUOT; _

，FileFormat：= xlNormal，密码：=""，WriteResPassword：=""，_

ReadOnlyRecommended：= False，CreateBackup：= False


因此，我应该在Access的FileName字段中写什么？我知道我

推开一点距离愚蠢的界限...


问候

DR

I do not want to abuse from your kindness but I am really dumb, I have
a problem on the file name setup.
Here is how the excel macro save the daily file on a folder:

ChDir "C:\Documents and Settings\Daniel\My Documents\Dividend and
Index Pricing\CAC 40\Dividend Data"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Daniel\My Documents\Dividend and
Index Pricing\CAC 40\Dividend Data" & _
Format(Date, "dd_mmm_yy") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Thus, what should I write in the FileName field in Access ? I know I
push a little bit far away the boundary of stupidity ...

Regards
DR

这篇关于从Excel导入Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！