Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 4th 20, 06:36 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 37
Default File Extension

Hi Team

This is more an annoyance than an actual life-or-death problem.

The below code is not the all I use in this instance but, I can tell you everything works as expected, with one small caveat:

When the sheet is attached to the email, ( and this happens with every file I use this code in ) it saves it with two extensions:

example: "myFileName.xlsm.xlsx"

The workbook(s) this is generated from is/are always [.xlsm], but the newly created sheet that attaches to emails is always a [.xlsx].

Any thoughts on how I can restructure the below code to stop this from happening please:

As always
TIA
Mark.


Dim TempFilePath As String, TempFileName As String, FileExtStr As String
Set Sourcewb = ActiveWorkbook

With Destwb
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End With

TempFilePath = "T:\National\Incident Register\"
TempFileName = Sourcewb.Name

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

On Error Resume Next
With OutMail
.To = toCell
.CC = ccCell
.BCC = ""
.Subject = "Incident Report(s) yet to be Closed"
.Attachments.Add Destwb.FullName
.HTMLBody = strBody & "<br" & RangetoHTML(rng) & "<br" & Signature
.display 'or use .Display
End With
On Error GoTo 0

.Close savechanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

  #2   Report Post  
Old November 4th 20, 07:26 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 37
Default File Extension

Hi All

I worked it out.

I simply changed: TempFileName = Sourcewb.Name to TempFileName = "myFileName"

Annoyance sorted.

Cheers
Mark.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Code execution is correct but generates Error :The file you aretrying to open is in a different format than specified by the file extension. JeanPierre Charron Excel Programming 3 February 19th 14 04:16 PM
Determine Excel file version with no file extension. tjlumpkin Excel Discussion (Misc queries) 2 July 23rd 09 06:59 AM
Retrieving Excel File extension based on XL File Format Enumeratio Sasikumar Kannappan Excel Programming 2 June 24th 09 03:59 PM
file format or file extension is not valid...error message Ballun Excel Discussion (Misc queries) 0 May 7th 09 09:06 PM
Additional file with no extension created during File Save As proc Peter Rooney Excel Discussion (Misc queries) 2 August 11th 05 02:48 PM


All times are GMT +1. The time now is 09:27 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017