Join excel tables - problem

[Bookmaster]

Hi,

I have to join 2 excel sheets. In all sheets I have just one column with different numbers. So I import all tables (sheets) thru "external data" then excel... Then I start with create, query design and choose both tables, then mark fields, and make Join Properties and finally RUN. All of that as I did many times before. But now I am getting msg: "Type mismatch in expression".
I did check both sheets and they have format like general. I tried also with custom on both sheets, then tried with text format as well, but every time I have same msg: "Type mismatch in expression".
I am using Access just to joing to or more exccel sheets and I don't know much about it.
Where I am wrong please?
How can I find problem and solve it please?

best regards

[ranman256]

this happens. Access cant determine if the field is string or numeric, so the join fails.

Before I use an XL file, i configure my field by...
to string by putting a quote in front of every cell. (for string fields)
for numerics: I run a macro to validate there are no strings in it.

Public Sub Cvt2Txt()
While ActiveCell.Value <> ""
   If Left(ActiveCell.Value, 1) <> "'" Then ActiveCell.Value = "'" & ActiveCell.Value
   NextRow
Wend
End Sub

Then access works fine.

[Bookmaster]

I am not sure if I understand you. I opened my XL table, then I open MS Visual basic, made new module and when I run it I got compile error: Sub or Function not defined.
I might explain it wrong in start. I have two XL sheets and I have just A column populated with numbers with six digits. I would like to join XL tables with access. I know there is problem with formatting but I tried almost every option and I still get same error.
I would like to have macro that puts all numbers in column A into number formatting so that I can join XL tables in access.