hardroader
New Member
- Joined
- Aug 1, 2013
- Messages
- 37
I have an existing macro that works but I would like to eliminate 1 step. I am downloading information from a website in the form of a comma separated file. I then copy that information into another spreadsheet called "Export.csv" and then I run my macro and it updates the information as necessary. I would like to eliminate the need to copy the information into the "Export.csv" file and use the file name that was used when the information was downloaded.
Code:
Sub MorningstarExport()
'
' 2014 RSI Tracker EL Module 9
' MorningstarExport Macro
'
' Option Key Cntl+Shift+E
' Before you run this marco, the proper information from the website needs to be downloaded and saved to a file named "Export.csv"
Dim UserEntry As String
' Select a worksheet to receive the data that is in the Export.csv file
Do
UserEntry = Application.InputBox("Enter the worksheet name to received the exported data.", _
"Destination Sheet", Type:=2)
If UserEntry = "False" Then Exit Sub ' User canceled
If Evaluate("ISREF('" & UserEntry & "'!A1)") Then Exit Do 'Test if worksheet name exists
MsgBox "Unable to find sheet named: " & UserEntry, , "Sheet Not Found"
Loop
Sheets(UserEntry).Select
' Copy/Paste the information from export.csv file
Workbooks("Export.csv").Sheets(1).Range("A1:M113").Copy _
Destination:=ActiveSheet.Range("AA484")
Range("AA484:AK486").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
UserDate = InputBox("Enter the date of this import.")
Range("AA486").Select
ActiveCell = UserDate
Range("L485").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[21]C[-3],"" "",R[21]C[-2])"
Range("L485").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = False
Selection.Font.Bold = True
Call JumpDueDiligencePage3
ExitSub:
End Sub
[CODE]
Thank you for taking the time to look at this.