Z
Zerex71
Greetings,
I seem to be having a devil of a problem which is annoying the dickens
out of me, and I don't know how to fix it. Here's my problem:
In spreadsheet 1 (which contains my "processing macro"), I have two
columns of interest - column A (XML file name) and column D (CSV file
name). I want to use my processing macro (actually a public function)
to run down the list of files in column A and open them as XML and
automatically save them out to the corresponding CSV file name in
column D and not have any user interference or require any input.
Seems simple, right? Well, every time it gets to the first file, it
opens the file up as a spreadsheet (didn't really want that) and there
it is but it's locked -- I can't do anything with it until I switch
back to spreadsheet 1 (the controlling spreadsheet).
But here's the worse problem - debugging my processing macro (public
function), I find that it goes right back into the function again like
it's starting all over and I *do not want that*! I'm seriously in the
middle of my loop, find the first file, possibly second one, and the
function is called all over again, right at the start. Ugh. What's
going on here? Is there some trigger or event being raised when I do
the OpenXML that is causing spreadsheet 1 to call its function again?
Here's the public function code. It's messy because I am experimenting
with things right now (commented out):
Public Function IdentifyXMLFiles() As String
' This function automatically figures out the number of XML files
to be processed.
' It is a sample of how to use Excel to automatically go through a
series of cells
' and identify the content of the cells.
Dim count As Integer ' used to keep track of how many we find
Dim xmlFile As String ' used to hold value of current XML file
Dim csvFile As String ' used to hold the corresponding CSV file
Dim xmlCell As String ' used to hold cell identifier to pass to the
Range function
Dim csvCell As String ' used to hold cell identifier to pass to the
Range function
' Declare the working directories for input and output
Dim inputDir As String
inputDir = "D:\Work\Projects\OneTESS\Data\Data Conversion\Input
XML\"
Dim outputDir As String
outputDir = "D:\Work\Projects\OneTESS\Data\Data Conversion\Output
CSV\"
' Fix the worksheet containing this macro so we can explicitly use
it with
' no confusion
Dim SourceFile As String
SourceFile = "XML-CSV Data File Mapping r4.xls" ' This is the name
of the file containing *this* macro
' Start by getting into the source directory
ChDir inputDir
For i = 8 To 375 ' range of rows which hold XML file names
xmlCell = "A" + Right(Str(i), Len(Str(i)) - 1)
csvCell = "D" + Right(Str(i), Len(Str(i)) - 1)
' "File Mapping" is the name of the source worksheet which
contains the columns of interest.
If ((Workbooks(SourceFile).Worksheets("File
Mapping").Range(xmlCell).Text <> "No match found") And _
(Workbooks(SourceFile).Worksheets("File
Mapping").Range(xmlCell).Text <> "")) Then
' We have found a valid XML file - store it
xmlFile = Workbooks(SourceFile).Worksheets("File
Mapping").Range(xmlCell).Text ' Capture input file name
csvFile = Workbooks(SourceFile).Worksheets("File
Mapping").Range(csvCell).Text ' Get corresponding output file name
' Attempt to open the file and load it as an XML file
Workbooks.OpenXML Filename:=(inputDir + xmlFile),
LoadOption:=xlXmlLoadImportToList
'MyWorkbook = ActiveWorkbook
' Save the file off as a CSV and close it. Return to the
"host" spreadsheet (the
' one containing this macro) and continue processing the
other files.
'ActiveWorkbook.Close SaveChanges:=True,
Filename:=(outputDir + csvFile), RouteWorkbook:=False
'MyWorkbook.Close
Workbooks(1).Close SaveChanges:=True, Filename:=(outputDir
+ csvFile), RouteWorkbook:=False
' NOTE: Doing a SaveAs causes the file to open and require
user input. We don't want that.
' We want Excel to automatically do the conversion and save
it using the filename specified.
' Correct this and you might just be in business!
' Increment the counter
count = count + 1
End If
Next i
' Now output the count
'IdentifyXMLFiles = Str(count)
End Function
Please help if you have any ideas! I've done a fair bit of simple VBA
programming but usually it's just to attach a simple function with a
return value and fill cells with that function to compute various
things. I'm trying to use this as a
Other disclaimers:
1. I'm not interested in nor do I need style sheets or the schemas.
2. I just want VB to open the file internally, process the spreadsheet,
and write the file out. Is that so hard???
I don't care to see the
conversion "in progress".
Mike
I seem to be having a devil of a problem which is annoying the dickens
out of me, and I don't know how to fix it. Here's my problem:
In spreadsheet 1 (which contains my "processing macro"), I have two
columns of interest - column A (XML file name) and column D (CSV file
name). I want to use my processing macro (actually a public function)
to run down the list of files in column A and open them as XML and
automatically save them out to the corresponding CSV file name in
column D and not have any user interference or require any input.
Seems simple, right? Well, every time it gets to the first file, it
opens the file up as a spreadsheet (didn't really want that) and there
it is but it's locked -- I can't do anything with it until I switch
back to spreadsheet 1 (the controlling spreadsheet).
But here's the worse problem - debugging my processing macro (public
function), I find that it goes right back into the function again like
it's starting all over and I *do not want that*! I'm seriously in the
middle of my loop, find the first file, possibly second one, and the
function is called all over again, right at the start. Ugh. What's
going on here? Is there some trigger or event being raised when I do
the OpenXML that is causing spreadsheet 1 to call its function again?
Here's the public function code. It's messy because I am experimenting
with things right now (commented out):
Public Function IdentifyXMLFiles() As String
' This function automatically figures out the number of XML files
to be processed.
' It is a sample of how to use Excel to automatically go through a
series of cells
' and identify the content of the cells.
Dim count As Integer ' used to keep track of how many we find
Dim xmlFile As String ' used to hold value of current XML file
Dim csvFile As String ' used to hold the corresponding CSV file
Dim xmlCell As String ' used to hold cell identifier to pass to the
Range function
Dim csvCell As String ' used to hold cell identifier to pass to the
Range function
' Declare the working directories for input and output
Dim inputDir As String
inputDir = "D:\Work\Projects\OneTESS\Data\Data Conversion\Input
XML\"
Dim outputDir As String
outputDir = "D:\Work\Projects\OneTESS\Data\Data Conversion\Output
CSV\"
' Fix the worksheet containing this macro so we can explicitly use
it with
' no confusion
Dim SourceFile As String
SourceFile = "XML-CSV Data File Mapping r4.xls" ' This is the name
of the file containing *this* macro
' Start by getting into the source directory
ChDir inputDir
For i = 8 To 375 ' range of rows which hold XML file names
xmlCell = "A" + Right(Str(i), Len(Str(i)) - 1)
csvCell = "D" + Right(Str(i), Len(Str(i)) - 1)
' "File Mapping" is the name of the source worksheet which
contains the columns of interest.
If ((Workbooks(SourceFile).Worksheets("File
Mapping").Range(xmlCell).Text <> "No match found") And _
(Workbooks(SourceFile).Worksheets("File
Mapping").Range(xmlCell).Text <> "")) Then
' We have found a valid XML file - store it
xmlFile = Workbooks(SourceFile).Worksheets("File
Mapping").Range(xmlCell).Text ' Capture input file name
csvFile = Workbooks(SourceFile).Worksheets("File
Mapping").Range(csvCell).Text ' Get corresponding output file name
' Attempt to open the file and load it as an XML file
Workbooks.OpenXML Filename:=(inputDir + xmlFile),
LoadOption:=xlXmlLoadImportToList
'MyWorkbook = ActiveWorkbook
' Save the file off as a CSV and close it. Return to the
"host" spreadsheet (the
' one containing this macro) and continue processing the
other files.
'ActiveWorkbook.Close SaveChanges:=True,
Filename:=(outputDir + csvFile), RouteWorkbook:=False
'MyWorkbook.Close
Workbooks(1).Close SaveChanges:=True, Filename:=(outputDir
+ csvFile), RouteWorkbook:=False
' NOTE: Doing a SaveAs causes the file to open and require
user input. We don't want that.
' We want Excel to automatically do the conversion and save
it using the filename specified.
' Correct this and you might just be in business!
' Increment the counter
count = count + 1
End If
Next i
' Now output the count
'IdentifyXMLFiles = Str(count)
End Function
Please help if you have any ideas! I've done a fair bit of simple VBA
programming but usually it's just to attach a simple function with a
return value and fill cells with that function to compute various
things. I'm trying to use this as a
Other disclaimers:
1. I'm not interested in nor do I need style sheets or the schemas.
2. I just want VB to open the file internally, process the spreadsheet,
and write the file out. Is that so hard???
I don't care to see theconversion "in progress".
Mike