Option Strict On

[LadyDi]

I have been doing quite a bit of research about Visual Basic and trying to improve some of my databases and some modules I have in Excel. In my research, I came across the phrase "Option Strict On". From what I read, when this is placed prior to any event handlers, it prevents automatic conversions of values. One of the modules I have in Excel exports certain data to another spreadsheet. Then some of my co-workers import that spreadsheet into a database. For some reason, when the data is imported into the database, the formats are changed (numbers are formatted as text, where they are formatted as "General" in the spreadsheet). I thought if I added this line at the beginning, I could prevent that from happening. However, Excel and Access with neither one allow me to use this statement. I've put it under the "Option Explicit" statement and I have tried replacing the "Option Explicit" statement with the "Option Strict On" statement, and neither way works. Do you know why I can't use this statement? Do you have any suggestions as to how to stop Access from changing the formats upon import?

 

[boblarson]

As for changing formats, if numbers are formatted as GENERAL and not numbers, if there are any spaces in any of the first several rows, or any text, then it will change it to text. If there are spaces that can't be seen, Access will still find them and if there is even one there, it will change it automatically.

If you can't control the spreadsheet, then I would import to an interim table and use append queries to move the data to the right fields using the conversions like CInt, CLng, etc.

 

[LadyDi]

Thank you very much for the information and the advice. I appreciate it.

 

[LadyDi]

Is there a way that I can "trim" the whole spreadsheet via the vba code to remove all trailing spaces or leading spaces?

 

[boblarson]

Is there a way that I can "trim" the whole spreadsheet via the vba code to remove all trailing spaces or leading spaces?

It is possible to use code to go through each cell on the spreadsheet (sorry, no bulk option exists of which I am aware). It would be much easier and also take less time for the operations to occur to do what I suggested with an interim table.

 

[LadyDi]

Okay, thank you.

 

[SteveH2508]

It is possible to use code to go through each cell on the spreadsheet (sorry, no bulk option exists of which I am aware). It would be much easier and also take less time for the operations to occur to do what I suggested with an interim table.

http://www.asap-utilities.com/index.php ASAP Utilities has a tool for stripping out spaces in Excel worksheets.