[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 871
  • Last Modified:

Using Excel and NotePad, file SaveAs

The code below works fine but not sure how to tell give it a generic file name and type in NotePad?
filename needs a .sif suffix and the type needs to be All Files(*.*)
I do not want it to save it, because I need to edit the name a bit every time so it unique and the folder needs to be the same each time too.  Can Excel do this?

Shell "notepad.exe", vbNormalFocus
SendKeys "^V"
SendKeys "%FA"

Open in new window

0
RWayneH
Asked:
RWayneH
  • 10
  • 6
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
Couldn't you save this as a Unicode Text file from within Excel?  It sounds like this could be sourced from a single column of data since you're not using a CSV or tab-delimited type.

You could use VBA to create the filename and store the filepath for saving it.  And you can override the ".txt" extension as well.  See this example code:
Sub Save_SIF()
    Dim strFilePath, strFileName As String
    
    strFilePath = "C:\Misc - Excel Development\Excel Tips Tools Tricks\Expert Exchange Work\Test\"
    strFileName = "TestFile-" & Format(Now(), "yyyy-mm-dd") & ".sif"
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
        strFilePath & strFileName, FileFormat:=xlUnicodeText, CreateBackup:=False
    Application.DisplayAlerts = True
    MsgBox "Sif file saved"
End Sub

Open in new window


Let me know if this might meet your requirements and I can help tweak it for you.

Regards,
-Glenn
0
 
RWayneHAuthor Commented:
this will be used to save multi files, so I will need it to stop on a suggested filename.  I need to make the file name unique so I can reference the correct one or pull the proper file when using it late in the procedure.  Can we stop it so it does not do the Save? but leaves the user in that field, so all they have to do is a minor edit prior to saving?  Or perhaps an input box that would insert the edit, as a prefix?  Thanks, it looks like this is possible using excel
0
 
RWayneHAuthor Commented:
and where would I place the
Shell "notepad.exe", vbNormalFocus
SendKeys "^V"
SendKeys "%FA"

Open in new window

in the sub?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Glenn RayExcel VBA DeveloperCommented:
So, you would like to offer the user a pre-selected filename but allow them to edit the name before save?  That can be done.
0
 
RWayneHAuthor Commented:
This is correct, they could use the preselected name or put in whatever they like, as long as the format is .sif and it saves the file that way.  Perhaps an inputbox may be the best way to do this?
0
 
Glenn RayExcel VBA DeveloperCommented:
Yep; an input box is the way to go, like so:
Option Explicit
Sub Save_SIF()
    Dim strFilePath, strFileName As String
    
    strFilePath = "C:\filepath\" 'This is constant and stored in this subroutine
    
    strFileName = InputBox("Please enter a filename or click OK to accept the default.", "Save File", _
                "TestFile-" & Format(Now(), "yyyy-mm-dd")) '<--- change to new default
                
    If strFileName = "" Then Exit Sub 'Will happen if Cancel is pressed
    strFileName = strFileName & ".sif"
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
        strFilePath & strFileName, FileFormat:=xlUnicodeText, CreateBackup:=False
    Application.DisplayAlerts = True
    MsgBox "Sif file saved"
End Sub

Open in new window


You will need to change the filepath in line 5 and your default filename criteria in line 8.  My example will produce "TestFile-2014-08-12" (uses today's date).

And, obviously, since the file is being exported/saved via Excel, there's not need to Shell notepad.exe.

Regards,
-Glenn
0
 
RWayneHAuthor Commented:
Will this may chg the process a little, before I opened notepad and would save from there. (that is what I was going to try and automate)  What you are saying is that I really do not have to do that.  I provided a sample worksheet from a workbook.  Are you saying that I need to test the code on this sheet tab?  Is there a way to just save the sheet tab from a workbook as a single .sif file?  or is this going to save the whole workbook?  I pulled the sheet from a larger workbook.

Do I need to copy the sheet tab out, and then run the code?  The macro is in the workbook.  A little confused
SaveSIF-Sample.xlsx
0
 
RWayneHAuthor Commented:
I was trying to do a series of send.keys after copying it out to notepad with
Range(myRange.Address).Copy
Shell "notepad.exe", vbNormalFocus
SendKeys "^V"
SendKeys "%FA"
SendKeys "%T"

Open in new window

0
 
RWayneHAuthor Commented:
it is failing on xlUnicodeText     this needs to be saved in notepad... after it is copied into notepad from Excel.  This is the part that I was wondering if Excel could do this.
0
 
Glenn RayExcel VBA DeveloperCommented:
It's really, really simple.  It will only save the active sheet (the one visible).  I assumed from a previous question that users would select a range of data and copy it (to the clipboard, presumably to paste in Notepad).  Now, that won't be necessary; they would just select the sheet tab and then run this macro.

I did just realize, however, that you may actaully want to move this sheet to its own temporary workbook and save it as a SIF file so that the original workbook will not be affected.  So I've modified the code and have attached a sample workbook for you to test with you sample data.  Again, you need to change the default filepath in line 5 (currently C:\filepath\) and your default naming syntax.  I've got comments pointing to both in the code.

-Glenn
EE-SaveSIF.xlsm
0
 
RWayneHAuthor Commented:
ok understood and testing now.  First run through worked fine.  This is the first time that I have pulled an individual sheet tab out of a workbook and saved it as something else.   I am going to use that a lot more.  Thanks.
0
 
RWayneHAuthor Commented:
This does save a file, but when I upload these .sif files to an internal environment they are empty?  When comparing the data there is quotes around every third row of data.  I upload these .sif files to another environment that will not allow those quotes.  Is there anyway to accomplish the same thing, but instead of use this xlUnicodeText ??  It use the NotePad and save them there?  That seems to be the any format that will upload correctly to our internal environment.  Unless excel can save as a different format that will take those quotes out.

notice the quotes added
MC=HAW
QT=1
"PN=TARX-48E4-LFGMJG4C,H_3E,HP_3E,TR_E,H_3E,HP_3E"
MC=HAW
QT=1
"PN=TARX-48F0-LFGMJG4C,H_3E,HP_3E,TR_E,H_3E,HP_3E"
MC=HAW
QT=1
0
 
Glenn RayExcel VBA DeveloperCommented:
Sorry about that; I have a fix for you.  Just had to change the file type in the .SaveAs line to this:
    ActiveWorkbook.SaveAs Filename:= _
        strFilePath & strFileName, FileFormat:=xlTextPrinter, CreateBackup:=False

Open in new window


Corrected sample file attached.

-Glenn
EE-SaveSIF.xlsm
0
 
RWayneHAuthor Commented:
Thanks, testing this now and will let you know how it goes.
0
 
RWayneHAuthor Commented:
Worked Awesome, Excellent!!
0
 
Glenn RayExcel VBA DeveloperCommented:
I was glad I could help.

Regards,
-Glenn
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now