Inserting data from Excel to Access by VBA

[lukelucky]

Hello everybody,

I've just started with Access and some coding around it. Right now, I'm trying to deal with my first issue, which I would like to approach you with.

I'm trying to put together some copying tool, copying the data from Excel to Access. My code is currently working as long as the "DoubAmount" is a number. Unfortunatelly, there are also the cells containing text in my Excel sheet I source the data from. At the point, when macro reaches these cells (there is a loop going cell by cell through the row), it breaks down. The error message says:

Run-time error '13':

Type mismatch

Would you have by chance any idea, how to redefine the variable "doubAmount" to enable copying over both number and text data? Or where else could be a problem? I've tried to replace the "Double" by "Variant", but it wasn't working either.

I will post here only the parts of the code, that I'm working with at the moment and I consider crucial.

First, I've got the declaration of variables:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Then, I've defined ranges with the data and stored all of the data into globalXLSmax:

Please Login or Register  to view this content.

("globalXLSdata(i).doubAmount = vAmount(i, 1)" is referenced by debugger as a source of the breakdown")

globalXLSdata is then later referenced when I "INSERT INTO" the data into the Access:

Please Login or Register  to view this content.

Thank you for any help!

[cytop]

My code is currently working as long as the "DoubAmount" is a number.

Not unreasonable as you have declared DoubAmount as a Double... and you cannot put the value of a "cell containing text" in a Double - you have to convert to a number first. Can't say what the actual conversion would be like as you don't give an example of the data.

[Arkadi]

based on your sql string, the database field for doubamount is a numeric data type.... so even if you define the variable as a variant or string, I don't imagine the sql exectution would work?

[lukelucky]

Thank you for your answers! You are completely right. The point is, that the colleague, who started with coding prepared the code only for copying the cells containing numbers. However, he hasn't considered, that there might be some other "text" cells such as links to websites, periodicity classification ("daily", "weekly", etc.) and some other specifications (e.g. "historical values").

Now, I'm trying to figure out the way, how to avoid breaking the code. At least for now, I would like to try to skip all the columns including text, as the numbers are the most important for me.

Please Login or Register  to view this content.

Do you think there is a way, that I could at least convert all the text cells to zeros by addiding similar code as below to the one above.

Please Login or Register  to view this content.

This one isn't working as it says also:

Run-time error '13:

Type mismatch

[Arkadi]

if a cell has text, and you dim the variable as double, you will get that error... however, if you use the "val" function it will return 0.

For example Cell A1 has "hello" in it:

Please Login or Register  to view this content.

Results in Type Mismatch.

Please Login or Register  to view this content.

results in i being 0

Which also means you would not need the if statement to check whether the value is >0

[lukelucky]

Excellent, such a solution is exactly, what I need at the moment

Thanks!

[Arkadi]

You're welcome

Please remember to mark the thread as solved if that fixes your issue? Thanks!