• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Is there a way to pull Text from an Excel File via a Script? - Not a Variable Data Process

Engaged ,
Feb 28, 2023 Feb 28, 2023

Copy link to clipboard

Copied

Currently, I have to copy data from an online PDF into an XSLM Exel file. 
Then using a Macro I am able to pull and organize the needed data into a column L15:L32
Last, back in my Illustrator file I have Tethered Text Boxes, the 1st one contains the text "xxxx", I select that and "CTRL+V" to paste in the needed data.

 

Is it possible for a Script to grab the data from the active tab in Excel and do a Find and Replace if that script knows the name and location of the Excel file?

C:\Users\Public\Spec Work.xlsm


Any and all feedback welcome on this 🙂

TOPICS
Scripting

Views

853

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe
LEGEND ,
Feb 28, 2023 Feb 28, 2023

Copy link to clipboard

Copied

Not really. How would you even do that without being able to control Excel, which uses VBA? The apps just don't talk to each other. You could possibly create a function that fetches the data to the clipboard whenever a table cell is updated in Excel and paste it from there, but that's probably as good as it gets and even that is a super fragile construct. Of course you could also parse the XLSM file, but you'd still need to re-save it every time and parsing through a chunky XML node tree is anything but trivial and won't be fast. There's a ton of extra wrapping in these files, including binary data which you would need to strip/ ignore.

 

Mylenium

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Feb 28, 2023 Feb 28, 2023

Copy link to clipboard

Copied

@Mylenium yeah I knew they didn't really communicate all that well unless the excel file was a simple XML, but wasn't sure if there was a way to "code around" the issue by way of some how some way creating a JSON file or the like.   But I've been amazed at what scripts have been able to accomplish so I thought I would make the ask  🙂

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 28, 2023 Feb 28, 2023

Copy link to clipboard

Copied

Since you're on Windows, there are two ways I can think of, both are similar

 

1. an Illustrator vbs script can talk to both Excel and Illustrator

2. a vba script in Excel can talk to Illustrator

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Mar 01, 2023 Mar 01, 2023

Copy link to clipboard

Copied

Okay so here is what I did....  I wrote a macro within my Excel document to copy the selected cells and save them to a text file called "Spec.txt".  Then write a script that would do a find and replace to fill in the needed data. Finally, the script clears the txt file so that there's no fear of importing the wrong data.

// Get a reference to the currently active document
var doc = app.activeDocument;
var sel = doc.selection;

// Call the function to get the text string from a file and replace the contents of any text frames containing the word "ordnum"
getspecInformation();

// This function reads the contents of a file and returns them as a string
function getspecInformation() {
var specText = getTextString("C:/Users/Public/Spec.txt");
// If the file exists and is not empty
if (specText !== null && specText !== "") {
// Replace the contents of any text frames containing the word "ordnum" with the contents of the file
replaceText(specText);
}
}

// This function reads the contents of a file and returns them as a string
function getTextString(filePath) {
var textString;
var file = new File(filePath);
// If the file exists
if (file.exists) {
// Open the file in read mode
file.open("r"); // "r" stands for read
// Read the contents of the file into a string
textString = file.read();
// Close the file
file.close();
// Return the string containing the file contents
return textString;
} else {
// If the file does not exist, return null
return null;
}
}

// This function searches for the word "ordnum" in any text frames in the document and replaces it with the specified text
function replaceText(specText) {
// Create a regular expression to search for the word "ordnum" (with the "g" flag for a global search)
var searchString = /ordnum/g; // g for global search, remove i to make a case sensitive search
// Get a reference to all the text frames in the document
var textFrames = doc.textFrames;
// Declare some variables for use in the loop
var thisTF, newString;
// If there are any text frames in the document
if (textFrames.length > 0) {
// Loop through each text frame
for (var i = 0; i < textFrames.length; i++) {
thisTF = textFrames[i];
// If the contents of the text frame contain the word "ordnum"
if (thisTF.contents.indexOf("ordnum") !== -1) {
// Replace the word "ordnum" with the specified text
newString = thisTF.contents.replace(searchString, specText);
// Set the contents of the text frame to the new string
thisTF.contents = newString;
}
}
}
// Call the function to clear the contents of the file used for replacement
clearFileContents("C:/Users/Public/Spec.txt");
}

// This function clears the contents of the specified file
function clearFileContents(filePath) {
// Create a new file object using the specified file path
var file = new File(filePath);
// If the file exists
if (file.exists) {
var fileLength = file.length;
if (fileLength > 0) {
file.open("w"); // "w" stands for write
file.close();
}
}
}




Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Mar 08, 2023 Mar 08, 2023

Copy link to clipboard

Copied

So I am getting some additional garbage in my text file.  The date format changes to a numeric format and my dimension text
example: (4"w x 6"h) 
come into the txt file as ("4""w x 6""h")

Any Ideas on how I can fix that?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 08, 2023 Mar 08, 2023

Copy link to clipboard

Copied

LATEST

str.replace(doubleQuotes, singleQuotes)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Feb 28, 2023 Feb 28, 2023

Copy link to clipboard

Copied

Can you export the Excel file to CSV? If so, then as long as it's a simple CSV, you can read it into ES like any other file and extract the info you need. It does require the extra step of exporting the file though.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines