Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    11

    Unanswered: Tweeks to improve macro

    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

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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:
    Code:
    intCount = 1
    Do Until [test that no more files need to be created]
       Open "C:\Corridor Analysis\" & right("000" & intCount, 3) &  ".txt" For Output As #1
       Print #1, theclipboard
       Close #1
       intCount = intCount + 1
    Loop
    Good luck!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Sep 2009
    Posts
    11

    Thanks

    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

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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:
    Code:
    Dim rngFiles As Range
    Dim x As Cell
    
    set rngFiles = [Workbook].[RangeName]
    For Each x in Cell
       Open "C:\Corridor Analysis\" & x.Value &  ".txt" For Output As #1
       Print #1, theclipboard
       Close #1
    Next x
    Or something like that, anyway.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Sep 2009
    Posts
    11
    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

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Cool.

    Well done, and good luck!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Sep 2009
    Posts
    11
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •