By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

how to read multiple worksheet from an excel file using php

P: 56
hello
Can any one tell me how to read multiple worksheets from a single excel file.I have stored that excel in xml file.so i want to read that xml that has multiple worksheet.And i want to store that multiple worksheet data in different table.How can i do it.Below is my xml file.


<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>The Fritz Family</Author>
<LastAuthor>com6</LastAuthor>
<Created>2008-05-17T17:58:56Z</Created>
<LastSaved>2008-06-10T06:16:20Z</LastSaved>
<Version>10.2625</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\comp4\Deval%20(F)\Office%20XP\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>11640</WindowHeight>
<WindowWidth>15480</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>135</WindowTopY>
<ActiveSheet>2</ActiveSheet>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Worksheet ss:Name="Master Import Sheet">
<Table ss:ExpandedColumnCount="11" ss:ExpandedRowCount="43" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="15">
<Column ss:StyleID="s81" ss:AutoFitWidth="0" ss:Width="90.75"/>
<Column ss:StyleID="s84" ss:Width="49.5"/>
<Column ss:StyleID="s84" ss:Width="51.75"/>
<Column ss:StyleID="s84" ss:AutoFitWidth="0" ss:Width="81.75"/>
<Column ss:StyleID="s84" ss:Width="83.25"/>
<Column ss:StyleID="s80" ss:AutoFitWidth="0" ss:Width="108"/>
<Column ss:StyleID="s80" ss:AutoFitWidth="0" ss:Width="107.25"/>
<Column ss:StyleID="s80" ss:AutoFitWidth="0" ss:Width="153.75"/>
<Column ss:StyleID="s80" ss:AutoFitWidth="0" ss:Width="127.5"/>
<Column ss:StyleID="s75" ss:Width="384.75"/>
<Column ss:Width="71.25"/>
<Row ss:Height="17.25" ss:StyleID="s62">
<Cell ss:StyleID="s76"><Data ss:Type="String">Item Number</Data><Comment
ss:Author="Matthew T. Fritz"><ss:Data
xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Face="Tahoma"
x:Family="Swiss" html:Size="8" html:Color="#000000">AUTOGENERATED CELL</Font></B></ss:Data></Comment></Cell>
<Cell ss:StyleID="s82"><Data ss:Type="String">Logo ID</Data><Comment
ss:Author="Matthew T. Fritz"><ss:Data
xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Face="Tahoma"
x:Family="Swiss" html:Size="8" html:Color="#000000">INPUT CELL</Font></B></ss:Data></Comment></Cell>
<Cell ss:StyleID="s82"><Data ss:Type="String">Color Id</Data><Comment
ss:Author="Matthew T. Fritz"><ss:Data
xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Face="Tahoma"
x:Family="Swiss" html:Size="8" html:Color="#000000">INPUT CELL</Font></B></ss:Data></Comment></Cell>
<Cell ss:StyleID="s82"><Data ss:Type="String">Style/Unit Id</Data><Comment
ss:Author="Matthew T. Fritz"><ss:Data
xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Face="Tahoma"
x:Family="Swiss" html:Size="8" html:Color="#000000">INPUT CELL</Font></B></ss:Data></Comment></Cell>
<Cell ss:StyleID="s82"><Data ss:Type="String">Descriptor ID</Data><Comment
ss:Author="Matthew T. Fritz"><ss:Data
xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Face="Tahoma"
x:Family="Swiss" html:Size="8" html:Color="#000000">INPUT CELL</Font></B></ss:Data></Comment></Cell>
<Cell ss:StyleID="s77"><Data ss:Type="String">Design/Logo Name</Data><Comment
ss:Author="Matthew T. Fritz"><ss:Data
xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Face="Tahoma"
x:Family="Swiss" html:Size="8" html:Color="#000000">AUTOGENERATED CELL</Font></B></ss:Data></Comment></Cell>
<Cell ss:StyleID="s77"><Data ss:Type="String">Color Name</Data><Comment
ss:Author="Matthew T. Fritz"><ss:Data
xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Face="Tahoma"
x:Family="Swiss" html:Size="8" html:Color="#000000">AUTOGENERATED CELL</Font></B></ss:Data></Comment></Cell>
<Cell ss:StyleID="s77"><Data ss:Type="String">Style/Unit Name</Data><Comment
ss:Author="Matthew T. Fritz"><ss:Data
xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Face="Tahoma"
x:Family="Swiss" html:Size="8" html:Color="#000000">AUTOGENERATED CELL</Font></B></ss:Data></Comment></Cell>
<Cell ss:StyleID="s77"><Data ss:Type="String">Descriptor Name</Data><Comment
ss:Author="Matthew T. Fritz"><ss:Data
xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Face="Tahoma"
x:Family="Swiss" html:Size="8" html:Color="#000000">AUTOGENERATED CELL</Font></B></ss:Data></Comment></Cell>
<Cell ss:StyleID="s73"><Data ss:Type="String">Item Name</Data><Comment
ss:Author="Matthew T. Fritz"><ss:Data
xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Face="Tahoma"
x:Family="Swiss" html:Size="8" html:Color="#000000">AUTOGENERATED CELL</Font></B></ss:Data></Comment></Cell>
</Row>
<Row ss:Height="15.75">
<Cell ss:StyleID="s78"
ss:Formula="=CONCATENATE((TEXT(RC [ 1 ],&quot;000&quot;)),(TEXT(RC[ 2],&quot;000&quot;)),(TEXT(RC[ 3],&quot;000&quot;)),(TEXT(RC[4],&quot;000&quot;)))"><Data
ss:Type="String">001002002001</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">001</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">002</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">002</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">001</Data></Cell>
<Cell ss:StyleID="s79"
ss:Formula="=VLOOKUP(RC[ -4],'DesignLogo (ABC)'!C[-5]:C[-4],2,FALSE)"><Data
ss:Type="String">SW-Explorer</Data></Cell>
<Cell ss:Formula="=VLOOKUP(RC[ -4],'Color (DEF)'!C[-6]:C[-5],2,FALSE)"><Data
ss:Type="String">Carrot</Data></Cell>
<Cell ss:Formula="=VLOOKUP(RC[-4],'StyleUnit (GHI)'!C[-7]:C[-6],2,FALSE)"><Data
ss:Type="String">Ladies Standard T-Shirt</Data></Cell>
<Cell ss:StyleID="s79"
ss:Formula="=VLOOKUP(RC[-4],'Descriptor (JKL)'!C[-8]:C[-7],2,FALSE)"><Data
ss:Type="String">Wholesale Youth XS</Data></Cell>
<Cell ss:StyleID="s74"
ss:Formula="=CONCATENATE(RC[-1],&quot; &quot;,RC[-2],&quot; &quot;,RC[-3],&quot; &quot;,RC[-4])"><Data
ss:Type="String">Wholesale Youth XS Ladies Standard T-Shirt Carrot SW-Explorer</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s78"
ss:Formula="=CONCATENATE((TEXT(RC[1],&quot;000&quot;)),(TEXT(RC[2],&quot;000&quot;)),(TEXT(RC[3],&quot;000&quot;)),(TEXT(RC[4],&quot;000&quot;)))"><Data
ss:Type="String">004001003001</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">004</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">001</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">003</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">001</Data></Cell>
<Cell ss:StyleID="s79"
ss:Formula="=VLOOKUP(RC[-4],'DesignLogo (ABC)'!C[-5]:C[-4],2,FALSE)"><Data
ss:Type="String">SW-Animal</Data></Cell>
<Cell ss:Formula="=VLOOKUP(RC[-4],'Color (DEF)'!C[-6]:C[-5],2,FALSE)"><Data
ss:Type="String">Khaki Green</Data></Cell>
<Cell ss:Formula="=VLOOKUP(RC[-4],'StyleUnit (GHI)'!C[-7]:C[-6],2,FALSE)"><Data
ss:Type="String">Unisex Recycled T-Shirt</Data></Cell>
<Cell ss:StyleID="s79"
ss:Formula="=VLOOKUP(RC[-4],'Descriptor (JKL)'!C[-8]:C[-7],2,FALSE)"><Data
ss:Type="String">Wholesale Youth XS</Data></Cell>
<Cell ss:StyleID="s74"
ss:Formula="=CONCATENATE(RC[-1],&quot; &quot;,RC[-2],&quot; &quot;,RC[-3],&quot; &quot;,RC[-4])"><Data
ss:Type="String">Wholesale Youth XS Unisex Recycled T-Shirt Khaki Green SW-Animal</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s78"
ss:Formula="=CONCATENATE((TEXT(RC[1],&quot;000&quot;)),(TEXT(RC[2],&quot;000&quot;)),(TEXT(RC[3],&quot;000&quot;)),(TEXT(RC[4],&quot;000&quot;)))"><Data
ss:Type="String">002004001002</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">002</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">004</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">001</Data></Cell>
<Cell ss:StyleID="s85"><Data ss:Type="String">002</Data></Cell>
<Cell ss:StyleID="s79"
ss:Formula="=VLOOKUP(RC[-4],'DesignLogo (ABC)'!C[-5]:C[-4],2,FALSE)"><Data
ss:Type="String">SW-Aid</Data></Cell>
<Cell ss:Formula="=VLOOKUP(RC[-4],'Color (DEF)'!C[-6]:C[-5],2,FALSE)"><Data
ss:Type="String">Mocha</Data></Cell>
<Cell ss:Formula="=VLOOKUP(RC[-4],'StyleUnit (GHI)'!C[-7]:C[-6],2,FALSE)"><Data
ss:Type="String">Unisex Standard T-Shirt</Data></Cell>
<Cell ss:StyleID="s79"
ss:Formula="=VLOOKUP(RC[-4],'Descriptor (JKL)'!C[-8]:C[-7],2,FALSE)"><Data
ss:Type="String">Wholesale Youth S</Data></Cell>
<Cell ss:StyleID="s74"
ss:Formula="=CONCATENATE(RC[-1],&quot; &quot;,RC[-2],&quot; &quot;,RC[-3],&quot; &quot;,RC[-4])"><Data
ss:Type="String">Wholesale Youth S Unisex Standard T-Shirt Mocha SW-Aid</Data></Cell>
</Row>

</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>-3</HorizontalResolution>
<VerticalResolution>-3</VerticalResolution>
</Print>
<Zoom>85</Zoom>
<Panes>
<Pane>
<Number>3</Number>
<RangeSelection>C1</RangeSelection>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="DesignLogo (ABC)">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="61" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Column ss:StyleID="s93" ss:Width="98.25"/>
<Column ss:StyleID="s70" ss:Width="122.25"/>
<Row ss:Height="15" ss:StyleID="s72">
<Cell ss:StyleID="s92"><Data ss:Type="String">Design/Logo ID</Data></Cell>
<Cell ss:StyleID="s71"><Data ss:Type="String">Design/Logo Name</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s86"><Data ss:Type="String">000</Data></Cell>
<Cell ss:StyleID="s69"><Data ss:Type="String">Test Logo</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s86"><Data ss:Type="String">001</Data></Cell>
<Cell ss:StyleID="s69"><Data ss:Type="String">SW-Explorer</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s86"><Data ss:Type="String">002</Data></Cell>
<Cell ss:StyleID="s69"><Data ss:Type="String">SW-Aid</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s86"><Data ss:Type="String">003</Data></Cell>
<Cell ss:StyleID="s69"><Data ss:Type="String">SW-Service</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s86"><Data ss:Type="String">004</Data></Cell>
<Cell ss:StyleID="s69"><Data ss:Type="String">SW-Animal</Data></Cell>
</Row>

</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Panes>
<Pane>
<Number>3</Number>
<RangeSelection>C1</RangeSelection>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Color (DEF)">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="61" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Column ss:StyleID="s93"/>
<Column ss:StyleID="s70" ss:Width="80.25"/>
<Row ss:Height="15" ss:StyleID="s72">
<Cell ss:StyleID="s92"><Data ss:Type="String">Color ID</Data></Cell>
<Cell ss:StyleID="s71"><Data ss:Type="String">Color Name</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s86"><Data ss:Type="String">000</Data></Cell>
<Cell ss:StyleID="s69"><Data ss:Type="String">Custom Choice</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s86"><Data ss:Type="String">001</Data></Cell>
<Cell ss:StyleID="s69"><Data ss:Type="String">Khaki Green</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s86"><Data ss:Type="String">002</Data></Cell>
<Cell ss:StyleID="s69"><Data ss:Type="String">Carrot</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s86"><Data ss:Type="String">003</Data></Cell>
<Cell ss:StyleID="s69"><Data ss:Type="String">Chrome</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s86"><Data ss:Type="String">004</Data></Cell>
<Cell ss:StyleID="s69"><Data ss:Type="String">Mocha</Data></Cell>
</Row>

</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveCol>1</ActiveCol>
<RangeSelection>C2</RangeSelection>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="StyleUnit (GHI)">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="27" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Column ss:StyleID="s90" ss:Width="79.5"/>
<Column ss:StyleID="s68" ss:Width="154.5"/>
<Row ss:Height="15.75" ss:StyleID="s65">
<Cell ss:StyleID="s88"><Data ss:Type="String">Style/Unit ID</Data></Cell>
<Cell ss:StyleID="s64"><Data ss:Type="String">Style/Unit Name</Data></Cell>
</Row>
<Row ss:StyleID="s63">
<Cell ss:StyleID="s87"><Data ss:Type="String">000</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">Test Style</Data></Cell>
</Row>
<Row ss:StyleID="s63">
<Cell ss:StyleID="s87"><Data ss:Type="String">001</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">Unisex Standard T-Shirt</Data></Cell>
</Row>
<Row ss:StyleID="s63">
<Cell ss:StyleID="s87"><Data ss:Type="String">002</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">Ladies Standard T-Shirt</Data></Cell>
</Row>
<Row ss:StyleID="s63">
<Cell ss:StyleID="s87"><Data ss:Type="String">003</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">Unisex Recycled T-Shirt</Data></Cell>
</Row>

</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>7</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Descriptor (JKL)">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="102" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Column ss:StyleID="s90" ss:Width="87.75"/>
<Column ss:StyleID="s68" ss:Width="154.5"/>
<Row ss:Height="15.75">
<Cell ss:StyleID="s88"><Data ss:Type="String">Descriptor ID</Data></Cell>
<Cell ss:StyleID="s64"><Data ss:Type="String">Style/Unit Name</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s87"><Data ss:Type="String">000</Data></Cell>
<Cell ss:StyleID="s66"/>
</Row>
<Row>
<Cell ss:StyleID="s87"><Data ss:Type="String">001</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">Wholesale Youth XS</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s87"><Data ss:Type="String">002</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">Wholesale Youth S</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s87"><Data ss:Type="String">003</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">Wholesale Youth M</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s87"><Data ss:Type="String">004</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">Wholesale Youth L</Data></Cell>
</Row>

</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>7</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>


How to read this multiple worksheets?
Help me pls.
Thanks
Jun 10 '08 #1
Share this Question
Share on Google+
3 Replies


jkmyoung
Expert 100+
P: 2,057
The structure of this is basically:
Expand|Select|Wrap|Line Numbers
  1. <Workbook>
  2.   <Worksheet>
  3.      <Row>
  4.         <Cell>Data</Cell>
  5.      </Row>
  6.    </Worksheet>
  7. <Workbook>
  8.  
The question then becomes, do you want to use a xml module in order to read each of these worksheets? What do you mean by read? What are you going to do with the data afterwards?

Another possible solution might also be to write a VBScript macro that saves each of the worksheets to seperate CSV files, as you're probably more comfortable working with CSV.
Jun 10 '08 #2

P: 56
The structure of this is basically:
Expand|Select|Wrap|Line Numbers
  1. <Workbook>
  2.   <Worksheet>
  3.      <Row>
  4.         <Cell>Data</Cell>
  5.      </Row>
  6.    </Worksheet>
  7. <Workbook>
  8.  
The question then becomes, do you want to use a xml module in order to read each of these worksheets? What do you mean by read? What are you going to do with the data afterwards?

Another possible solution might also be to write a VBScript macro that saves each of the worksheets to seperate CSV files, as you're probably more comfortable working with CSV.
Thanks for ur response..

I want to actually do that the different worksheets data are inserted in different tables.so i want to get each worksheet to get its data and after that i want to insert that data in database table.

Please help me..
thanks
Jun 11 '08 #3

jkmyoung
Expert 100+
P: 2,057
Seriously suggest using a macro to turn each worksheet into a csv. Then load the csv into the database.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Sub Macro1()
  3. '
  4. ' Macro1 Macro
  5. ' Macro recorded 11/06/2008 by jkmyoung
  6.     Dim sName As String
  7.     Dim fName As String
  8.     For Each Sht In Application.Worksheets
  9.     Windows("Book1.xls").Activate
  10.     sName = Sht.Name
  11.     Sheets(sName).Select
  12.     Sheets(sName).Copy
  13.     fName = "C:\temp\" + sName
  14.     ActiveWorkbook.SaveAs Filename:= _
  15.         fName, FileFormat:=xlCSV, _
  16.         ReadOnlyRecommended:=False, CreateBackup:=False        
  17.     ActiveWorkbook.Close
  18.     Next Sht
  19.  
  20. End Sub
  21.  
Jun 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.