vba Excel访问：零长度字符串为空号 [英] vba Excel to Access: zero length string to Null number

问题描述

我在Excel中的同一列中有两个值。我选择其中之一并运行以下操作：

  Debug.Print IsNumeric（Selection），_ 
 VarType（Selection ），_ 
 VarType（Trim（Selection）），_ 
> &安培; Selection.Value& <，_ 
 Len（Trim（Selection）），_ 
 Len（Selection），_ 
 Selection.NumberFormat 
  

然后我选择另一个并运行相同的调试。

我得到这个：

• True，5，8，> 9.46979663546499< 16，16，General


• False，8，8 ，>< 0，0，General

注意：该列有多次出现

1. 有人可以解释一下吗？我已经vba'ing和Excel的很长一段时间，我仍然没有（详细）Excel的数字格式，以及如何最好地与他们合作。我想我有它，然后我总是绊倒这样的新东西。


2. 在这种情况下，我的目标是让MS Access自动理解这个列是一个双/数字/浮点数/任何列，当我导入它可以是NULL，而不是抛出错误。在导入之前，我必须通过格式化/更改Excel来实现此目的。 （部分原因是因为我的客户端流程最好，部分是因为我想让我的头脑终于...不能相信我还没有！）我有超过2000行更改每列，所以解决方案一次格式化整个列将是最好的，而不仅仅是一个单元格。

谢谢！

解决方案

IsNumeric为数字返回true，为空格返回false。我不知道这是否是意想不到的，但是MS不得不让它返回一个或另一个。逻辑是空白不是数字或文本。

Vartype为数字返回Double（如预期）。如果我的VarType是一个空单元格，我得到vbEmpty（0），而不是8（Excel 2010 x86）。如果我在单元格中放了一个单引号，我就会得到相同的一样。

当您修剪（）某事时，您将其转换为文本。修剪什么，Trim函数只返回一个字符串，所以你总是得到VarType 8。

阅读这篇关于混合数据类型的文章 http://dailydoseofexcel.com/archives/2004/06/03 /外部数据混合的数据类型/ 。确保您阅读评论。

当Office程序导入时，它使用一些注册表项来确定数据类型。通常，它读取字段的前8行以确定数据类型是什么。如果它看到数据类型的混合，它选择大多数，并转换或忽略其他所有内容。您可以更改注册表设置以查看超过8行或将所有内容默认为文本，但不能将其视为数字。

它会很好，如果它只是忽略空单元格，并占据其余的大部分。但是，空单元格不仅仅是Excel之外的一个概念，所以并不让我感到意外。

我认为正确的答案是创建一个模式文件，并将其放在与要导入的文件相同的目录中。请阅读 https://msdn.microsoft .com / en-us / library / ms709353％28v = vs85％29.aspx 这本质上是将所有列数据类型设置在一个文件中。

我几乎每天在Excel VBA中使用它 - 我使用VBA创建一个Schema.ini文件，然后使用ADO读入文件。我从未在Access中使用过，特别是通过UI导入。但值得一试。如果不起作用，您可以在VBA和ADO中自行进行所有导入。

I have two values in the same column in Excel. I select one of them and run the following:

Debug.Print IsNumeric(Selection), _
            VarType(Selection), _
            VarType(Trim(Selection)), _
            ">" & Selection.Value & "<", _
            Len(Trim(Selection)), _
            Len(Selection), _
            Selection.NumberFormat

Then I select the other and run the same debug.

And I get this:

• True, 5, 8, >9.46979663546499<, 16, 16, General
• False, 8, 8, ><, 0, 0, General

Note: the column has multiple occurrences of both

1. Can someone explain this? I've been vba'ing and Excel'ing a long time and I still don't get (in detail) the number formatting Excel does and how to work with them best. I think I have it then I always stumble upon something new like this.
2. In this case my objective is to get MS Access to automatically understand that this column is a double/number/float/whatever column that can be NULL when I import it and not throw errors. I have to achieve this through formatting/changing it in Excel prior to importing it. (Partly because that will work best with my client's processes and partly because I want to get my head around this finally...can't believe I don't already!) I have over 2000 rows to change for each column so a solution that formats the entire column at once would be best, not just one cell at a time.

Thanks!

解决方案

IsNumeric returns true for the number and false for the blank. I'm not sure if this is unexpected, but MS had to make it return one or the other. The logic is that a blank is neither numeric or text.

Vartype returns Double for the number (as expected). If I VarType an empty cell, I get vbEmpty (0), not 8 as you get (Excel 2010 x86). If I put a single apostrophe in the cell, I get the same as you.

When you Trim() something, you convert it to text. It doesn't matter what you trim, the Trim function only returns a string, so you will always get VarType 8.

Read this post on mixed data types http://dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/. Make sure you read the comments.

When an Office program imports, it uses some registry keys to determine data types. Typically, it reads the first 8 rows of the field to determine what the data type is. If it sees a mixture of data types, it picks the majority and converts or ignores everything else. You can change the registry settings to look at more than 8 rows or to default everything to text, but you can't tell it to treat empty cells as numbers.

It would be nice if it would simply ignore empty cells and take the majority of the rest. But 'empty cell' is just not a concept outside of Excel so it doesn't really surprise me.

The right answer for you, I think, is to create a Schema file and put it in the same directory as the file you're going to import. Read about it at https://msdn.microsoft.com/en-us/library/ms709353%28v=vs.85%29.aspx This is essentially setting all your column data types in a file.

I use this almost every day in Excel VBA - I use VBA to create a Schema.ini file, then use ADO to read in the file. I haven't ever used this in Access, particularly importing through the UI. But it's worth a try. If it doesn't work, you can just do all the importing yourself in VBA and ADO.

这篇关于vba Excel访问：零长度字符串为空号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！