导入链接表时删除垃圾第一行 [英] Delete Junk First Row When Importing Linked Table

问题描述

我有一个从Excel导入的链接表，数据从我无法控制的源导出。传入表包含第1行中的标题，第一个单元格中只有文本后跟空白单元格，第2行中的列名称。我目前在导入/链接表格之前手动删除第一行，但我需要设置它可以让其他人使用它而无需经过这个手动步骤。我想设置一些代码（VBA）来自动完成。这将需要：


1）确定第一行是否包含列名或垃圾。即：检查第一行，第一个单元格（A1）是否以课程和课程开头。或标题或（因为如果以标题开头，则垃圾行已被删除）;或者：检查单元格B1，C1，D1等是否为空（表示垃圾行）。


2）如果第一行确实是垃圾，则将其删除。


理想情况下，此代码将与链接表更新集成，以便在导入时对表进行清理。这是在更新过程中发生还是在发生后不久对我来说，只要在链接/导入表时才会这样做。

解决方案

< blockquote>你有什么乔尔？重要的是，所需的代码大部分都是确定与问题无直接关系的东西，但需要花费大量时间来开发（EG。正在处理的Excel文件的地址，可能会选择如果有必要的话。


实际问题很容易处理，但我们不应该浪费时间为你开发整个项目。你需要在问题中包含这些内容。

可悲的是，我还没有。我是一个完整的VBA菜鸟，我希望能指出正确的方向。我不希望任何人为我编写代码，但即使找出可能有用的特定功能也会受到赞赏。

我的方法是导入Excel电子表格，然后分析第一个记录中的第一个字段，之后采取适当的操作。

1. 如果表已经存在，请删除它。
2. 导入电子表格。
3. 如果第一个记录的第一个字段中的值=课程，则删除行。
   

   展开 | 选择 | 换行 | 行号

I have a linked table that is being imported from Excel, with the data being exported from a source I cannot control. The incoming table contains a heading in row 1 that only has text in the first cell followed by blank cells, with the column names in row 2. I am currently manually deleting this first row before importing/linking the table, but I need to set it up so that others can use it without having to go through this manual step. I would like to set up some code (VBA) to do it automatically. This would entail:

1) Determine if the first row contains column names or junk. ie: Check if the first row, first cell (A1) starts with either "Curriculum" or "Title" (because if it starts with "Title" the junk row has already been deleted); or: check if cells B1, C1, D1, etc are blank (indicating a junk row).

2) If the first row is indeed junk, delete it.

Ideally, this code would be integrated with the linked table update, so that the table is ''cleaned'' as it''s imported. Whether this happens in the update process or immediately after doesn''t matter to me, as long as it is only being done when the table is being linked/imported.

解决方案

What do you have already Joel? It''s important, as much of the code required would be to determine things which are not related directly to the problem, but would take a lot of time to develop (EG. The address of the Excel file being worked upon and possibly selecting it if that were necessary).

The actual question is easy enough to handle, but we shouldn''t be wasting our time developing the whole project for you. You need to include such stuff in the question.

Sadly, I have nothing yet. I''m a total VBA noob, and I''m hoping to be pointed in the right direction. I don''t expect anyone to write the code for me, but even identifying specific functions that might help would be appreciated.

My approach would be to Import the Excel Spreadsheet, then analyze the 1st Field in the 1st Record, taking appropriate action afterwards.

1. If the Table already exists, DELETE it.
2. Import the Spreadsheet.
3. If the Value in the 1st Field of the 1st Record = "Curriculum", then DELETET the Row.
   

   Expand|Select|Wrap|Line Numbers

这篇关于导入链接表时删除垃圾第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！