How to import the content of an excel textbox into Navision?

Minu63

Good Morning to everybody!

I have to import the content of an excel textbox (not of a regular cell) into navision. How can I do so?

Thanks a lot

Sog

Is it possible to modify the excel file to have the value of the textbox copied into a hidden sheet in the excel file?
Then you don't have to deal with the problem you layed out.

Minu63

I'm afraid that this wouldn't work. I have to import thousands of excel sheets (service reports of the past x years). I already tried to write an excel macro to copy the content of the textboxes (there are four on the same sheet) into a regular cell, but I failed writing this macro
But however I think it's also impossible to run the excel macro from Navision, isn't it?

Sog

Well If you've failed to write that macro, you migth experience the same problem with import in NAV.
Because the macro exists of the same object as one would implement in NAV. The workbook, worksheet types are also used in NAV. I wish you the best of luck in finding a solution.

rmv_RU

Try to save Excel workbook in xml format and then read xml file and parse it.
See also reading large texts from Excel

Minu63

In the meantime I found a solution on the Microsoft homepage:

Sub CopyAllTextboxContentsToCell()

' .... define first row
x = 1

' .... loop through all textboxes
For Each tbox In ActiveSheet.TextBoxes

' .... select cell and insert text
Range("A" & x).Value = tbox.Text

' .... increase row number
x = x + 1

Next tbox

End Sub

And now the content of the cells A1, A2, etc, is really very easy to import in Navision. Concerning that Navision cannot manage texts larger than 1024 caracters I had to modify the VB Module a little bit:

' .... select cell and insert text
Range("A" & x).Value = Left(tbox.Text, 1024)

Unfortunately the case of a textbox with more than 1024 caracters isn't managed, but in this special case that doesn't matter.

I hope that this solution helps others, too.

David_Singleton

Minu63 wrote:

I hope that this solution helps others, too.

:thumbsup: Hi Minu,
thanks for sharing. Its always nice when someone solves their own problem but still comes back to share the solution.

And to rmv_RU, good suggestion thinking outside the box. =D>

Definitely good to see threads like this.