Hello,

I am very new to programming with VBA in Excel, but am trying to learn
as much as possible, and these groups are certainly an excellent
resource for that. So here is my problem.

Each day I need to:
1) Open up an Excel file linked to several other files
2) Break the links to the other files
3) Delete a column
4) Save the file as an .xls file with the current date
5) Save a second copy as a .csv file (with same file name each day)
6) Upload .csv file to our database

I've been trying to break this down into steps and write code for each
step as I learn it. So far I've done steps 2-5 with the code below.

Sub SavePriceUpload()

'

' Comment: Save as Date

' Created on 7/27/2006

'

Sheets("Sheet1").Activate

Cells.Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _

:=False, Transpose:=False



Columns("H:H").Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Delete Shift:=xlToLeft



dtmDate = Date



strMonth = Month(Date)

strDay = Day(Date)

strYear = Right(Year(Date), 2)



ActiveWorkbook.SaveAs Filename:= _

"F:\PRICEUPLD\Price\PriceUpload" & strMonth & "-" & strDay & "-" &
strYear & ".xls"



ActiveWorkbook.SaveAs Filename:= _

"F:\PRICEUPLD\price.csv"



End Sub

Everything seems to work great, except when I try to upload the .csv
file to my database it fails. If I open the .csv file in a text editor
I can see that there is a bunch of other info that doesn't show up when
I open the csv file in Excel, like my VBA code. So any ideas how I can
do this without the extraneous data. Let me know if I'm going about
this in entirely the wrong way.

Thanks,
Dylan

I can't imagine what the extraneous data might be.

But to have more control you might look at Chip Pearson's code.

http://www.cpearson.com/excel/imptext.htm import/export text files

--
Regards,
Tom Ogilvy


" wrote:

Hello,

I am very new to programming with VBA in Excel, but am trying to learn
as much as possible, and these groups are certainly an excellent
resource for that. So here is my problem.

Each day I need to:
1) Open up an Excel file linked to several other files
2) Break the links to the other files
3) Delete a column
4) Save the file as an .xls file with the current date
5) Save a second copy as a .csv file (with same file name each day)
6) Upload .csv file to our database

I've been trying to break this down into steps and write code for each
step as I learn it. So far I've done steps 2-5 with the code below.

Sub SavePriceUpload()

'

' Comment: Save as Date

' Created on 7/27/2006

'

Sheets("Sheet1").Activate

Cells.Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _

:=False, Transpose:=False



Columns("H:H").Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Delete Shift:=xlToLeft



dtmDate = Date



strMonth = Month(Date)

strDay = Day(Date)

strYear = Right(Year(Date), 2)



ActiveWorkbook.SaveAs Filename:= _

"F:\PRICEUPLD\Price\PriceUpload" & strMonth & "-" & strDay & "-" &
strYear & ".xls"



ActiveWorkbook.SaveAs Filename:= _

"F:\PRICEUPLD\price.csv"



End Sub

Everything seems to work great, except when I try to upload the .csv
file to my database it fails. If I open the .csv file in a text editor
I can see that there is a bunch of other info that doesn't show up when
I open the csv file in Excel, like my VBA code. So any ideas how I can
do this without the extraneous data. Let me know if I'm going about
this in entirely the wrong way.

Thanks,
Dylan

Thanks Tom,

The extra data appears to be formatting data along with the VBA code.
It seems that the file is not truly getting saved as a .csv file, since
when I look at it in notepad there are no commas.

Thanks for the link to Chris Pearson's code. I need to go through it
in more detail, but it looks like I'll be able to modify it for my
needs.

Dylan

I appreciate the link to Chris Pearson's code.

Tom Ogilvy wrote:
I can't imagine what the extraneous data might be.

But to have more control you might look at Chip Pearson's code.

http://www.cpearson.com/excel/imptext.htm import/export text files

--
Regards,
Tom Ogilvy


" wrote:

Hello,

I am very new to programming with VBA in Excel, but am trying to learn
as much as possible, and these groups are certainly an excellent
resource for that. So here is my problem.

Each day I need to:
1) Open up an Excel file linked to several other files
2) Break the links to the other files
3) Delete a column
4) Save the file as an .xls file with the current date
5) Save a second copy as a .csv file (with same file name each day)
6) Upload .csv file to our database

I've been trying to break this down into steps and write code for each
step as I learn it. So far I've done steps 2-5 with the code below.

Sub SavePriceUpload()

'

' Comment: Save as Date

' Created on 7/27/2006

'

Sheets("Sheet1").Activate

Cells.Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _

:=False, Transpose:=False



Columns("H:H").Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Delete Shift:=xlToLeft



dtmDate = Date



strMonth = Month(Date)

strDay = Day(Date)

strYear = Right(Year(Date), 2)



ActiveWorkbook.SaveAs Filename:= _

"F:\PRICEUPLD\Price\PriceUpload" & strMonth & "-" & strDay & "-" &
strYear & ".xls"



ActiveWorkbook.SaveAs Filename:= _

"F:\PRICEUPLD\price.csv"



End Sub

Everything seems to work great, except when I try to upload the .csv
file to my database it fails. If I open the .csv file in a text editor
I can see that there is a bunch of other info that doesn't show up when
I open the csv file in Excel, like my VBA code. So any ideas how I can
do this without the extraneous data. Let me know if I'm going about
this in entirely the wrong way.

Thanks,
Dylan

You are correct - I didn't look closely enough.

you have to do more than specify the extension

ActiveWorkbook.SaveAs Filename:= _
"F:\PRICEUPLD\price.csv", FileFormat:=xlCSV


--
Regards,
Tom Ogilvy


" wrote:

Thanks Tom,

The extra data appears to be formatting data along with the VBA code.
It seems that the file is not truly getting saved as a .csv file, since
when I look at it in notepad there are no commas.

Thanks for the link to Chris Pearson's code. I need to go through it
in more detail, but it looks like I'll be able to modify it for my
needs.

Dylan

I appreciate the link to Chris Pearson's code.

Tom Ogilvy wrote:
I can't imagine what the extraneous data might be.

But to have more control you might look at Chip Pearson's code.

http://www.cpearson.com/excel/imptext.htm import/export text files

--
Regards,
Tom Ogilvy


" wrote:

Hello,

I am very new to programming with VBA in Excel, but am trying to learn
as much as possible, and these groups are certainly an excellent
resource for that. So here is my problem.

Each day I need to:
1) Open up an Excel file linked to several other files
2) Break the links to the other files
3) Delete a column
4) Save the file as an .xls file with the current date
5) Save a second copy as a .csv file (with same file name each day)
6) Upload .csv file to our database

I've been trying to break this down into steps and write code for each
step as I learn it. So far I've done steps 2-5 with the code below.

Sub SavePriceUpload()

'

' Comment: Save as Date

' Created on 7/27/2006

'

Sheets("Sheet1").Activate

Cells.Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _

:=False, Transpose:=False



Columns("H:H").Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Delete Shift:=xlToLeft



dtmDate = Date



strMonth = Month(Date)

strDay = Day(Date)

strYear = Right(Year(Date), 2)



ActiveWorkbook.SaveAs Filename:= _

"F:\PRICEUPLD\Price\PriceUpload" & strMonth & "-" & strDay & "-" &
strYear & ".xls"



ActiveWorkbook.SaveAs Filename:= _

"F:\PRICEUPLD\price.csv"



End Sub

Everything seems to work great, except when I try to upload the .csv
file to my database it fails. If I open the .csv file in a text editor
I can see that there is a bunch of other info that doesn't show up when
I open the csv file in Excel, like my VBA code. So any ideas how I can
do this without the extraneous data. Let me know if I'm going about
this in entirely the wrong way.

Thanks,
Dylan

Excellent! Thanks Tom.