Excel Sheet with Two Buttons to save certain cells as TXT to specific location

1
votes

I have a Excel Spreedsheet that has data in Cells A2 that I need to be able to click on the button and it exports that data to a text file in a network drive as a certain name (no extension).

I also then need another button on the same sheet to save cells E2 and F2 to the same network location as a separate file name (no extension).

I have the below code, but it copies ALL the data on the sheet to the file name when each button would need to only save the data from specific cells. I'm guessing this is a RANGE option that I just cannot figure out. 

Private Sub CommandButton1_Click()
Dim path As String
Dim filename1 As String

path = "S:\"
filename1 = "scal0091"
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=path & filename1 & ".", FileFormat:=xlText
End Sub

Any suggestions?

Thank you!

1
excelvba
first copy data from A2 to another sheet and save txt file. - Techie

1 Answers

0
votes

This should do:

Private Sub CommandButton1_Click()
Dim path As String
Dim filename1 As String

'New Variables:
Dim MyVal as Variant
Dim FileNum as Integer
Dim FSO as Object
Dim MySheet as Sheet


path = "S:\"
filename1 = "scal0091"
Set MySheet = Thisworkbook.Sheets("MySheetName") 'Put Your Sheet Name Here
MyVal = MySheet.range("A2").value 'Your specific cell value
Application.DisplayAlerts = False 'why?

'Create a new text file:
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CreateTextFile path & filename1, False

'Write to Text File
FileNum = FreeFile()
Open path & filename1 for output as #FileNum
    Print #FileNum, MyVal
Close #FileNum

Set FSO = Nothing

End Sub