Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    XL data into a text file

    I have a text file containing data for four years(1997-1999, see attachment). How can I use VBA(preferably but I also own VB 6.0) to append to the file data for 2000 from an Excel range(ImportRange, see attachment) which is part of an XL file produced monthly? I don't know how to use the "Open filename for Append etc." statement when an XL range is involved.
    Attached Files Attached Files

  2. #2
    calacuccia
    Guest

    Re: XL data into a text file

    Hi grovelli,

    Try this procedure, but chew it to your needs, where needed.

    Sub ExportToTextFile()
    Dim startDate As Date
    Dim sDay As Integer, i As Integer
    Dim mMonth As Integer, mYear As Integer
    Dim addString As String
    monthArray = Array("GEN", "FEB", "MAR", "APR", "MAG", "GIU", "LUG", "AGO", "SET", "OTT", "NOV", "DIC")
    mMonth = 1
    mString = monthArray(mMonth - 1)
    mYear = 0
    startDate = CDate("01/" & CStr(mMonth) & "/" & CStr(mYear))
    'Determine serial number of 1st day of month in year
    If mMonth = 1 Then
    sDay = 1
    Else
    For i = 2 To mMonth
    sDay = sDay + Day(CDate("01/" & CStr(i) & "/" & CStr(mYear)) - 1)
    Next i
    End If
    Open "C:GeertOudDEE_Temporary_Trialstest.dat" For Append As #1
    For i = 1 To Range("ImportRange").Rows.Count
    If Len(CStr(i)) = 1 Then
    addString = " " & i
    Else
    addString = CStr(i)
    End If
    Print #1, Format(i + sDay - 1, "000"); Spc(2); Format(mYear, "00"); Spc(1); mString; Spc(1); addString; Spc(8); Format(Range("ImportRange").Cells(i, 1), "00.0")
    Next i
    Close #1
    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    As far as I'm concerned, you're great!

    How come at <A target="_blank" HREF=http://www.experts-exchange.com/jsp/memberProfile.jsp?mbr=sipea>http://www.experts-exchange.com/jsp/member...e.jsp?mbr=sipea</A>
    Sipea says you are less important than you think?
    What's the purpose of setting the StartDate variable since you don't use it in the sub?
    Your Sub runs fine if I put it in a module within the XL file that provides data to the text file; how do I specify the file containing the range so that I don't have to put your sub in every XL file that feeds data to the text file, I mean, I can't use something similar to
    For i = 1 To Range("C:ImportJanuary.XLS.ImportRange").Rows.Coun t,
    can I?(Wishful thinking <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>)

  4. #4
    calacuccia
    Guest

    Re: As far as I'm concerned, you're great!

    Grovelli,

    how did you find the sipea profile? The reason why is a long story ;-) Just look at the single question sipea asked, and you should find out why.

    The startDate variable makes no sense, I started with that variable and replaced it by the sDay variable afterwards, and forgot to take the startDate variable out afterwards.

    You can of course put the macro in another file, your personal.xls file for example. There are then several ways to refer to the to be processed workbook and worksheet. I would use one of both below:

    1/ Instead of <font color=ff6666>For i = 1 To Range("ImportRange").Rows.Count</font color=ff6666> use

    Dim myFile As String
    Dim myBook As Workbook
    Dim myRange As Range
    '....existing code
    myFile = Application.GetOpenFilename
    Set myBook = Workbooks.Open(myFile)
    Set myRange = myBook.Worksheets("I").Range("ImportRange")
    For i = 1 To myRange.Rows.Count
    '.... existing code
    Print #1, Format(i + sDay - 1, "000"); Spc(2); Format(mYear, "00"); Spc(1); mString; Spc(1); addString; Spc(8); Format(myRange.Cells(i, 1), "00.0")
    '....existing code
    myBook.Close SaveChanges:=False
    Set myRange = Nothing
    Set myBook = Nothing
    End Sub


    2/ Instead of <font color=ff6666>For i = 1 To Range("ImportRange").Rows.Count</font color=ff6666> use

    Dim myBook As Workbook
    Dim myRange As Range
    '....existing code
    Set myBook = Workbooks.Open("C:ImportJanuary.XLS")
    Set myRange = myBook.Worksheets("I").Range("ImportRange")
    For i = 1 To myRange.Rows.Count
    '.... existing code
    Print #1, Format(i + sDay - 1, "000"); Spc(2); Format(mYear, "00"); Spc(1); mString; Spc(1); addString; Spc(8); Format(myRange.Cells(i, 1), "00.0")
    '....existing code
    myBook.Close SaveChanges:=False
    Set myRange = Nothing
    Set myBook = Nothing
    End Sub

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GetOpenFilename is a doozy!

    That really made my day, thanks. It works like a charm (need any additional kudos? <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>).
    Does it matter to you if anybody knows how I found those slanderous remarks <img src=/S/argue.gif border=0 alt=argue width=50 height=25> or do you prefer me to send you a private message?
    O.k., now the Sub looks like this:
    Sub ExportToTextFile()
    Dim startDate As Date
    Dim sDay As Integer, i As Integer
    Dim mMonth As Integer, mYear As Integer
    Dim addString As String
    Dim monthArray, mString As String
    Dim myFile As String
    Dim myBook As Workbook
    Dim myRange As Range
    monthArray = Array("GEN", "FEB", "MAR", "APR", "MAG", "GIU", "LUG", "AGO", "SET", "OTT", "NOV", "DIC")
    mMonth = 1
    mString = monthArray(mMonth - 1)
    mYear = 0
    startDate = CDate("01/" & CStr(mMonth) & "/" & CStr(mYear))
    'Determine serial number of 1st day of month in year
    If mMonth = 1 Then
    sDay = 1
    Else
    For i = 2 To mMonth
    sDay = sDay + Day(CDate("01/" & CStr(i) & "/" & CStr(mYear)) - 1)
    Next i
    End If
    Open "C:GESSO9699x.DAT" For Append As #1
    myFile = Application.GetOpenFilename("Microsoft Excel Files (*.xls), *.xls")
    Set myBook = Workbooks.Open(myFile)
    Set myRange = myBook.Worksheets("I").Range("ImportRange")
    For i = 1 To myRange.Rows.Count
    If Len(CStr(i)) = 1 Then
    addString = " " & i
    Else
    addString = CStr(i)
    End If
    Print #1, Format(i + sDay - 1, "000"); Spc(2); Format(mYear, "00"); Spc(1); mString; Spc(1); addString; Spc(8); Format(myRange.Cells(i, 1), "#0.0")
    Next i
    Close #1
    myBook.Close SaveChanges:=False
    Set myRange = Nothing
    Set myBook = Nothing
    End Sub

    Suppose I find that, e.g., March 1999 in the text file contains incorrect data. If I want to use your Sub to substitute (instead of appending) March 1999 data in the text file with the data in the ImportRange range of the relevant(March 1999) XL file I just have to change the line
    Open "C:GESSO9699x.DAT" For Append As #1 into
    Open "C:GESSO9699x.DAT" For Output As #1
    but how can I position the text file pointer so that the substitution starts at the 1st March 1999 line?

Posting Permissions

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