InDesign CC and Excel Scripting, Type definition in VBA

[csab6597pv]

for years now I am using VBA scripts in Microsoft Excel to control InDesign. I used the code:

Dim InApp as InDesign.Application

Set InApp = CreateObject("InDesign.Application")

but now in InDesign CC 2019 this is a 'type mismatch' error. It works if I do:

Dim InApp as Object

Set InApp = CreateObject("InDesign.Application")

But then of course I do not have any type information in the debugging view and so on. This is extremely annoying, as I have a lot of objects like InDesign.Font, InDesign.Table, InDesign.Swatches etc.

In Excel I think I have the correct reference to the type library "Adobe InDesign CC 2019 Type Library" located at c:\ProgramData\Adobe\InDesign\Version 14\...

How to get this set up properly?

[Kasyan Servetsky]

I wrote about the problem a while ago.

[csab6597pv]

seems I found it after hours.

I deleted the .tlb type file, started InDesign as administrator, closed it, started InDesign normally again. This recreates the .tlb file yet again, but something else does happen such that it works now. Very dumb indeed, I remember to have read about this somewhere a long time ago for an older version of InDesign, seems not to be fixed yet.

[erwanp38748770]

Hey guys

How are you ?
I need some help to use Indesign with VisualBasic code
I an using MAC, so ... i don't know if it's possible
The library  do not exist in VBA reference

I really need some helps
I want to use VBA to open an Indesign files, and import some text and image in a template

Thanks for your help guy

Erwan

[Kasyan Servetsky]

It's impossible to use VBA on MAC — it's for Windows only. You need Apple Script for this.

Alternatively, you can use a 'JavaScript + Apple Script' approach (my favorite): execute AS from InDesign's JS via doScript() method, sending parameters (arguments) and getting back results if necessary.

[erwanp38748770]

Hi Kasyan 

thank you very much for your answer

i am surprised because I am using VBA on excel on Mac 

i can open the Visual Basic editor and have already put some code that working fine 

so maybe it's because there was a recent update on MAC ? 
Somebody else to find this library on Mac ? 

thanks 😊 

[Kasyan Servetsky]

I am on early 2009 Mac Pro — hacked to install Mojave 10.14.4 to support the latest InDesign.

I have MS Office 2016 (version 16.16.7) and have no idea about VBA on Mac.

Maybe you are on MS Office 2019 and things are different there.

As to me, I am more familiar with VB than AS — this is the first programming language I learned.
Anyway, that's the end of the road for me: my company is not going to buy a new Mac so I can't update it.

Here I posted a function to get data from Excel (both VBA and AS versions) which worked for me very well in a few projects. Hope this may get you started.

— Kas

[erwanp38748770]

Hey 

Thanks for your answer 
I use VBA in excel visual basic editor
To open it, i had to add the Feature on the top bar of my excel

Thanks for your solution, but i want to drive Indesign with excel, and not drive Excel with indesign
Is that you suggest no ? 
(sorry i am french)

[rob day]

Not sure if this helps, but this AppleScript gets an excel or numbers file’s text with each row as a paragraph, with tab delimited colums:

tell application "Adobe InDesign 2020"
	--a text selection
	set sel to selection
	set theText to my getText()
	--sets the contents of the parent text flow to the excel or numbers file with tab delimited columns and rows as paragraphs
	set contents of parent of item 1 of sel to theText
end tell

on getText()
	tell application "Adobe InDesign 2020"
		set thePath to choose file with prompt "Select a destination folder"
		set myFileRef to open for access (thePath as alias)
		set myEOF to get eof myFileRef
		set myText to read myFileRef using delimiter {return}

	end tell
	return item 1 of myText
end getText

[rob day]

This version converts the excel text into an InDesign table:

tell application "Adobe InDesign 2020"
	--a text selection
	set sel to object reference of selection
	
	set p to object reference of parent of selection
	set contents of sel to my getText()
	
	set t to object reference of text from character 1 to character (count of characters of p) of p
	tell t
		--convert to table needs hard returns
		my GrepSearch("\\s?\\n", "\\r")
		try
			convert to table
		end try
	end tell
end tell

on getText()
	tell application "Adobe InDesign 2020"
		set thePath to choose file with prompt "Select a destination folder"
		set myFileRef to open for access (thePath as alias)
		set myEOF to get eof myFileRef
		set myText to read myFileRef using delimiter {return}
	end tell
	return item 1 of myText
end getText

--a grep search 
on GrepSearch(f, c)
	tell application "Adobe InDesign 2020"
		set find grep preferences to nothing
		set change grep preferences to nothing
		set find what of find grep preferences to f
		set change to of change grep preferences to c
		change grep
	end tell
end GrepSearch

[palala fog]

Good morning,

Please, how to use your script? Placing the excel files in Indesign?

Tried to use this Apple script but this error appeared:

This is the script you proposed:

tell application "Adobe InDesign 2020"

--a text selection

set sel to object reference of selection

set p to object reference of parent of selection

set contents of sel to my getText()

set t to object reference of text from character 1 to character (count of characters of p) of p

tell t

--convert to table needs hard returns

my GrepSearch("\\s?\\n", "\\r")

try

convert to table

end try

end tell

end tell

on getText()

tell application "Adobe InDesign 2020"

set thePath to choose file with prompt "Select a destination folder"

set myFileRef to open for access (thePath as alias)

set myEOF to get eof myFileRef

set myText to read myFileRef using delimiter {return}

end tell

return item 1 of myText

end getText

--a grep search

on GrepSearch(f, c)

tell application "Adobe InDesign 2020"

set find grep preferences to nothing

set change grep preferences to nothing

set find what of find grep preferences to f

set change to of change grep preferences to c

change grep

end tell

end GrepSearch