如何在将excel表导入访问权限之前对其进行编辑 [英] How do you edit an excel sheet before importing it into access

问题描述

我正在实习的公司是将格式数据从excel表格转换为可过滤的访问图表。我是VBA的新手，目前我有一个按钮，可以导入excel表并重新格式化信息，使其更适合访问者喜欢的内容。我遇到了一个问题，但是在excel表单上的一些事情是，所有表中的前两行是视觉绒毛，第三行包含我需要的字段名称。如何删除Excel工作表中的前两行，而不保存并将其导入访问权限。我遇到的另一个问题是excel工作表中的字段名称和表名称有空格或句点，从我们经历过错误的方面来看，当需要引用它们时，VBA中有一个大的no。所以我还想知道如何在导入之前用下划线替换excel表中的所有空格，同样不会保存到原始的excel文件中。由于是由公司的高级公司处理的东西，除了启动代码的按钮按钮之外，绝对没有一个可以手动。 Access 2007

The company i am interning for is having me format data from an excel sheet into a filterable access graph. Im rather new to VBA and at the moment i have a button that imports an excel sheet and re-formats the information to be more fitting to what access likes. I run into an issue however with some of the things that are on the excel sheet side, being that the first 2 rows in all of the sheets are visual fluff and the third row contains what i need for field names. How can i delete the first 2 rows in the excel sheet, without saving it and have it imported into access. The other issue i come across is that the field names, and table names, from the excel sheet have spaces or periods, which from what Ive experienced error wise is a large no in VBA when they are needed to be referenced. So i would also like to know how i can replace all the spaces in the excel sheet with an underscore before importing, again without being saved to the original excel file. Due to being something that is handled by higher ups in the company absolutely none of this can be manual, aside from the button push that starts the code. Access 2007

推荐答案

您的上一条评论可能是最重要的评论之一，也是理想的第一条评论。在这种情况下，从处理文件起源的那些人那里获得承诺是至关重要的，这些文件的格式永远不会改变。通过非常真实地解释，您编写的任何代码都将严重依赖于准确找到您期望的数据，这是最容易实现的。如果没有高水平的技能和经验，这些代码就无法设计灵活。因此，您的代码将像自动机一样工作。完全适合所提供的格式。 任何其他人完全无用（或更糟）。


你可能会从我的评论中猜到我，以及我听到过的许多其他人，定期处理为一种文件格式设计的系统，然后一旦格式被更改，就可以处理该文件。它也可能发生在你身上。至少如果你努力让那些负责人去理解这个问题，那么这个机会就有可能被最小化。在这一点上，确保你有好的笔记，当他们回来抱怨它不再正常工作时你可以再次参考。这些注释应该与文件本身相关联。或者在某个地方，当用户回来时，您可以轻松快速地找到它们。


更改Excel电子表格中的数据是一回事，但可以直接从电子表格导入Excel中的Access，如与文件相反，可能比你想要在这个阶段玩的要复杂得多（除非你是一个非常雄心勃勃的实习生，在你没有理由的地区拥有大量的自信）。访问也往往不喜欢导入文件，如果它们在Excel中打开并因此被锁定。我建议您进行更改，然后将它们保存在新的位置，然后在Excel中关闭文件，然后再将文件导入Access。


阅读完上次评论现在，并回到我之前写的，我想这将不得不在VBA而不是手动完成。可能但不是基本我很遗憾地说。我建议最好的起点是你要导入的数据库的VBA。这必须使用应用程序自动化。这允许您通过在进行更改之前获取打开Excel和文件的Access以及将更改后的副本保存到其他位置来完成Excel中的工作。


您将看到什么我已经说过这将是一个相当大的项目。这里任何人都不应该为你做的事情。 OTOH，如果你准备接受它，那么当并发症混淆或压倒你时，我们当然可以提供帮助。


我希望你觉得这很有用。

Your last comment is possibly one of the most important and would have been ideal as the first statement. In such circumstances it is critically important that you get a commitment from those dealing with the origination of the files that the formats of these files never change. This is most easily done by explaining, very truthfully, that any code you write will be heavily dependent on finding the data exactly where you expect it. Such code cannot be designed to be flexible without high levels of skill and experience. Thus, your code will work like an automaton. Perfectly fit for the format supplied. Perfectly useless (or worse) for any other.

You will probably guess from my comments that I, as well as many others I''ve heard from, regularly deal with systems designed for one format of file that are then expected to deal with the file once the format''s been changed. It will probably happen for you too. At least if you make a good effort to get those in charge to appreciate the issue there''s a chance that will be minimised. On that point make sure you have good notes that you can refer to again when they come back complaining it doesn''t work properly any more. Those notes should be somewhere associated with the file itself. Alternatively somewhere you can get to them easily and quickly when the users do come back.

Changing data in an Excel spreadsheet is one thing but importing directly from a spreadsheet into Access from Excel, as opposed to from a file, is probably a lot more complicated than you want to play with at this stage (Unless you''re a very ambitious intern with oodles of self-confidence in an area you have no reason to be). Access also tends not to like importing files if they''re open, and thus locked, in Excel. I suggest you make your changes and then save them in a new place, before closing the file in Excel, before, in turn, importing the file into Access.

Having read the last comment now, and returned to what I wrote before, I guess this will have to be done in VBA rather than manually. Possible but not basic I''m sorry to say. I would suggest the best place to start is in the VBA of the database you want to import into. This would have to use Application Automation. This allows you to do the work in Excel by getting Access to open Excel and the file before making the changes and saving the changed copy away somewhere else.

You''ll see from what I''ve said that this is going to be a sizeable project. Not something that anyone here should be doing for you. OTOH if you''re prepared to take it on then we can certainly assist when the complications confuse or overwhelm you.

I hope you find this helpful.

让我补充一点，如果你的老板，在意识到跳入深水这对于实习生有多少，请鼓励你继续这个项目，那么你可以获得一些非常有用和有趣的经验。我怀疑你会喜欢它，即使你被困了几次。可能因为你被困了几次并设法超越它。 应用程序自动化也是一个非常有趣，强大且有用的领域。特别是在办公室类型的环境中。

Let me just add that if your bosses, after realising just how much of a jump into deep water this would be for an intern, do encourage you to proceed with the project, then you could garner some very useful and interesting experience. I suspect you''d enjoy it even if you did get stuck a few times. Probably because you got stuck a few times and managed to get past it. Application Automation is also a very interesting, powerful and useful area. Particularly in office type environments.

感谢您的回复并引导我走向我需要做的事情。幸运的是，我是一名全职实习生，这是我目前唯一的项目，所以我只有时间学习。幸运的是，这应该是我为项目做的最后一件事。当我进一步深入项目的这一步时，我可能会寻求一些帮助。

Thank you for the response and for leading me towards what i need to do. Luckily i''m a full time intern with this as my only current project so i have nothing but time to learn. Also luckily, this SHOULD be the last thing i have to do for the project. I will likely be asking for some help as i move further into this step of the project.

这篇关于如何在将excel表导入访问权限之前对其进行编辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！