Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Csv file macro (2003)

    I have the following macro that brings a csv file into Excel. My problem is that it doesn't make room on each sheet for a heading row. I am bringing in a csv file that has 17 sheets so I have to make room on each sheet for a heading because ultimately I have to bring this file into Access. I have to work backwards copying a few rows to the next sheet and then inserting a row at the top for a heading. Is there anyway in the following macro to add a blank row at the top for each sheet?

    Sub ImportMultSheets()
    Dim wks As Worksheet
    Dim sPathFilename As String
    Dim lRow As Long
    Dim lLimit As Long
    Dim sLine As String
    Dim sArray() As String

    sPathFilename = "N:AdminLindaCSV FileMain.csv"
    lLimit = 65536
    ReDim sArray(1 To lLimit, 0)
    lRow = 1
    Open sPathFilename For Input As #1
    Do While Not EOF(1)
    Line Input #1, sLine
    sArray(lRow, 0) = sLine
    lRow = lRow + 1
    If lRow = lLimit + 1 Then
    Set wks = Worksheets.Add
    With wks
    .Range("A1").Resize(lLimit, 1).Value = sArray
    .Columns("a:a").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True
    End With
    ReDim sArray(1 To lLimit, 0)
    lRow = 1
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Csv file macro (2003)

    Does this do what you want?

    Sub ImportMultSheets()
    Dim wks As Worksheet
    Dim sPathFilename As String
    Dim lRow As Long
    Dim lLimit As Long
    Dim sLine As String
    Dim sArray() As String

    sPathFilename = "N:AdminLindaCSV FileMain.csv"
    lLimit = 65536
    ReDim sArray(2 To lLimit, 0)
    lRow = 2
    Open sPathFilename For Input As #1
    Do While Not EOF(1)
    Line Input #1, sLine
    sArray(lRow, 0) = sLine
    lRow = lRow + 1
    If lRow = lLimit + 1 Then
    Set wks = Worksheets.Add
    With wks
    .Range("A2").Resize(lLimit-1, 1).Value = sArray
    .Columns("a:a").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True
    End With
    ReDim sArray(2 To lLimit, 0)
    lRow = 2
    End If

    (Changes in bold)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Csv file macro (2003)

    The cvs file has a blank row at top but when it converts from comma delimited to the final format - that doesn't have a blank row at top which is ultimately what I need to have a blank row at top. I looked at the bottom row (65536) and it is putting all the columns in the first column...?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Csv file macro (2003)

    Oops, sorry, forgot one modification. Change

    Destination:=Range("A1"), _

    to

    Destination:=Range("A2"), _

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Csv file macro (2003)

    I forgot to add the end part of the macro.

    Loop
    Set wks = Worksheets.Add
    With wks
    .Range("A1").Resize(lLimit, 1).Value = sArray
    .Columns("a:a").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True
    End With
    Close #1
    Set wks = Nothing
    End Sub

    Should I make any changes to this part of the macro?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Csv file macro (2003)

    Yes, see my previous reply.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Csv file macro (2003)

    The macro works. Now my problem is I get the following message after I bring in 13 sheets. "Excel cannot complete task with available resources. Choose less data or close other applications". I closed all applications and rebooted and still get that error after a certain amount of worksheets come in. Is this due to a memory error and can I get my memory increased and if so how much memory would be enough? I also have to bring all these sheets into Access - would I get not enough resources trying to do that too? Thanks for your help.....

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Csv file macro (2003)

    Do you have a specific reason for importing into Access via Excel? Access can handle much larger tables than Excel (no 65,536 row limit)

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Csv file macro (2003)

    If I import the csv file directly into Access, I have problems with the date not importing. I was bringing it into Excel first so I could change the date format (hence my other post about the formatchange macro). Also there are some text fields that have leading 0s and the csv file brings those in as numbers and drops the leading 0. I was going to fix all this in Excel and then import into Access - unless you know a better way - I am hoping......

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Csv file macro (2003)

    You'd probably have to import in batches of 10 sheets at a time or so, then quit and restart Excel before continuing.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Csv file macro (2003)

    But I am using that macro to bring the csv file into Excel. How do I stop after 10 sheets. The reason I am using the macro to bring the csv file in, is because when I open the csv file directly into Excel, it stops at the first sheet and says it didn't bring in all the data. This is becoming a nightmare. Is there somewhere in the macro I can tell it to bring in the first 10 sheets and then run the macro again and start at sheet 11?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Csv file macro (2003)

    Then I'd import directly into Access, then use an update query or VBA code to massage the data into the shape you want.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Csv file macro (2003)

    How can I massage the date if it isn't there? In Access it gives me an error table and the main table doesn't have the date field.

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Csv file macro (2003)

    You could import the date into a text field in Access, then use an update query to convert the text into a real date.
    Legare Coleman

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Csv file macro (2003)

    Another option would be to create code in Access to read the csv file line by line, split it into parts, edit the parts as needed, then add a record to the table and set the fields.

Posting Permissions

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