excel VBA save as question

Faolchu

I have a macro in Excel that opens a file, does some work on it and converts it to a txt file. basically the user clicks a button, it asks them to select the source file and then the save destination for the txt file. I want to get rid of the steps to select a save location, i want to use teh same location all the time and overwrite teh text file.


If MsgBox("Do you want to save the output file?", vbYesNo, "Save CSV file") = vbYes Then

fname = Application.GetSaveAsFilename( _
InitialFileName:="b:\Share\\Text_Files\file.txt", _
FileFilter:="Text Files, *.txt", _
Title:="Save file as txt")

If Len(fname) <> 0 Then
csvbook.SaveAs fname, FileFormat:=xlCSV
csvbook.Close False
frombook.Close

End If

the above will open the save as dialog box, give the file a default name of file.txt in the path i require. I want to basically suppress this dialog box so that teh file name is always the same and in the same path every time, automatically overwriting each time its run. anyone any ideas?

FSL

If you do not want to give the user the option of choosing the name and location then you don't need a file save dialog box. The destination name and location is the same as the initial name and location.

Unless I am completely misunderstanding your question I can not see what your problem is.

srsly78

Use export instead of save.

Faolchu

FSL wrote: »

If you do not want to give the user the option of choosing the name and location then you don't need a file save dialog box. The destination name and location is the same as the initial name and location.

.

the destination file name & path will be different to the source name each time.

basically its we have an excel sheet for each month of teh year so you have a folder for Month, then a sub folder for year, then teh excel file. so each month the source location changes. We then have a seperate application that needs to import some of the data in the excel sheet.

So the easiest solution would be to create a macro that copies teh required info into a seperate file (in this case a csv file) that has the data in the right order and then import that data into our database. thats why the destination name & path stays static so I dont have to modify a SQL script each month before the users carry out their task.

so each month the users will select a different source spreadsheet, but the file created will always be in teh same location. So they need to be able to select the source each time but not the destination.


the full code is below

Private Sub CommandButton1_Click()
' execute import

Dim frombook As Workbook, csvbook As Workbook
Dim fromfilename As String
fromfilename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")


' Set frombook = Workbooks.Open(thissheet.Cells(2, 2).Value & thissheet.Cells(3, 2).Value, , True)

If Len(fromfilename) <> 0 Then ' fromfilename is a nul string if canceled or no file selected

Set frombook = Workbooks.Open(fromfilename, , True)
Set csvbook = Workbooks.Add ' create the ouput file

Call Make _csv(frombook, csvbook)

If MsgBox("Do you want to save the output file?", vbYesNo, "Save CSV file") = vbYes Then

fname = Application.GetSaveAsFilename( _
InitialFileName:="b:\Share\ Text_Files\file.txt", _
FileFilter:="Text Files, *.txt", _
Title:="Save file as txt")


If Len(fname) <> 0 Then
csvbook.SaveAs fname, FileFormat:=xlCSV
csvbook.Close True
frombook.Close

End If
Else
MsgBox "The files remain open - if saving the output file later make sure to name the file with an extesion of txt.", vbOKOnly
End If ' msgbox


End If 'fromfilename <> 0

End Sub

Galtee

Faolchu wrote: »

the destination file name & path will be different to the source name each time.

basically its we have an excel sheet for each month of teh year so you have a folder for Month, then a sub folder for year, then teh excel file. so each month the source location changes. We then have a seperate application that needs to import some of the data in the excel sheet.

So the easiest solution would be to create a macro that copies teh required info into a seperate file (in this case a csv file) that has the data in the right order and then import that data into our database. thats why the destination name & path stays static so I dont have to modify a SQL script each month before the users carry out their task.

so each month the users will select a different source spreadsheet, but the file created will always be in teh same location. So they need to be able to select the source each time but not the destination.


the full code is below

Private Sub CommandButton1_Click()
' execute import

Dim frombook As Workbook, csvbook As Workbook
Dim fromfilename As String
fromfilename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")


' Set frombook = Workbooks.Open(thissheet.Cells(2, 2).Value & thissheet.Cells(3, 2).Value, , True)

If Len(fromfilename) <> 0 Then ' fromfilename is a nul string if canceled or no file selected

Set frombook = Workbooks.Open(fromfilename, , True)
Set csvbook = Workbooks.Add ' create the ouput file

Call Make _csv(frombook, csvbook)

If MsgBox("Do you want to save the output file?", vbYesNo, "Save CSV file") = vbYes Then

fname = Application.GetSaveAsFilename( _
InitialFileName:="b:\Share\ Text_Files\file.txt", _
FileFilter:="Text Files, *.txt", _
Title:="Save file as txt")


If Len(fname) <> 0 Then
csvbook.SaveAs fname, FileFormat:=xlCSV
csvbook.Close True
frombook.Close

End If
Else
MsgBox "The files remain open - if saving the output file later make sure to name the file with an extesion of txt.", vbOKOnly
End If ' msgbox


End If 'fromfilename <> 0

End Sub

So you want to remove the

fname = Application.GetSaveAsFilename( _
InitialFileName:="b:\Share\ Text_Files\file.txt", _
FileFilter:="Text Files, *.txt", _
Title:="Save file as txt")

section altogether? and replace it with

fname = "b:\Share\Text_Files\yourFileName.txt"?

Faolchu

Galtee wrote: »

So you want to remove the

fname = Application.GetSaveAsFilename( _
InitialFileName:="b:\Share\ Text_Files\file.txt", _
FileFilter:="Text Files, *.txt", _
Title:="Save file as txt")

section altogether? and replace it with

fname = "b:\Share\Text_Files\yourFileName.txt"?

spot on that worked a treat thanks.