CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2006
    Location
    Hubli, India
    Posts
    70

    Exclamation Export to CSV or Excel Multiple Sheets

    Hello Code Gurus

    This is the Code which i wrote to export records to CSV file Format, Which then eventually opens in Excel.

    [CODE]
    If F.FileExists("C:\file1.csv") = True Then
    F.DeleteFile "C:\file1.csv", True
    End If
    Set F1 = F.OpenTextFile("C:\file1.csv", ForWriting, True, TristateFalse)
    F1.WriteLine "Delivered Bills "
    F1.WriteLine "Date,Bill No,Delivered To,Relation,Contact No,Delivery Time"
    F1.WriteLine
    F1.WriteLine RSTemp.GetString(adClipString, , ",")
    F1.Close
    Set F1 = Nothing

    MsgBox "File Exported to C:\file1.csv", vbInformation, "Info"
    [CODE]

    But when I open it with Excel, It opens in a single Sheet and has limits 65536 rows.

    My Question is how to go about exporting the CSV file to open in Multiple Sheets .
    I want to do it without using Excel Object, (beacuse if Ecel is not found in the Client Machine !)

    Any Help , suggestions !!!!! Please

  2. #2
    Join Date
    Aug 2006
    Location
    Hubli, India
    Posts
    70

    Please

    Hey Guys Please Somebody Help me....

  3. #3
    Join Date
    Dec 2006
    Location
    Pune, India.
    Posts
    579

    Re: Export to CSV or Excel Multiple Sheets

    Quote Originally Posted by RajWolf
    I want to do it without using Excel Object, (beacuse if Ecel is not found in the Client Machine !)
    I think you have to use excel object. If excel is not there on client machine then how will you open your .csv file on multiple sheets in excel??

    Tricky!!!

  4. #4
    Join Date
    Aug 2006
    Location
    Hubli, India
    Posts
    70

    Re: Export to CSV or Excel Multiple Sheets

    Oh No No.
    Actually I am Exporting in the .CSV File format so that it can be opened in EXCEL Or OPEN OFFICE Spread Sheet.

    So I'll Reframe my Question.

    How to Export .CSV file in such a format that it must open in Multiple Sheets of EXCEL or OPEN OFFICE.

  5. #5
    Join Date
    Aug 2006
    Location
    Hubli, India
    Posts
    70

    Re: Export to CSV or Excel Multiple Sheets

    Please AnyBody

    Mr. WoF , JonnyPoet

    Please

  6. #6
    Join Date
    Aug 2006
    Location
    Hubli, India
    Posts
    70

    Question Re: Export to CSV or Excel Multiple Sheets

    What Should I to with this thread ?

  7. #7
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Export to CSV or Excel Multiple Sheets

    Sorry, RajWolf, even if you address me directly, I have to admit that I'm not an expert with Excel sheets.
    As I look at your code I don't even understand how it could export anything from an excel-file.

    I'd try to use Excel to export a .csv file first. Multi sheet of course. Then I'd examine the file. The .csv format is readable ASCII, so any information for multiple sheets will be seen. Maybe you have to add lines to create such info in your export function...
    Maybe it becomes clearer if you could provide a sample .csv file you have created with Excel and which you have tried to reimport successfully.

  8. #8
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: Export to CSV or Excel Multiple Sheets

    Okay here's the skinny on the subject.....

    EXCEL : has limits... Noticably - 255 columbs and 65535 rows... (per sheet) more specifically in Hex it has HFF col & HFFFF rows...

    You can not exceed this at all, and excel simly dumps the extra with a warning...

    CSV : Is a raw data format.. It does NOT have ANY commands that can be placed in it...

    With this in mind, All that you can do is to check your CSV output and trim it to 65534 rows of data and then write the rest into a second file (again checking for file length)...

    There after you can write a script in excell to import the files into seperate sheets of a single workbook...

    I've done plenty of work with CSV and Excel and can assit you with this...

    Gremmy...
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  9. #9
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Export to CSV or Excel Multiple Sheets

    Why are you trying to save it as a csv file? That will only open in Excel, and as Gremmy said, has limits.

    Save a txt file (or .wri, to open it in Wordpad). Then you could load it into Word, or import it into Access, which would be your best bet.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  10. #10
    Join Date
    Aug 2006
    Location
    Hubli, India
    Posts
    70

    Smile Re: Export to CSV or Excel Multiple Sheets


    CSV : Is a raw data format.. It does NOT have ANY commands that can be placed in it...
    EXCEL : has limits... Noticably - 255 columbs and 65535 rows... (per sheet) more specifically in Hex it has HFF col & HFFFF rows...
    Yes , I tried, you all are correct.
    What i was trying to do is export data in .CSV that the client user will be able to open it in Excel if he has or Open Office if he has. Then the Multiple Sheets problem Occured.


    I was mistaking CSV files but as said by GremlinSA .CSV is a raw data format.

    Thanks a lot for the HELP for now I know that it is not possible with .CSV files


    I think now I should export limited records and let the user do the management of the sheets.

    Thank you all !!!!

  11. #11
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: Export to CSV or Excel Multiple Sheets

    You can take it one step further and have a Excel script to merge all the CSV's on to a single Excel workbook with multiple sheets..

    It is a bit involved but posible..

    Gremmy..
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  12. #12
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Export to CSV or Excel Multiple Sheets

    Not if each sheet is bigger than the max. Best to use a DB. Your clients don't need Access for your app to read it.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  13. #13
    Join Date
    Nov 2004
    Location
    LA. California Raiders #1 AKA: Gangsta Yoda™
    Posts
    616

    Re: Export to CSV or Excel Multiple Sheets

    Since you are writting the csv file you can place a check for when it gets to 65,535 rows and then span it out to a secondary csv file.

    opening a csv in Excel is easy to do and the 65K limit is increased in Excel 2007

    But if the user doesnt have 2007 then this code to break up the import into multiple sheets is a value

    http://support.microsoft.com/kb/q120596/
    VB/Office Guru™ (AKA: Gangsta Yoda™)
    VB Forums - Super Moderator 2001-Present

    Microsoft MVP 2006-2011

    Please use [code]your code goes in here[/code] tags when posting code.

    Senior Software Engineer MCP, BSEE, CET
    VS 2012 Premium, VS 6.0 Enterprise SP6, VSTO, Office Ultimate 2010, Windows 7 Ultimate
    Star Wars Gangsta Rap SE Reputations & Rating Posts Office Primary Interop AssembliesAdvanced VB/Office Guru™ Word SpellChecker™.NETAdvanced VB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address ListVB6/Crystal Report Ex.VB6/CR Print Setup Dialog Ex.

  14. #14
    Join Date
    Nov 2005
    Posts
    13

    Re: Export to CSV or Excel Multiple Sheets

    You can export directly to excel sheet in an xls file by using the access oledb driver. It supports all kinds of file formats besides just the access mdb type. Check it out. You should be able to create a multiple sheet xls file that can be opened by excel and by open office.

  15. #15
    Join Date
    Nov 2004
    Location
    LA. California Raiders #1 AKA: Gangsta Yoda™
    Posts
    616

    Re: Export to CSV or Excel Multiple Sheets

    The link I posted does write out to multiple sheets.
    VB/Office Guru™ (AKA: Gangsta Yoda™)
    VB Forums - Super Moderator 2001-Present

    Microsoft MVP 2006-2011

    Please use [code]your code goes in here[/code] tags when posting code.

    Senior Software Engineer MCP, BSEE, CET
    VS 2012 Premium, VS 6.0 Enterprise SP6, VSTO, Office Ultimate 2010, Windows 7 Ultimate
    Star Wars Gangsta Rap SE Reputations & Rating Posts Office Primary Interop AssembliesAdvanced VB/Office Guru™ Word SpellChecker™.NETAdvanced VB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address ListVB6/Crystal Report Ex.VB6/CR Print Setup Dialog Ex.

Page 1 of 2 12 LastLast

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured