0

I need to save the txt file, which is embedded as an object in the Excel workbook.

I had a look at Saving embedded OLE Object (Excel workbook) to file in Excel 2010.

In the workbook, I have an embedded text file and pdf files (for test purposes, but ultimately I need to use txt file). When I point the OLEObject to xlsm and update the fName accordingly, the below command works correctly.

But when I point the OLEObject to txt file and change the extension in fName to txt, it fails with

Run-time error 1004.

What is the command for txt files? I tried SaveAs2 as per the other threads. 

Dim uName As String
Dim fName As String
Dim oEmbFile As Object

uName = Left(Environ("AppData"), Len(Environ("AppData")) - 16)
fName = uName & "\Desktop\OTPReport_Vin" & ".xlsm"

Set oEmbFile = wbkCurrent.Worksheets("Compare the changes").OLEObjects("Object 1")
oEmbFile.Object.SaveAs fName
Community
  • 1
  • 1
Vin
  • 5
  • 4
  • Unrelated to your issue: `fName = Environ("USERPROFILE") & "\Desktop\OTPReport_Vin" & ".xlsm"` would be a little more succinct then all that string manipulation to derive `uname` – JNevill Jan 02 '20 at 20:02
  • I would add `fName = CreateObject("WScript.Shell").SpecialFolders.Item("Desktop") & "\OTPReport_Vin.xlsm"` – omegastripes Jan 02 '20 at 20:05
  • @omegastripes: This made no difference as the debug watch window shows the same value for my code and yours. – Vin Jan 03 '20 at 15:31
  • Bumping the thread to get some attention. Further the same code is tested for csv, xls and xlsm values. As mentioned, the OLEObject and file extention was updated for each trial. – Vin Jan 07 '20 at 19:57

1 Answers1

0

You need to specify the format, not just change the extension.

Here's the list of different ones (though I don't think it is fully complete): https://learn.microsoft.com/en-us/office/vba/api/excel.xlfileformat

You would want xlTextWindows so your code would be:

oEmbFile.Object.SaveAs fName, xlTextWindows
Warcupine
  • 4,460
  • 3
  • 15
  • 24
  • This did not help. It resulted in application 1004 again. `oEmbFile.Object.SaveAs fName, FileFormat:=xlTextWindows ` – Vin Jan 03 '20 at 15:29
  • All the txt file formats are tried out. everything resulted in 1004 error. – Vin Jan 07 '20 at 20:01