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


In article <[email protected]>,
"kilo1990" <[email protected]> wrote:

> 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]

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.


In article <[email protected]>,
"kilo1990" <[email protected]> wrote:

> 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?

[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

On 17 Dec 2005 11:33:53 -0800, "kilo1990" <[email protected]> wrote:

>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...

[JE McGimpsey]

What was the error?

In article <[email protected]>,
"kilo1990" <[email protected]> wrote:

> 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...

[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(...

In article <[email protected]>,
"kilo1990" <[email protected]> wrote:

> 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")

[kilo1990]

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


JE McGimpsey wrote:
> No progress - my typo. Should be
>
> ...Worksheets(...
>
> not
>
> ...Worksheet(...
>
> In article <[email protected]>,
> "kilo1990" <[email protected]> wrote:
>
> > 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")