Not copying Excel to Notepad as CSV format

[Ochimus]

Macro needs to copy Excel worksheet, open Notepad and save data as a CSV file.

But generates an error when setting strData = .GetText

Code does not include the "last leg" which needs to convert the Tab separated form to a Comma separated one.

Please Login or Register  to view this content.

Trust someone can see the error? All solutions, suggestions and alternatives welcome as ever.

Ochimus

[Marc L]

Hi,

why using the clipboard instead of just saving directly the text file on the disk ? As Notepad is useless …

[dangelor]

Possibly...

Please Login or Register  to view this content.

[Ochimus]

Marc,

I'm using Notepad because the End User wants the Excel data (not the whole sheet) in CSV form that then gets input to some other software.

dangelor,

Appreciate the equally prompt response, but the line is simply renaming the Excel file complete with the Macro button?

I need the outcome to be a folder containing the original Excel file with Macro button and a text file in Notepad showing the data as CSV.

Hope that clarifies?


Ochimus

[dangelor]

The code simply creates a 'comma delimited' text file named "TextCSV.txt" in the same folder as the workbook holding the code. You can then open the .txt file with any program you wish.

[Marc L]

Ochimus,

as Excel can create a standard csv text file, you can first SaveCopyAs your source workbook to a temp one,
open this temp one then apply the SaveAs to a csv file like Dangelor showed you in post #3, so easy in just 3 codelines …
A 4th codeline is needed to just kill the temp workbook if necessary.
Then if really really necessary you can launch Notepad to open the csv text file …

[Ochimus]

Thanks to both for input.

Followed your path, which saves the data, but still having issues getting Notepad to open automatically and upload the CSV data automatically.

Still don't understand why the original approach of copying the data to a clipboard, opening Notepad, and adding the info didn't work.

Ochimus

[CK76]

Although I agree with other posters that you don't need to use clipboard...
I tested your code and found no issues.

Error must be specific to your environment.

I removed section not needed, and ran code on my environment. No error generated and returned result fine.

Please Login or Register  to view this content.

When you expand Clipboard menu in Home ribbon. Do you see that it contains string? If it doesn't, that causes .GetText to throw error.

0.JPG

[dangelor]

Possibly...

Please Login or Register  to view this content.