automate importing

[jln via OfficeKB.com]

How can i automate importing of worksheets from cvs files into an open work
book? I was thinking of creating a button to contain a macro that would have
a pop up window that let the user choose the file to import, and have the
macro delete the old tab and rename the imported tab what ever i hard code it
to be. I could really use some help on this whole thing I know what i want
but dont know how to get there.

 

[Dave Peterson]

I'd start by recording a macro when I did it manually.

The post back if you need help tweaking the code.

 

[jln via OfficeKB.com]

Hey david thanks for the post.

I'd start by recording a macro when I did it manually.

The post back if you need help tweaking the code.

How can i automate importing of worksheets from cvs files into an open work
book? I was thinking of creating a button to contain a macro that would have

[quoted text clipped - 5 lines]

 

[jln via OfficeKB.com]

A record macro will not help me in this case becuase the file name changes
for each person thats why i want to use GetOpenFilename. Im just not sure how.

I'd start by recording a macro when I did it manually.

The post back if you need help tweaking the code.

How can i automate importing of worksheets from cvs files into an open work
book? I was thinking of creating a button to contain a macro that would have

[quoted text clipped - 5 lines]

 

[Dave Peterson]

Recording the macro will get a lot of the code.

Then you can start tweaking it...

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.Open Filename:=myFileName '....rest of recorded code here!

End Sub

A record macro will not help me in this case becuase the file name changes
for each person thats why i want to use GetOpenFilename. Im just not sure how.

I'd start by recording a macro when I did it manually.

The post back if you need help tweaking the code.

How can i automate importing of worksheets from cvs files into an open work
book? I was thinking of creating a button to contain a macro that would have

[quoted text clipped - 5 lines]

 

[jln via OfficeKB.com]

Ok that gave me a good start i have it opening the file then coping. My next
step would be how do i make it activate the right workbook. The work book
names are always different.

Recording the macro will get a lot of the code.

Then you can start tweaking it...

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.Open Filename:=myFileName '....rest of recorded code here!

End Sub

A record macro will not help me in this case becuase the file name changes
for each person thats why i want to use GetOpenFilename. Im just not sure how.

[quoted text clipped - 12 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1

 

[Dave Peterson]

The .csv file that you just opened will be the activeworkbook. But you could
use a variable to represent that workbook, too:

Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim myWkbk as workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

set mywkbk = Workbooks.Open(Filename:=myFileName)
'....rest of recorded code here!

End Sub

Then you can refer to that CSV workbook file with:

mywkbk.worksheets(1).range("a1").value = "whatever you wanted"

Ok that gave me a good start i have it opening the file then coping. My next
step would be how do i make it activate the right workbook. The work book
names are always different.

Recording the macro will get a lot of the code.

Then you can start tweaking it...

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.Open Filename:=myFileName '....rest of recorded code here!

End Sub

A record macro will not help me in this case becuase the file name changes
for each person thats why i want to use GetOpenFilename. Im just not sure how.

[quoted text clipped - 12 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1

 

[jln via OfficeKB.com]

Ok im getting closer

here is what i have

Problem is on this line
With ActiveSheet.QueryTables.Add(Connection:=myFileName, Destination:=Range
("A1")) '

Option Explicit
Sub Mgicimport()


'
' Macro6 Macro
' Macro recorded 11/6/2006 by J928052
'

'


Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV",
_
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.Open Filename:=myFileName '....rest of recorded code here!

ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=myFileName, Destination:
=Range("A1")) ' Problem is HERE
.Name = ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With




End Sub

 

[Dave Peterson]

The routine I was suggesting didn't use a querytable. It would just open the
file. Then you could copy and paste to whereever you wanted to. (Delete any
existing data first if you want.)

 

[jln via OfficeKB.com]

Thanks dave for the help with the error . I have 2 things left that i needed
it to do and ill be all done.

1. how do i make the right work book active? I have tried doing it as a
copy/paste and as this query way but it goes to the work sheet that is being
opened not the one that was open.
2. How do I Close the cvs file after import? Should i just use a .close
statment?

 

[Dave Peterson]

dim CurWks as worksheet
dim myWkbk as workbook

set curwks = activesheet 'or whatever you want it to be
'code to get the name and open the .csv file

mywkbk.worksheets(1).usedrange.copy _
destination:=curwks.range("a1")
(Paste into A1 of the original sheet????)

'close the .csv file without making changes
mywkbk.close savechanges:=false