Opening a Txt file in Microsoft Excel, Macro Code not accepted

[RachelMann]

Hi everyone,

I recorded a macro in excel and attempted to modify it to work in Blue Prism, but am having issues with the code and keep receiving an error ("Complier error at line 4. End of Statement expected."). Any ideas on how to fix this? Modified code below (note, folder path and file name have been edited for privacy):

ChDir _
("C:\folder path")
Workbooks.OpenText (Filename:="C:\BSEG.txt") _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.Save


Any help would be appreciated! Thank you.

------------------------------
Rachel Mann
------------------------------

[ewilson]

Hi @RachelMann,

Are you trying to use the Excel VBO to work with this text file or just a code stage in some VBO you've created?

Cheers,
​

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

[ewilson]

Give this a try. There's an issue with matching parenthesis.

ChDir("C:\folder path")
Workbooks.OpenText (Filename:="C:\BSEG.txt", Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True)
ActiveWorkbook.Save​

FWIW - You're including a lot of additional optional parameters that are doing nothing but cluttering your code.

Cheers,


------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

[RachelMann]

Thank you for your reply. Unfortunately, I am still getting the same error message, but for line 1 as the code is now on 1 line. Any additional suggestions or options to troubleshoot? Thanks again for your help!

------------------------------
Rachel Mann
------------------------------

[ewilson]

1 line? The code should be split across 3 lines.

Line 1: ChDir("C:\folder path")

Line 2: Workbooks.OpenText (Filename:="C:\BSEG.txt", Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True)

Line 3: ActiveWorkbook.Save​

Cheers,



------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------