CODEWITHSUNDEEP
vbaexcelvisio
CH1350
CH1350

Reputation: 1

Paste Special VBA does not work in embedded excel workbook

I'm having an issue with the PasteSpecial function when working in an embedded excel workbook. The program I am working in is "Promax" which is block diagramming software running in Visio, which has the option to add an embedded excel workbook. I've essentially set up a number of cells in excel so that I can import a bunch of fields into a PDF form.

While working in the embedded workbook I am unable to get this function to give any output into the new excel worksheet. If I save a version of the workbook that is outside of promax, the code runs fine. If I just try to paste instead of paste special the code works fine, but all of the references that I pasted break in the new workbook.

Sub ExporttotxtFile()
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Sheets("Sheet1").Range("A1:HK2")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
wb.Worksheets(1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
saveFile = Application.GetSaveAsFilename(InitialFileName:="Export", fileFilter:="Text Files (*.txt), *.txt")
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Does anyone have a solution to this or another method of getting this done? This code was taken from this: https://www.extendoffice.com/documents/excel/612-excel-export-data-to-text.html

Thanks!

Upvotes: 0

Views: 232

Answers (1)

CH1350
CH1350

Reputation: 1

While playing around with this more it seemed like xlPasteFormat was pasting an image over the values rather than formatting. I scrapped the pastespecial method and used a different solution.

    wb.Worksheets(1).Paste
    wb.Worksheets(1).Range("A1:HK2").Value = WorkRng.Value

I paste everything with the first paste and set the values with the second line so that the formatting stays.

Thanks for the help everyone.

Upvotes: 0

Related Questions