Macro to Prompt for File -- Neophite Needs HELP
 

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

Macro to Prompt for File -- Neophite Needs HELP
 

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

Macro to Prompt for File -- Neophite Needs HELP
 

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 .com,
"kilo1990" 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)...

Macro to Prompt for File -- Neophite Needs HELP
 

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

Macro to Prompt for File -- Neophite Needs HELP
 

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?

Macro to Prompt for File -- Neophite Needs HELP
 

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 .com,
"kilo1990" 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?

Macro to Prompt for File -- Neophite Needs HELP
 

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

Macro to Prompt for File -- Neophite Needs HELP
 

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

Macro to Prompt for File -- Neophite Needs HELP
 

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

Macro to Prompt for File -- Neophite Needs HELP
 

What was the error?

In article .com,
"kilo1990" 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...

Macro to Prompt for File -- Neophite Needs HELP
 

Where would I stick that in?

Macro to Prompt for File -- Neophite Needs HELP
 

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

Macro to Prompt for File -- Neophite Needs HELP
 

No progress - my typo. Should be

...Worksheets(...

not

...Worksheet(...

In article . com,
"kilo1990" 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")

Macro to Prompt for File -- Neophite Needs HELP
 

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


JE McGimpsey wrote:
No progress - my typo. Should be

...Worksheets(...

not

...Worksheet(...

In article . com,
"kilo1990" 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")