I have written a macro to open series of dbf files that are created by one programme (it only exports as a .dbf file), manipulate the data, join all the files together in one column and export all of the data to a txt file for another programme (that only imports .txt files).

It does the job but there are two things I would like to change if possible.
1. Once the dbf files have had the data copied from them they need to close.

When developing the procedure it was testing importing from excel and the following code closed the files without saving.

Dim wb As Workbook
Dim AWb As String
AWb = ActiveWorkbook.Name

For Each wb In Workbooks
If wb.Name <> AWb Then
wb.Close savechanges:=False
End If

Next wb

Is it possible to re-write this so it closes a dbf file without saving?

2. I use the following code to paste the data into the text file for the next programme to import.

Sub writetofile()

Dim theclipboard
theclipboard = CreateObject("htmlfile").ParentWindow.ClipboardDat a.GetData("text")

Open "c:\Corridor Analysis\001.txt" For Output As #1
Print #1, theclipboard
Close #1

Sheets("Sheet1").Select
Range("g1").Select
End Sub

I want each text file to have a separate file name. How can I either add a dialog box that allows me to create a file other than “001.txt” or allow me to save it to another name when closing.

Thanks
John

Point 1 - the Excel application object has a property DisplayAlerts. Set this to False before the line of code to close the file, and True just after. This will suppress the "Save changes?" prompt.

Point 2 - you'll need to use a loop and a counter:Good luck!

weejas

Thanks, setting the Display alert to false works perfectly so it will make it easier for other people to use the programme, which is why I am trying to make it work as seamlessly as possible.

I am sorry I did not word my other request properly and your answer does not quite do what I am trying to do. The purpose of the programme is to model the effect of changes to a region (There are 25 regions + sometimes just modelling small parts of the region) by comparing the current data to projected data.

Therefore I need to create txt files with meaningful names so I can look at them sometime in the future and know what region/sub region/new data/old data it refers to.

What I am trying to do is open a dialog box to enter something like Region3Old or Region3New and then get the macro to create that file. Because I cannot do that I just had it create 001.txt which I then have to rename later.

I am sure there is a way but when it comes to macros I struggle.

John

You're welcome!

If you have a list of names for the files that you will create, make a table of them on a spare worksheet and name it. Then you can use it to create a For Each loop instead:Or something like that, anyway.

weejas

Thank you again. I think I can use the latest code to do what I want.

As I will only ever export one file at a time i will set up a cell on the front page where people can enter the file name and run the import macro to refrence that. This is only the 2nd or 3rd time I have done anything more than just record a macro. It feels good.

John

Cool.

Well done, and good luck!

weejas

Thank you again. I will try to use the latest code to do what I want.

As I will only ever export one file at a time i will set up a cell on the front page where people can enter the file name and run the import macro to refrence that.

I have tried quickly and can not get it to work. I get a Compile error "User-defined-type not defined. It does not like Dim X as cell. I have created a range name and have used that in the .[rangename]. Do I also need to replace the [workbook]. with the actual name of the file too?

This is only the 2nd or 3rd time I have done anything more than just record a macro. It feels good.

John