Use Excel to work on another Excel workbook?

T

Toby Erkson

Web site recommendations for the given subject line? Jon Peltier's site give
good examples for PowerPoint but I'm a VBA beginner/intermediate so actual
Excel examples would be nice.
 
F

Frank Kabel

Hi
you may provide some more details what you're trying to achieve
exactly?
 
T

Toby Erkson

I get a .CSV file from a Siebel db. Some of the fields contain double-quotes
(") because the sales agents put them in when they shouldn't and those need to
be removed before the .csv file is imported to MS Access -- getting IT to
strip them out is, well, let's just say converting the Pope to Buddhism would
be much easier.

Because my Access VBA is extremely weak I decided to use Excel since it does
the job perfectly. I open the .csv file in Excel, perform a Replace where I
replace all instances of double-quotes with nothing. Save the .csv file and
it's ready for importing by Access and I get no Importfile_errors.

I want to automate the above and figure Excel VBA can do it, I just need to
figure out the Excel object stuff (something I'm weak on). What I'm guessing
is that I open the main Excel workbook, whereupon the VBA would create (get?)
a second Excel object that would import the .csv file, fix it, re-save it and
close, then the main Excel workbook would quit itself. I can do everything
except create (or get?) the second Excel object that would be importing and
saving the .csv file.
 
D

Dave Peterson

I think I'd start a new workbook, turn on the macro recorder and record a macro
when I did it manually.

If the .CSV file never changes names, you may not even have to modify your macro
(unless you want to make it nicer).

Then whenever you need to convert your .csv file, you can just open this
workbook (with the recorded macro) and rerun the macro.

I like to put a button from the forms toolbar on the first worksheet and assign
it to that recorded macro. Add some instructions and you're off and running.

If the name of the .CSV file changes, you can modify your macro to prompt the
user (YOU!) to point at the correct .csv file.

Option Explicit
Sub testme()

Dim myFileName As String
Dim wkbk As Workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.csv")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.Open Filename:=myFileName
set wkbk = activeworkbook

'....rest of recorded code

end sub
 
T

Toby Erkson

Dave is on-line :)

Thanks Dave, I'll look into your suggestion...for some reason I'm thinking
it won't work but I'll try it. The process is automated so it's never
touched by a human (I use a scheduler named "ad Tempus" by Arcana
Development, http://www.arcanadev.com/. I recommend it) and the file name
will never change because Access (which is also automated) needs to be able
to find the file.
 
T

Toby Erkson

Heh, yeah, I do use the recorder quite a bit, mostly because VB isn't like the
BASIC I grew up on (Apple & Commodore). Good to know I'm not the only one.

I followed your suggestion, add a couple extra lines (save, close, alerts) and
it worked the first time -- pretty darn diddly simple! Thank you for the
assistance (and Frank for prodding ;-)

Here's the final result (names have been changed to protect the innocent):
--------------------------------------------------------
Private Sub Workbook_Open()
'Because double-quotes in the Siebel export file can cause import problems for
Access
'they are stripped from the file.

Application.DisplayAlerts = False
'Import the Siebel CSV file
Workbooks.OpenText Filename:= _
"\\server\foo\leads_output.csv", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
'Replace all occurrences of double-quotes with nothing (erase them)
Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
ActiveWorkbook.Save 'Save the modified CSV file
ActiveWorkbook.Close 'Close it
Application.DisplayAlerts = True
Application.Quit 'Exit this workbook
End Sub
 
D

Dave Peterson

Ned would be very proud <vbg>.



Toby said:
Heh, yeah, I do use the recorder quite a bit, mostly because VB isn't like the
BASIC I grew up on (Apple & Commodore). Good to know I'm not the only one.

I followed your suggestion, add a couple extra lines (save, close, alerts) and
it worked the first time -- pretty darn diddly simple! Thank you for the
assistance (and Frank for prodding ;-)

Here's the final result (names have been changed to protect the innocent):
--------------------------------------------------------
Private Sub Workbook_Open()
'Because double-quotes in the Siebel export file can cause import problems for
Access
'they are stripped from the file.

Application.DisplayAlerts = False
'Import the Siebel CSV file
Workbooks.OpenText Filename:= _
"\\server\foo\leads_output.csv", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
'Replace all occurrences of double-quotes with nothing (erase them)
Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
ActiveWorkbook.Save 'Save the modified CSV file
ActiveWorkbook.Close 'Close it
Application.DisplayAlerts = True
Application.Quit 'Exit this workbook
End Sub
Click to expand...
 
Top