ushasharma84
07-25-2011, 03:38 AM
hi
I have a code which converts a single worksheet to xml. Now I want that all worksheets data of a workbook to get converted to xml at same time on a single click.
I am pasting the code which I am using
findusedrange subroutine which automatically selects the non empty continuous region of the current/active worksheet
' To automatically select the "REAL"/non empty continuous regions (rows and columns)
Sub FindUsedRange()
Dim LastRow As Long
Dim FirstRow As Long
Dim LastCol As Integer
Dim FirstCol As Integer
' Find the FIRST real row
FirstRow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
' Find the FIRST real column
FirstCol = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
' Find the LAST real row
LastRow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
' Find the LAST real column
LastCol = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
'Select the ACTUAL Used Range as identified by the variables identified above
'MsgBox (FirstRow & "," & LastRow & "," & FirstCol & "," & LastCol)
Dim topCel As Range
Dim bottomCel As Range
Set topCel = Cells(FirstRow, FirstCol)
Set bottomCel = Cells(LastRow, LastCol)
ActiveSheet.Range(topCel, bottomCel).Select
End Sub
Subroutine for writing plain string out a file
Sub sWriteFile(strXML As String, strFullFileName As String)
Dim intFileNum As String
intFileNum = FreeFile
Open strFullFileName For Output As #intFileNum
Print #intFileNum, strXML
Close #intFileNum
End Sub
These subroutines have been called on a button click
Private Sub CmdProcessXML_Click()
Dim strXML As String
If Application.Count(Selection) = 0 Then
FindUsedRange
End If
'fGenerateXML sub routine generates xml from an excel range strXML = fGenerateXML(Selection, "issue")
' sWriteFile strXML, ThisWorkbook.Path & filenameinput
sWriteFile strXML, filenameinput
MsgBox ("Completed. XML Written to " & filenameinput)
Startform.Hide
End Sub
providing a default name to exported xml file and saving in the same directory in which xls file resides
Private Sub UserForm_Initialize()
filenameinput.Text = ThisWorkbook.Path & "\default.xml"
End Sub
:eek:
Thanks in advance
I have a code which converts a single worksheet to xml. Now I want that all worksheets data of a workbook to get converted to xml at same time on a single click.
I am pasting the code which I am using
findusedrange subroutine which automatically selects the non empty continuous region of the current/active worksheet
' To automatically select the "REAL"/non empty continuous regions (rows and columns)
Sub FindUsedRange()
Dim LastRow As Long
Dim FirstRow As Long
Dim LastCol As Integer
Dim FirstCol As Integer
' Find the FIRST real row
FirstRow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
' Find the FIRST real column
FirstCol = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
' Find the LAST real row
LastRow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
' Find the LAST real column
LastCol = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
'Select the ACTUAL Used Range as identified by the variables identified above
'MsgBox (FirstRow & "," & LastRow & "," & FirstCol & "," & LastCol)
Dim topCel As Range
Dim bottomCel As Range
Set topCel = Cells(FirstRow, FirstCol)
Set bottomCel = Cells(LastRow, LastCol)
ActiveSheet.Range(topCel, bottomCel).Select
End Sub
Subroutine for writing plain string out a file
Sub sWriteFile(strXML As String, strFullFileName As String)
Dim intFileNum As String
intFileNum = FreeFile
Open strFullFileName For Output As #intFileNum
Print #intFileNum, strXML
Close #intFileNum
End Sub
These subroutines have been called on a button click
Private Sub CmdProcessXML_Click()
Dim strXML As String
If Application.Count(Selection) = 0 Then
FindUsedRange
End If
'fGenerateXML sub routine generates xml from an excel range strXML = fGenerateXML(Selection, "issue")
' sWriteFile strXML, ThisWorkbook.Path & filenameinput
sWriteFile strXML, filenameinput
MsgBox ("Completed. XML Written to " & filenameinput)
Startform.Hide
End Sub
providing a default name to exported xml file and saving in the same directory in which xls file resides
Private Sub UserForm_Initialize()
filenameinput.Text = ThisWorkbook.Path & "\default.xml"
End Sub
:eek:
Thanks in advance