I have a program that produces a CSV file without headers. When the program
finishes creating the file, it launches excel and opens the CSV. Is there a
way that I can have headers added to the file?

mr_bill,

I would look into the "program that produces a CSV file" first to see if
there are any header options.

If not, since the program opens an instance of Excel, you could not
automatically format it unless you have Excel to perform this on start up,
which would affect every file you open.

You could write a macro, and make it available on an Excel toolbar, that you
could click on when the file opens. This macro could set the headers for you.
The only drawback to this is if you have an instance of Excel open at the
time the CSV instance opens, it will error on trying to open the Personal.xls
file for a second time.

Just some thoughts,

Alan


"The only dumb question is a question left unasked."



"mr_bill" wrote:

> I have a program that produces a CSV file without headers. When the program
> finishes creating the file, it launches excel and opens the CSV. Is there a
> way that I can have headers added to the file?

It would be difficult to get the program to put headers on, there are date
and numeric fields and the headers would be text.

Would it be possible to create a template that would auto import the csv? I
could have the full path and file name written to a text file that could be
read by a macro to import the csv file.

"Alan" wrote:

> mr_bill,
>
> I would look into the "program that produces a CSV file" first to see if
> there are any header options.
>
> If not, since the program opens an instance of Excel, you could not
> automatically format it unless you have Excel to perform this on start up,
> which would affect every file you open.
>
> You could write a macro, and make it available on an Excel toolbar, that you
> could click on when the file opens. This macro could set the headers for you.
> The only drawback to this is if you have an instance of Excel open at the
> time the CSV instance opens, it will error on trying to open the Personal.xls
> file for a second time.
>
> Just some thoughts,
>
> Alan
>
>
> "The only dumb question is a question left unasked."
>
>
>
> "mr_bill" wrote:
>
> > I have a program that produces a CSV file without headers. When the program
> > finishes creating the file, it launches excel and opens the CSV. Is there a
> > way that I can have headers added to the file?

Might I ask, what program is creating the CSV file?

You could set up a template with the header already in it, or the code to
add the headers can be included in the macro.

Yes, you can open a text file and use the value, (CSV source file
directory), with a macro

Workbooks.OpenText Filename:= _
"YourDirectorPath&\YourFileLocater.txt", Origin:=437 _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1), _
TrailingMinusNumbers:=True

The path will be in A1. Setting a path variable, this can be used to open
the CSV file then closing the text file:

Dim csvPath As String
csvPath = Range("A1").Value
ActiveWorkbook.Close False

Opens the CSV file.

Workbooks.Open Filename:= csvPath


The text file has to have the exact path & filename included.

The rest is a matter of copying the data into the template or just adding a
header. Not difficult.


Alan


"The only dumb question is a question left unasked."


"mr_bill" wrote:

> It would be difficult to get the program to put headers on, there are date
> and numeric fields and the headers would be text.
>
> Would it be possible to create a template that would auto import the csv? I
> could have the full path and file name written to a text file that could be
> read by a macro to import the csv file.
>
> "Alan" wrote:
>
> > mr_bill,
> >
> > I would look into the "program that produces a CSV file" first to see if
> > there are any header options.
> >
> > If not, since the program opens an instance of Excel, you could not
> > automatically format it unless you have Excel to perform this on start up,
> > which would affect every file you open.
> >
> > You could write a macro, and make it available on an Excel toolbar, that you
> > could click on when the file opens. This macro could set the headers for you.
> > The only drawback to this is if you have an instance of Excel open at the
> > time the CSV instance opens, it will error on trying to open the Personal.xls
> > file for a second time.
> >
> > Just some thoughts,
> >
> > Alan
> >
> >
> > "The only dumb question is a question left unasked."
> >
> >
> >
> > "mr_bill" wrote:
> >
> > > I have a program that produces a CSV file without headers. When the program
> > > finishes creating the file, it launches excel and opens the CSV. Is there a
> > > way that I can have headers added to the file?

I have written a query on an AS400 that produces an output file. The output
file name is variable (usually the terminal that is logged on). Then there
is a program that copies the data from the output file and writes it to a
CSV. This program strips off the headers, and if I try to make the first
line of the file a header file, I have issues with the data types. The CSV
is the copied to the IFS portion of the AS400 which is like a Windows Data
share. I can map network drive to the share, etc, so the data is available
to users that have authority.

The program then launches the STRPCCMD (an AS400 command to start a program
on the PC that envoked it) that opens the CSV. Since Excel is the program
that is set to open the CSV, the data is then displayed in Excel. The users
want to have the data in Excel, so that is good, the only problem is that
there are no headers and no formating to the data.

I was wanting to create a Macro in the template that would open
automatically when launced and would import the CSV data. The name of the
CSV file is variable, and the user won't know what it is to select it from a
list, so it needs to happen without user interaction. I was working on
trying to get the macro to import the CSV based upon a TXT file that has the
path and file name.

"Alan" wrote:

> Might I ask, what program is creating the CSV file?
>
> You could set up a template with the header already in it, or the code to
> add the headers can be included in the macro.
>
> Yes, you can open a text file and use the value, (CSV source file
> directory), with a macro
>
> Workbooks.OpenText Filename:= _
> "YourDirectorPath&\YourFileLocater.txt", Origin:=437 _
> , StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1), _
> TrailingMinusNumbers:=True
>
> The path will be in A1. Setting a path variable, this can be used to open
> the CSV file then closing the text file:
>
> Dim csvPath As String
> csvPath = Range("A1").Value
> ActiveWorkbook.Close False
>
> Opens the CSV file.
>
> Workbooks.Open Filename:= csvPath
>
>
> The text file has to have the exact path & filename included.
>
> The rest is a matter of copying the data into the template or just adding a
> header. Not difficult.
>
>
> Alan
>
>
> "The only dumb question is a question left unasked."
>
>
> "mr_bill" wrote:
>
> > It would be difficult to get the program to put headers on, there are date
> > and numeric fields and the headers would be text.
> >
> > Would it be possible to create a template that would auto import the csv? I
> > could have the full path and file name written to a text file that could be
> > read by a macro to import the csv file.
> >
> > "Alan" wrote:
> >
> > > mr_bill,
> > >
> > > I would look into the "program that produces a CSV file" first to see if
> > > there are any header options.
> > >
> > > If not, since the program opens an instance of Excel, you could not
> > > automatically format it unless you have Excel to perform this on start up,
> > > which would affect every file you open.
> > >
> > > You could write a macro, and make it available on an Excel toolbar, that you
> > > could click on when the file opens. This macro could set the headers for you.
> > > The only drawback to this is if you have an instance of Excel open at the
> > > time the CSV instance opens, it will error on trying to open the Personal.xls
> > > file for a second time.
> > >
> > > Just some thoughts,
> > >
> > > Alan
> > >
> > >
> > > "The only dumb question is a question left unasked."
> > >
> > >
> > >
> > > "mr_bill" wrote:
> > >
> > > > I have a program that produces a CSV file without headers. When the program
> > > > finishes creating the file, it launches excel and opens the CSV. Is there a
> > > > way that I can have headers added to the file?

If the file name is variable and the user does not know what it is, then one
way would be to use Application level Events (see
http://www.cpearson.com/excel/AppEvent.htm), to check in the
App_WorkbookOpen event if the file is a CSV. If there are no identifying
structure/data you can use, ask the user "Is this the AS400 export file ?".
If yes, insert a row at A:A and enter your header and formatting etc.

NickHK

"mr_bill" <(E-Mail Removed)> wrote in message
news:A898DBBD-681C-49B8-913C-(E-Mail Removed)...
> I have written a query on an AS400 that produces an output file. The
output
> file name is variable (usually the terminal that is logged on). Then
there
> is a program that copies the data from the output file and writes it to a
> CSV. This program strips off the headers, and if I try to make the first
> line of the file a header file, I have issues with the data types. The
CSV
> is the copied to the IFS portion of the AS400 which is like a Windows Data
> share. I can map network drive to the share, etc, so the data is
available
> to users that have authority.
>
> The program then launches the STRPCCMD (an AS400 command to start a
program
> on the PC that envoked it) that opens the CSV. Since Excel is the program
> that is set to open the CSV, the data is then displayed in Excel. The
users
> want to have the data in Excel, so that is good, the only problem is that
> there are no headers and no formating to the data.
>
> I was wanting to create a Macro in the template that would open
> automatically when launced and would import the CSV data. The name of the
> CSV file is variable, and the user won't know what it is to select it from
a
> list, so it needs to happen without user interaction. I was working on
> trying to get the macro to import the CSV based upon a TXT file that has
the
> path and file name.
>
> "Alan" wrote:
>
> > Might I ask, what program is creating the CSV file?
> >
> > You could set up a template with the header already in it, or the code
to
> > add the headers can be included in the macro.
> >
> > Yes, you can open a text file and use the value, (CSV source file
> > directory), with a macro
> >
> > Workbooks.OpenText Filename:= _
> > "YourDirectorPath&\YourFileLocater.txt", Origin:=437 _
> > , StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1), _
> > TrailingMinusNumbers:=True
> >
> > The path will be in A1. Setting a path variable, this can be used to
open
> > the CSV file then closing the text file:
> >
> > Dim csvPath As String
> > csvPath = Range("A1").Value
> > ActiveWorkbook.Close False
> >
> > Opens the CSV file.
> >
> > Workbooks.Open Filename:= csvPath
> >
> >
> > The text file has to have the exact path & filename included.
> >
> > The rest is a matter of copying the data into the template or just
adding a
> > header. Not difficult.
> >
> >
> > Alan
> >
> >
> > "The only dumb question is a question left unasked."
> >
> >
> > "mr_bill" wrote:
> >
> > > It would be difficult to get the program to put headers on, there are
date
> > > and numeric fields and the headers would be text.
> > >
> > > Would it be possible to create a template that would auto import the
csv? I
> > > could have the full path and file name written to a text file that
could be
> > > read by a macro to import the csv file.
> > >
> > > "Alan" wrote:
> > >
> > > > mr_bill,
> > > >
> > > > I would look into the "program that produces a CSV file" first to
see if
> > > > there are any header options.
> > > >
> > > > If not, since the program opens an instance of Excel, you could not
> > > > automatically format it unless you have Excel to perform this on
start up,
> > > > which would affect every file you open.
> > > >
> > > > You could write a macro, and make it available on an Excel toolbar,
that you
> > > > could click on when the file opens. This macro could set the headers
for you.
> > > > The only drawback to this is if you have an instance of Excel open
at the
> > > > time the CSV instance opens, it will error on trying to open the
Personal.xls
> > > > file for a second time.
> > > >
> > > > Just some thoughts,
> > > >
> > > > Alan
> > > >
> > > >
> > > > "The only dumb question is a question left unasked."
> > > >
> > > >
> > > >
> > > > "mr_bill" wrote:
> > > >
> > > > > I have a program that produces a CSV file without headers. When
the program
> > > > > finishes creating the file, it launches excel and opens the CSV.
Is there a
> > > > > way that I can have headers added to the file?

My company runs the AS400 also. They always had issues with exports to any
usefull file type. 4 years they hired a VB programmer to write their own
program and took our equipment off the AS400. Of course, the financial and
order fullfillment aspects of the business still ride on the back of the
AS400. We have one full time AS400 guy who seems to have about 3 hairs left.

Does each user generate their own reports? At time of report generation,
does it open an instance of Excel immediately? Both of the prior questions
are examples of my company's method of madness when using the AS400 for
equipment reports. No way the exporting program can open a template instead
of a default workbook?



"mr_bill" <(E-Mail Removed)> wrote in message
news:A898DBBD-681C-49B8-913C-(E-Mail Removed)...
>I have written a query on an AS400 that produces an output file. The
>output
> file name is variable (usually the terminal that is logged on). Then
> there
> is a program that copies the data from the output file and writes it to a
> CSV. This program strips off the headers, and if I try to make the first
> line of the file a header file, I have issues with the data types. The
> CSV
> is the copied to the IFS portion of the AS400 which is like a Windows Data
> share. I can map network drive to the share, etc, so the data is
> available
> to users that have authority.
>
> The program then launches the STRPCCMD (an AS400 command to start a
> program
> on the PC that envoked it) that opens the CSV. Since Excel is the program
> that is set to open the CSV, the data is then displayed in Excel. The
> users
> want to have the data in Excel, so that is good, the only problem is that
> there are no headers and no formating to the data.
>
> I was wanting to create a Macro in the template that would open
> automatically when launced and would import the CSV data. The name of the
> CSV file is variable, and the user won't know what it is to select it from
> a
> list, so it needs to happen without user interaction. I was working on
> trying to get the macro to import the CSV based upon a TXT file that has
> the
> path and file name.
>
> "Alan" wrote:
>
>> Might I ask, what program is creating the CSV file?
>>
>> You could set up a template with the header already in it, or the code to
>> add the headers can be included in the macro.
>>
>> Yes, you can open a text file and use the value, (CSV source file
>> directory), with a macro
>>
>> Workbooks.OpenText Filename:= _
>> "YourDirectorPath&\YourFileLocater.txt", Origin:=437 _
>> , StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1), _
>> TrailingMinusNumbers:=True
>>
>> The path will be in A1. Setting a path variable, this can be used to open
>> the CSV file then closing the text file:
>>
>> Dim csvPath As String
>> csvPath = Range("A1").Value
>> ActiveWorkbook.Close False
>>
>> Opens the CSV file.
>>
>> Workbooks.Open Filename:= csvPath
>>
>>
>> The text file has to have the exact path & filename included.
>>
>> The rest is a matter of copying the data into the template or just adding
>> a
>> header. Not difficult.
>>
>>
>> Alan
>>
>>
>> "The only dumb question is a question left unasked."
>>
>>
>> "mr_bill" wrote:
>>
>> > It would be difficult to get the program to put headers on, there are
>> > date
>> > and numeric fields and the headers would be text.
>> >
>> > Would it be possible to create a template that would auto import the
>> > csv? I
>> > could have the full path and file name written to a text file that
>> > could be
>> > read by a macro to import the csv file.
>> >
>> > "Alan" wrote:
>> >
>> > > mr_bill,
>> > >
>> > > I would look into the "program that produces a CSV file" first to see
>> > > if
>> > > there are any header options.
>> > >
>> > > If not, since the program opens an instance of Excel, you could not
>> > > automatically format it unless you have Excel to perform this on
>> > > start up,
>> > > which would affect every file you open.
>> > >
>> > > You could write a macro, and make it available on an Excel toolbar,
>> > > that you
>> > > could click on when the file opens. This macro could set the headers
>> > > for you.
>> > > The only drawback to this is if you have an instance of Excel open at
>> > > the
>> > > time the CSV instance opens, it will error on trying to open the
>> > > Personal.xls
>> > > file for a second time.
>> > >
>> > > Just some thoughts,
>> > >
>> > > Alan
>> > >
>> > >
>> > > "The only dumb question is a question left unasked."
>> > >
>> > >
>> > >
>> > > "mr_bill" wrote:
>> > >
>> > > > I have a program that produces a CSV file without headers. When
>> > > > the program
>> > > > finishes creating the file, it launches excel and opens the CSV.
>> > > > Is there a
>> > > > way that I can have headers added to the file?

Finally a pro jumps in with the goods! Good idea.


"NickHK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If the file name is variable and the user does not know what it is, then
> one
> way would be to use Application level Events (see
> http://www.cpearson.com/excel/AppEvent.htm), to check in the
> App_WorkbookOpen event if the file is a CSV. If there are no identifying
> structure/data you can use, ask the user "Is this the AS400 export file
> ?".
> If yes, insert a row at A:A and enter your header and formatting etc.
>
> NickHK
>
> "mr_bill" <(E-Mail Removed)> wrote in message
> news:A898DBBD-681C-49B8-913C-(E-Mail Removed)...
>> I have written a query on an AS400 that produces an output file. The
> output
>> file name is variable (usually the terminal that is logged on). Then
> there
>> is a program that copies the data from the output file and writes it to a
>> CSV. This program strips off the headers, and if I try to make the first
>> line of the file a header file, I have issues with the data types. The
> CSV
>> is the copied to the IFS portion of the AS400 which is like a Windows
>> Data
>> share. I can map network drive to the share, etc, so the data is
> available
>> to users that have authority.
>>
>> The program then launches the STRPCCMD (an AS400 command to start a
> program
>> on the PC that envoked it) that opens the CSV. Since Excel is the
>> program
>> that is set to open the CSV, the data is then displayed in Excel. The
> users
>> want to have the data in Excel, so that is good, the only problem is that
>> there are no headers and no formating to the data.
>>
>> I was wanting to create a Macro in the template that would open
>> automatically when launced and would import the CSV data. The name of
>> the
>> CSV file is variable, and the user won't know what it is to select it
>> from
> a
>> list, so it needs to happen without user interaction. I was working on
>> trying to get the macro to import the CSV based upon a TXT file that has
> the
>> path and file name.
>>
>> "Alan" wrote:
>>
>> > Might I ask, what program is creating the CSV file?
>> >
>> > You could set up a template with the header already in it, or the code
> to
>> > add the headers can be included in the macro.
>> >
>> > Yes, you can open a text file and use the value, (CSV source file
>> > directory), with a macro
>> >
>> > Workbooks.OpenText Filename:= _
>> > "YourDirectorPath&\YourFileLocater.txt", Origin:=437 _
>> > , StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1),
>> > _
>> > TrailingMinusNumbers:=True
>> >
>> > The path will be in A1. Setting a path variable, this can be used to
> open
>> > the CSV file then closing the text file:
>> >
>> > Dim csvPath As String
>> > csvPath = Range("A1").Value
>> > ActiveWorkbook.Close False
>> >
>> > Opens the CSV file.
>> >
>> > Workbooks.Open Filename:= csvPath
>> >
>> >
>> > The text file has to have the exact path & filename included.
>> >
>> > The rest is a matter of copying the data into the template or just
> adding a
>> > header. Not difficult.
>> >
>> >
>> > Alan
>> >
>> >
>> > "The only dumb question is a question left unasked."
>> >
>> >
>> > "mr_bill" wrote:
>> >
>> > > It would be difficult to get the program to put headers on, there are
> date
>> > > and numeric fields and the headers would be text.
>> > >
>> > > Would it be possible to create a template that would auto import the
> csv? I
>> > > could have the full path and file name written to a text file that
> could be
>> > > read by a macro to import the csv file.
>> > >
>> > > "Alan" wrote:
>> > >
>> > > > mr_bill,
>> > > >
>> > > > I would look into the "program that produces a CSV file" first to
> see if
>> > > > there are any header options.
>> > > >
>> > > > If not, since the program opens an instance of Excel, you could not
>> > > > automatically format it unless you have Excel to perform this on
> start up,
>> > > > which would affect every file you open.
>> > > >
>> > > > You could write a macro, and make it available on an Excel toolbar,
> that you
>> > > > could click on when the file opens. This macro could set the
>> > > > headers
> for you.
>> > > > The only drawback to this is if you have an instance of Excel open
> at the
>> > > > time the CSV instance opens, it will error on trying to open the
> Personal.xls
>> > > > file for a second time.
>> > > >
>> > > > Just some thoughts,
>> > > >
>> > > > Alan
>> > > >
>> > > >
>> > > > "The only dumb question is a question left unasked."
>> > > >
>> > > >
>> > > >
>> > > > "mr_bill" wrote:
>> > > >
>> > > > > I have a program that produces a CSV file without headers. When
> the program
>> > > > > finishes creating the file, it launches excel and opens the CSV.
> Is there a
>> > > > > way that I can have headers added to the file?
>
>

What I did was had the program that produces the CSV also write out a text
file with the path and CSV file name. I created an auto execute macro in the
template that has all the header data to read the TXT file and then import
the CSV file.


Private Sub Workbook_Open()

Dim ConnString As String
Open "location of file\qryfile.txt" For Input As #1
Line Input #1, ConnString
Close #1

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & ConnString _
, Destination:=Range("A3"))
.Name = "Rpt"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub



"NickHK" wrote:

> If the file name is variable and the user does not know what it is, then one
> way would be to use Application level Events (see
> http://www.cpearson.com/excel/AppEvent.htm), to check in the
> App_WorkbookOpen event if the file is a CSV. If there are no identifying
> structure/data you can use, ask the user "Is this the AS400 export file ?".
> If yes, insert a row at A:A and enter your header and formatting etc.
>
> NickHK
>
> "mr_bill" <(E-Mail Removed)> wrote in message
> news:A898DBBD-681C-49B8-913C-(E-Mail Removed)...
> > I have written a query on an AS400 that produces an output file. The
> output
> > file name is variable (usually the terminal that is logged on). Then
> there
> > is a program that copies the data from the output file and writes it to a
> > CSV. This program strips off the headers, and if I try to make the first
> > line of the file a header file, I have issues with the data types. The
> CSV
> > is the copied to the IFS portion of the AS400 which is like a Windows Data
> > share. I can map network drive to the share, etc, so the data is
> available
> > to users that have authority.
> >
> > The program then launches the STRPCCMD (an AS400 command to start a
> program
> > on the PC that envoked it) that opens the CSV. Since Excel is the program
> > that is set to open the CSV, the data is then displayed in Excel. The
> users
> > want to have the data in Excel, so that is good, the only problem is that
> > there are no headers and no formating to the data.
> >
> > I was wanting to create a Macro in the template that would open
> > automatically when launced and would import the CSV data. The name of the
> > CSV file is variable, and the user won't know what it is to select it from
> a
> > list, so it needs to happen without user interaction. I was working on
> > trying to get the macro to import the CSV based upon a TXT file that has
> the
> > path and file name.
> >
> > "Alan" wrote:
> >
> > > Might I ask, what program is creating the CSV file?
> > >
> > > You could set up a template with the header already in it, or the code
> to
> > > add the headers can be included in the macro.
> > >
> > > Yes, you can open a text file and use the value, (CSV source file
> > > directory), with a macro
> > >
> > > Workbooks.OpenText Filename:= _
> > > "YourDirectorPath&\YourFileLocater.txt", Origin:=437 _
> > > , StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1), _
> > > TrailingMinusNumbers:=True
> > >
> > > The path will be in A1. Setting a path variable, this can be used to
> open
> > > the CSV file then closing the text file:
> > >
> > > Dim csvPath As String
> > > csvPath = Range("A1").Value
> > > ActiveWorkbook.Close False
> > >
> > > Opens the CSV file.
> > >
> > > Workbooks.Open Filename:= csvPath
> > >
> > >
> > > The text file has to have the exact path & filename included.
> > >
> > > The rest is a matter of copying the data into the template or just
> adding a
> > > header. Not difficult.
> > >
> > >
> > > Alan
> > >
> > >
> > > "The only dumb question is a question left unasked."
> > >
> > >
> > > "mr_bill" wrote:
> > >
> > > > It would be difficult to get the program to put headers on, there are
> date
> > > > and numeric fields and the headers would be text.
> > > >
> > > > Would it be possible to create a template that would auto import the
> csv? I
> > > > could have the full path and file name written to a text file that
> could be
> > > > read by a macro to import the csv file.
> > > >
> > > > "Alan" wrote:
> > > >
> > > > > mr_bill,
> > > > >
> > > > > I would look into the "program that produces a CSV file" first to
> see if
> > > > > there are any header options.
> > > > >
> > > > > If not, since the program opens an instance of Excel, you could not
> > > > > automatically format it unless you have Excel to perform this on
> start up,
> > > > > which would affect every file you open.
> > > > >
> > > > > You could write a macro, and make it available on an Excel toolbar,
> that you
> > > > > could click on when the file opens. This macro could set the headers
> for you.
> > > > > The only drawback to this is if you have an instance of Excel open
> at the
> > > > > time the CSV instance opens, it will error on trying to open the
> Personal.xls
> > > > > file for a second time.
> > > > >
> > > > > Just some thoughts,
> > > > >
> > > > > Alan
> > > > >
> > > > >
> > > > > "The only dumb question is a question left unasked."
> > > > >
> > > > >
> > > > >
> > > > > "mr_bill" wrote:
> > > > >
> > > > > > I have a program that produces a CSV file without headers. When
> the program
> > > > > > finishes creating the file, it launches excel and opens the CSV.
> Is there a
> > > > > > way that I can have headers added to the file?
>
>
>

Excellent, glad you got it worked out. Hopefully I helped to remove the jam
in the cogs and got the wheels spinning again. I see from your code, you are
far more advanced than I. It seems I may still be coding in QBasic mode,
lol. I just started trying to reprogram myself by readings these threads. I
noticed your thread had been sitting there for a while, so I wanted to throw
something out there, if for no other reason, to bring your thread back
alive. I guess it worked. My code abilities are still in the stoneage. I
still uses "Select", can you imagine that, lol. Good luck in all your
ventures.

Alan


"The only dumb question is a question left unasked."
"Have you made someone smile today?"



"mr_bill" <(E-Mail Removed)> wrote in message
news:858E547D-25D8-497E-9403-(E-Mail Removed)...
> What I did was had the program that produces the CSV also write out a text
> file with the path and CSV file name. I created an auto execute macro in
> the
> template that has all the header data to read the TXT file and then import
> the CSV file.
>
>
> Private Sub Workbook_Open()
>
> Dim ConnString As String
> Open "location of file\qryfile.txt" For Input As #1
> Line Input #1, ConnString
> Close #1
>
> With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & ConnString _
> , Destination:=Range("A3"))
> .Name = "Rpt"
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = True
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = False
> .SaveData = True
> .AdjustColumnWidth = False
> .RefreshPeriod = 0
> .TextFilePromptOnRefresh = False
> .TextFilePlatform = 437
> .TextFileStartRow = 1
> .TextFileParseType = xlDelimited
> .TextFileTextQualifier = xlTextQualifierDoubleQuote
> .TextFileConsecutiveDelimiter = False
> .TextFileTabDelimiter = True
> .TextFileSemicolonDelimiter = False
> .TextFileCommaDelimiter = True
> .TextFileSpaceDelimiter = False
> .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
> 1,
> 1, 1, 1, 1, 1, 1, 1, 1, 1, _
> 1, 1)
> .TextFileTrailingMinusNumbers = True
> .Refresh BackgroundQuery:=False
> End With
>
> End Sub
>
>
>
> "NickHK" wrote:
>
>> If the file name is variable and the user does not know what it is, then
>> one
>> way would be to use Application level Events (see
>> http://www.cpearson.com/excel/AppEvent.htm), to check in the
>> App_WorkbookOpen event if the file is a CSV. If there are no identifying
>> structure/data you can use, ask the user "Is this the AS400 export file
>> ?".
>> If yes, insert a row at A:A and enter your header and formatting etc.
>>
>> NickHK
>>
>> "mr_bill" <(E-Mail Removed)> wrote in message
>> news:A898DBBD-681C-49B8-913C-(E-Mail Removed)...
>> > I have written a query on an AS400 that produces an output file. The
>> output
>> > file name is variable (usually the terminal that is logged on). Then
>> there
>> > is a program that copies the data from the output file and writes it to
>> > a
>> > CSV. This program strips off the headers, and if I try to make the
>> > first
>> > line of the file a header file, I have issues with the data types. The
>> CSV
>> > is the copied to the IFS portion of the AS400 which is like a Windows
>> > Data
>> > share. I can map network drive to the share, etc, so the data is
>> available
>> > to users that have authority.
>> >
>> > The program then launches the STRPCCMD (an AS400 command to start a
>> program
>> > on the PC that envoked it) that opens the CSV. Since Excel is the
>> > program
>> > that is set to open the CSV, the data is then displayed in Excel. The
>> users
>> > want to have the data in Excel, so that is good, the only problem is
>> > that
>> > there are no headers and no formating to the data.
>> >
>> > I was wanting to create a Macro in the template that would open
>> > automatically when launced and would import the CSV data. The name of
>> > the
>> > CSV file is variable, and the user won't know what it is to select it
>> > from
>> a
>> > list, so it needs to happen without user interaction. I was working on
>> > trying to get the macro to import the CSV based upon a TXT file that
>> > has
>> the
>> > path and file name.
>> >
>> > "Alan" wrote:
>> >
>> > > Might I ask, what program is creating the CSV file?
>> > >
>> > > You could set up a template with the header already in it, or the
>> > > code
>> to
>> > > add the headers can be included in the macro.
>> > >
>> > > Yes, you can open a text file and use the value, (CSV source file
>> > > directory), with a macro
>> > >
>> > > Workbooks.OpenText Filename:= _
>> > > "YourDirectorPath&\YourFileLocater.txt", Origin:=437 _
>> > > , StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0,
>> > > 1), _
>> > > TrailingMinusNumbers:=True
>> > >
>> > > The path will be in A1. Setting a path variable, this can be used to
>> open
>> > > the CSV file then closing the text file:
>> > >
>> > > Dim csvPath As String
>> > > csvPath = Range("A1").Value
>> > > ActiveWorkbook.Close False
>> > >
>> > > Opens the CSV file.
>> > >
>> > > Workbooks.Open Filename:= csvPath
>> > >
>> > >
>> > > The text file has to have the exact path & filename included.
>> > >
>> > > The rest is a matter of copying the data into the template or just
>> adding a
>> > > header. Not difficult.
>> > >
>> > >
>> > > Alan
>> > >
>> > >
>> > > "The only dumb question is a question left unasked."
>> > >
>> > >
>> > > "mr_bill" wrote:
>> > >
>> > > > It would be difficult to get the program to put headers on, there
>> > > > are
>> date
>> > > > and numeric fields and the headers would be text.
>> > > >
>> > > > Would it be possible to create a template that would auto import
>> > > > the
>> csv? I
>> > > > could have the full path and file name written to a text file that
>> could be
>> > > > read by a macro to import the csv file.
>> > > >
>> > > > "Alan" wrote:
>> > > >
>> > > > > mr_bill,
>> > > > >
>> > > > > I would look into the "program that produces a CSV file" first to
>> see if
>> > > > > there are any header options.
>> > > > >
>> > > > > If not, since the program opens an instance of Excel, you could
>> > > > > not
>> > > > > automatically format it unless you have Excel to perform this on
>> start up,
>> > > > > which would affect every file you open.
>> > > > >
>> > > > > You could write a macro, and make it available on an Excel
>> > > > > toolbar,
>> that you
>> > > > > could click on when the file opens. This macro could set the
>> > > > > headers
>> for you.
>> > > > > The only drawback to this is if you have an instance of Excel
>> > > > > open
>> at the
>> > > > > time the CSV instance opens, it will error on trying to open the
>> Personal.xls
>> > > > > file for a second time.
>> > > > >
>> > > > > Just some thoughts,
>> > > > >
>> > > > > Alan
>> > > > >
>> > > > >
>> > > > > "The only dumb question is a question left unasked."
>> > > > >
>> > > > >
>> > > > >
>> > > > > "mr_bill" wrote:
>> > > > >
>> > > > > > I have a program that produces a CSV file without headers.
>> > > > > > When
>> the program
>> > > > > > finishes creating the file, it launches excel and opens the
>> > > > > > CSV.
>> Is there a
>> > > > > > way that I can have headers added to the file?
>>
>>
>>