Macro to Prompt for File -- Neophite Needs HELP

[kilo1990]

I've been searching to try and fix this problem high & low on the
Internet, and I can't seem to get this sucker quite right. I know it's
an easy problem, but I have nil VBA knowledge, so this is nothing more
than a basic Macro.

I'm trying to automate my stock screening info as much as possible, and
I want to change the below Macro to prompt me for the CSV file name so
it's not linked to a certain file path. I tried to do the
GetFilenameOpen help dialogue in VBA, but I can't get it to work right.
ANY help would be greatly appreciated. Here's the code in the Macro:

Sub StockImport()
'
' StockImport Macro
' Macro recorded 09-12-2005 by
'
' Keyboard Shortcut: Ctrl+Shift+I
'
Workbooks.Open Filename:= _
"C:\Documents and Settings\MyName\My Documents\Money\Stock
Screener\Raw Screen Data.CSV"
Range("A2:N50").Select
Selection.Copy
Windows("Screen Research.xls").Activate
Range("A6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B4").Select
ActiveCell.FormulaR1C1 = [Now]
Range("B5").Select
Windows("Raw Screen Data.CSV").Activate
ActiveWindow.SmallScroll Down:=-33
ActiveWorkbook.Close
End Sub

I have lots more I want to do with this puppy, but I'm trying to take
baby steps...thanks SOOO much in advance (I've used alot of brain power
on this one)...

 

[kilo1990]

I know the part I need to chg is the "C:\Documents and
Settings\MyName\My Documents\Money\Stock Screener\Raw Screen Data.CSV"
-- I'd like to be able to point this to any CSV file (so it's not only
limited to my computer), but when I play around with it I end up
screwing up the entire macro...

 

[JE McGimpsey]

One way:

Public Sub StockImport()
Dim wbSource As Workbook
Dim sFileName As String
Dim rDest As Range
Application.ScreenUpdating = False
sFileName = Application.GetOpenFilename
If Len(sFileName) > 0 Then
With Workbooks("Screen Research.xls").Sheets(1)
Set rDest = .Range("A6")
With .Range("B4")
.NumberFormat = "dd mmmm yyyy hh:mm:ss"
.Value = Now
End With
End With
Set wbSource = Workbooks.Open(Filename:=sFileName, Format:=2)
With wbSource.Sheets(1).Range("A2:N50")
rDest.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
wbSource.Close
End If
Application.ScreenUpdating = True
End Sub

 

[kilo1990]

Do I replace my macro with everything you have up there? Sorry for the
silly question...

 

[kilo1990]

Ok, I did replace it, and it actually found the file!! But then the
debug command came up and highlighted the following row:

With Workbooks("Screen Research.xls").Sheet5.Select

Looks like it's confused on where to paste the data. The workbook has
about 15 worksheets in it, and the tab I want to paste the data is
"Last Import" So how do I specify that particular spreadsheet?

 

[JE McGimpsey]

If I understand you correctly,

With Workbooks("Screen Research.xls").Sheets("Last Import)

Note, there's no .Select at the end.

You very, very, very rarely have to select/activate cells in order to
address them - while the recorder uses selections, you should get out of
the habit of doing so as soon as you can.

 

[kilo1990]

Here's what I replaced it with:

With Workbooks("Screen Research.xls").Worksheet("Last
Import").Select

Here's the error it pops up:

Runtime error '9':
Subscript out of range

What does that mean? I feel like it's so close...

 

[kilo1990]

Thanks for the tip! But it still doesn't work. Same line selected,
with the same error given...bummer 'cause I thought that would fix it...

 

[Gord Dibben]

kilo

Try this if Screen Reasearch.xls not already active.

With Workbooks("Screen Research.xls")
.Activate
.Sheets("Last_Import").Select
End With


Gord Dibben Excel MVP

 

[JE McGimpsey]

What was the error?

 

[kilo1990]

Where would I stick that in?

 

[kilo1990]

JE,
A new error..which I consider progress! Here's the error:

Runtime error '438': Object doesn't support this property or method

Here's the highlighted line:

With Workbooks("Screen Research old.xls").Worksheet("Last
Import")

 

[JE McGimpsey]

No progress - my typo. Should be

...Worksheets(...

not

...Worksheet(...

 

[kilo1990]

THAT'S IT --- IT WORKS FLAWLESSLY!!!!! THANKS A BUNCH!!!