Results 1 to 10 of 10
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: take csv file and load into seperate structure wor (Excel 2000)

    Can you post the CSV file itself?

    What do you want the workbook to look like after the CSV is manipulted by the macro?

    Steve

  2. #2
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: take csv file and load into seperate structure wor (Excel 2000)

    Hi Steve,

    itried posting the csv file and it seems that it cant be load into the post.

    I would like the workbook to have the information from the csv file to be transferred into the appropitate fields that are defined on in the A col of the CSV file

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

    Re: take csv file and load into seperate structure wor (Excel 2000)

    Change the extension to .txt and upload that.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    take csv file and load into seperate structure wor (Excel 2000)

    Hi,
    I am trying to make a csv file load into a workbook that I have. The workbook I want to load it into is structured in the way that it has name ranges where I want to put the csv files data

    the csv file has two columns Col A is The named ranges and Col. Bholds the data that goes into the named range to the left.

    is there some sort of simple macro some can help me that can read the csv file in a manner where it gets the named range and pastes the value in Col b into the correct name range in the structure workbook and loops to the next named range and pastes the data??
    ?

    attached is my .xls csv file

    i will attach a worbook that I am trying to load the data into.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: take csv file and load into seperate structure wor (Excel 2000)

    i attached the .txt replacing the attachment in my first post

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: take csv file and load into seperate structure wor (Excel 2000)

    here s is an example of a simple sturcture workbook that Iam using. The real on is alot more complex though in structure and that is why it be nice to populate it with the CSV file

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: take csv file and load into seperate structure wor (Excel 2000)

    here is some code that gets me to a certain point.

    What I m not sure how to do is once " i " gets to 13 the data to be move is in an array of (11 rows by 1 column).
    is there an array function that I can add which would control the data file information moving to the structured workbook by saying from b12:b23 data to PreSandSched1 (ex.) in one chuck or is the paste method be easier and efficent?

    attached is a working workbook of the structure workbook, code is in the module. I have the workbook populated from the data file in my first post

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: take csv file and load into seperate structure wor (Excel 2000)

    Is this what you are after? I added routine to make sure all the names used were valid. the code will run faster without out, but it will prevent errors...

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>The "CSV file" you attached is not a CSV, there are no commas in it. I tested after I converted from tab delimited to CSV.

    Steve
    <pre>Option Explicit
    Sub CSV()
    Dim wCSV As Workbook
    Dim wks As Worksheet
    Dim sCSV As String
    Dim rng As Range
    Dim rngA As Range
    Dim rCell As Range
    Dim sNoRange As String
    Dim n As Name
    Dim rStart As Range
    'DISABLE SCREENUPDATING
    Application.ScreenUpdating = False

    'ASK USER WHERE CSV FILE IS
    Set wks = ThisWorkbook.Sheets("INPUT")
    Set rStart = Selection
    sCSV = Application.GetOpenFilename _
    (FileFilter:=".csv Files (*.csv),*.csv", _
    Title:="Select .CSV file To Open")
    If sCSV = "False" Then
    MsgBox "Canceled by user"
    GoTo ExitRoutine
    End If

    'OPEN THE SELECTED CSV FILE
    Set wCSV = Workbooks.Open(sCSV)

    'Check for invalid range names
    With wCSV.Sheets(1)
    Set rngA = .Range(.Range("A2"), _
    .Range("A65536").End(xlUp)). _
    SpecialCells(xlCellTypeConstants)
    End With
    sNoRange = ""
    For Each rCell In rngA
    Set n = Nothing
    On Error Resume Next
    Set n = ThisWorkbook.Names(rCell.Value)
    On Error GoTo 0
    If n Is Nothing Then
    sNoRange = sNoRange & vbCrLf & rCell.Value
    End If
    Next
    If sNoRange <> "" Then
    MsgBox "The following names were not found:" _
    & sNoRange & vbCrLf & "Please correct the CSV file"
    GoTo ExitRoutine
    End If
    'Add and end to the name ranges
    wCSV.Sheets(1).Range("B65536").End(xlUp).Offset(1, -1) = "End"
    'copy each named range to Input
    For Each rCell In rngA
    If rCell.Offset(1, 0) = "" Then
    With wCSV.Sheets(1)
    Set rng = .Range(rCell, _
    rCell.End(xlDown).Offset(-1, 0)) _
    .Offset(0, 1)
    End With
    Else
    Set rng = rCell.Offset(0, 1)
    End If
    rng.Copy
    wks.Range(rCell.Value).PasteSpecial (xlPasteValues)
    Next
    ExitRoutine:
    Application.CutCopyMode = False
    wCSV.Close (False)
    rStart.Activate
    Application.ScreenUpdating = True
    Set n = Nothing
    Set rng = Nothing
    Set rngA = Nothing
    Set rCell = Nothing
    Set wCSV = Nothing
    End Sub</pre>


  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: take csv file and load into seperate structure wor (Excel 2000)

    Wow, Steve I like, the error checking is great addition. Thank you very much

    one question though,
    some of the data is blank is there a way to not insert 0 values?

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: take csv file and load into seperate structure wor (Excel 2000)

    The "problem" is that there are zeroes in the CSV file. Can you prevent the zeroes from getting into the CSV file? If the CSV does not have zeroes, then they will not be copied into the sheet. Some of the ranges now, have zeroes and blanks...

    If you want to clear the cells with zero in the code, you can add this code (red lines are new)
    <pre>'OPEN THE SELECTED CSV FILE
    Set wCSV = Workbooks.Open(sCSV)
    <font color=red> With wCSV.Sheets(1)
    Set rngA = .Range(.Range("B2"), _
    .Range("B65536").End(xlUp)). _
    SpecialCells(xlCellTypeConstants, xlNumbers)
    End With
    For Each rCell In rngA
    If rCell = 0 Then
    rCell.ClearContents
    End If
    Next</font color=red>
    'Check for invalid range names</pre>


    Steve

Posting Permissions

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