Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Hereford, Herefordshire, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export to CSV file (2000 / 2003)

    I am using a spreadsheet with a couple of macro's to automate a time consuming task - the spreadsheet opens a csv file copies the contents into one sheet, rearranges that information (using a combination of If statements) and then exports it to another csv file.
    I do not know the length (in rows) of the file to be manipulated and I have provided for 500 lines just to be safe. The manipulated file is then imported into an accounting application and here is the problem.
    The accounting application accepts all the rows that contain data, and also all of the rows (up to row 500) that are blank. I knew that this would happen if I set the results of my if statement to be zero, so I have set each cell of each row with an if statement to return "" if it is blank. The accounting application is interpreting these cells as not being empty and trying to import them. I am using the "quick and dirty method" in my macro of selecting the whole sheet, then paste special values into a new workbook and saving that as a csv file - which is not very elegant.
    How can I avoid this problem - I have looked at posts which look at locating the last non-blank cell - or is there a better way of exporting which will avoid the whole problem?

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

    Re: Export to CSV file (2000 / 2003)

    If you are sure that there are no completely blank rows or columns within the "used" range of the worksheet, you can use

    Range("A1").CurrentRegion

    to get the complete rectangular region of used cells that contains A1. If you have a column that is guaranteed to be populated, say column C for example, you can use

    Range("C65536").End(xlUp).Row

    to retrieve the row number of the last populated row. Does this help you to limit the macro to processing only used rows? If not, tell us what more you need to know.

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Hereford, Herefordshire, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to CSV file (2000 / 2003)

    Hans,
    Thanks for the answer.
    I am attaching the csv file output by the sheet at the moment (as an xls)
    The range I want is A1:I158but column B must be empty in order for the import into the accounting package to work.
    One more point has come to light - I use 2000 and my client uses 2003. The result of =ISBLANK(A159) is TRUE on my spreadsheet but FALSE on my clients!
    Hope this helps

    Chris

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

    Re: Export to CSV file (2000 / 2003)

    What exactly is your question?

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

    Re: Export to CSV file (2000 / 2003)

    There is no VBA code in the workbook, so it is a little difficult to see what you are trying to do. Does this get you pointed in the right direction:

    <pre>Public Sub GetRange()
    Dim oRng As Range
    With Worksheets("Salary Journal")
    Set oRng = Range("A1", Range(.Range("A65536").End(xlUp), .Range("IV1").End(xlToLeft)))
    MsgBox "The last range is " & oRng.Address
    End With
    End Sub
    </pre>

    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Feb 2004
    Location
    Hereford, Herefordshire, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to CSV file (2000 / 2003)

    Thank you Legare and Hans,
    I'm not explaining myself very well!

    The problem is that the accounting application is seeing content in rows 159 to 500 and tries to import them. Excel 2000 reports that all of cells A159 to I500 are blank, Excel 2003 reports that they not blank and they contain text and the accounting application reports that they are not blank. I will never know the number of rows that will be needed (and so provided for up to 500) but I need the "unused cells" to be blank as the import procedure of the accounting application is very unforgiving.
    All my code says is this
    Cells.Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    So I either want to select the sells that aren't blank (ie A1:I158) or export the whole range (ie $A$1 to $I$500) to csv in such a way that if a cell has an if statement which results in "", that cell is exported as a blank cell

    Feel free to tell me I am being dense - I certainly feel it!
    Chris

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

    Re: Export to CSV file (2000 / 2003)

    How are you exporting the data? If I open your file and then do a File|Save As to a .csv file, and then open that file in WordPad, I do not see any blank lines at the end of the file.
    Legare Coleman

  8. #8
    New Lounger
    Join Date
    Feb 2004
    Location
    Hereford, Herefordshire, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to CSV file (2000 / 2003)

    I found it - and its Hans I have to thank! As I was using a sledgehammer to crack a nut I was exporting the information by selecting every cell on the sheet, opening a new workbook, pasting values only and saving the new work book as a csv file. Because of the IF statements some of the cells contained an empty string. By using Hans ClearEntries Sub which I found after a bit of digging I've now cleared them and it works like a dream.

    Thank you for your help - but one more unrelated question - My VBA is made up of half remembered bits, recording macro's and looking at the code and using help. I need to learn it properly - what approach/books/other materials would you recommend?

    Chris

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

    Re: Export to CSV file (2000 / 2003)

    See the thread starting at <post#=364221>post 364221</post#> for books on Excel VBA. I like those by John Walkenbach.

  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: Export to CSV file (2000 / 2003)

    Also check the links in <post#=320321>post 320321</post#> for some online info.

    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
  •