Notepad to embebed excel

[tizzodizzo]

I am using powerpoint VBA to write a macro.

I am trying to copy a line from notepad and paste it into an embeded excel spreadsheet that converts it into a chart. I only need one line from the notepad, luckily it is the last line.

I then want to resize the chart to fit the slide and save off into a jpg. I am able to resize and save but can't figure out how to import the line that I need.

I have minimal coding experience. Please help.

Thanks
Tizzodizzo

 

[PHV]

notepad is not scriptable nor automatable.
Why not reading the file by code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tizzodizzo]

Would it be better to open the file in Excel? Then try to copy the line I need?

 

[PHV]

Why not open the file with VBA ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bong]

It's simple enough to get the last line:

open [red]filename[/red] for input as #1
while not(eof(1)): line input #1,a: wend
close #1

Now the variable, a, is the last line.

_________________
Bob Rashkin

 

[tizzodizzo]

Thanks and I am able to open the file using VBA, I cant copy from the file or paste it in my embeded excel.

 

[PHV]

What is your actual code and where in it are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tizzodizzo]

Here is the current Code I have so far. I run into problems at the Z's, I just inserted them as a place marker. A lot is remed out.

Sub Import
Dim xlApp As Object
Dim strDataLine As String
Dim strORDPath As String
Dim xlWrkBook As Object, xlWrkSheet As Object
Dim lCurrSlide As Long

Set xlApp = CreateObject("Excel.Application")

Dim sInputDate As String
Dim sDirPath As String

sInputDate = InputBox(Prompt:="Enter the scenario date (mmm/dd/yyyy).", Title:="Scenario Date", Default:=Format(Now - 1, "mmm/dd/yyyy"))

'Calculate JDay
intYear = year(CDate(sInputDate))
intDays = DateDiff("d", DateSerial(intYear, 1, 0), CDate(sInputDate))
sInputJdate = Format(intDays, "000")

' Set the Daily folder
sDirPath = "Z:\ORD(Edited)\" & Right(sInputDate, 4) & "\" & Right(sInputDate, 4) & "_" & sInputJdate

' Open the Excel workbook
Set xlWrkBook = xlApp.Workbooks.Open(FileName:=sDirPath + "\" & Right(sInputDate, 4) & "_" & sInputJdate & "_v02.ord")

ZZZZZ
Open FileName For Input As #1
While Not (EOF(1)): Line Input #1, a: Wend
Close #1

'ReadORDFile()
Rem Read the ORD(edited).ord file
fn = FreeFile 'opens next file buffer, ussually set to 1
Open sDirPath For Input As #fn 'this opens the ASCII file as input in buffer #fn

Rem Reset row index to 2 for counting outages. Row 1 is for the title.
intRowIndex = 2





Rem Get the current ORD, build path, and open the current ORD.
fn = FreeFile
Open strSOFDrive & strSOFPath For Input As #fn

'Sheets("sheetname").Select

Do While Not EOF(fn)
Line Input #fn, strDataLine
strLineSplit() = Split(strDataLine, Chr(9))

If (strLineSplit(0) Like "*RMS") Then
If strLineSplit(7) = "FCSTDV" Or strLineSplit(7) = "FCSTMX" Then
'Sheets("Outages").Select

'Cells(intRowIndex, 1).Select
ActiveCell.FormulaR1C1 = strLineSplit(1) 'PRN number

'Cells(intRowIndex, 2).Select
ActiveCell.FormulaR1C1 = strLineSplit(3) 'SVN number



intRowIndex = intRowIndex + 1 'increment for next row
End If
End If
Loop
Close fn

'Turn on the screen updates
'Application.ScreenUpdating = True


' Copy ORD DATA picture onto the clipboard
'xlApp.ActiveWindow.Activate
'Sheets("Right(sInputDate, 4) & char95 & sInputJdate & "_v02").Select
'2008_284_v02
'ActiveSheet.ChartObjects("Chart 1").Activate
'ActiveChart.CopyPicture Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture



' Paste to PowerPoint and Resize
'ActiveWindow.View.GotoSlide Index:=2
'ActiveWindow.View.Paste
'With ActiveWindow.Selection.ShapeRange
'.Fill.Transparency = 0#
'.LockAspectRatio = msoFalse
'.Height = 539.88
'.Width = 719.75
'.Left = 0#
'.Top = 0#
'End With




' Close open Workbook
'xlWrkBook.Close (False)



'Close the openworkbook without saving changes
'xlWrkBook.Close (False)
'xlApp.Quit

'Set xlApp = Nothing
'Set xlWrkBook = Nothing

'MsgBox ("Import is complete. Version 1.3")

' End Sub

 

[ettienne]

What is FileName in your line
Open FileName For Input As #1 ?

 

[tizzodizzo]

The file name will change each time the Macro is run. And is contengent on the input date.

sDirPath + "\" & Right(sInputDate, 4) & "_" & sInputJdate & "_v02.ord")

 

[ettienne]

The point is you do not declare or set FileName anywhere.
FileName is "" when the line is executed.

 

[tizzodizzo]

I am now running into the problem where it is flagging out on Sheets("Data").Select. It doesn't like the word Sheets.

 

[combo]

When you work outside excel, each of its objects (as Sheets(..), ActiveCell etc) should be called after excel application reference, i.e:
xlApp.Sheets("Right(sInputDate, 4) & char95 & sInputJdate & "_v02").Select



combo

 

[tizzodizzo]

Is that the same when I want to select individual cells?

 

[combo]

If you do not have reference to 'Excel' library set, powerpoint will not understand 'Range' or 'Cells' call. If a class with this name exists in referenced libraries, the code will assume that you try to refer to it.
So there is a risk that the code will use wrong object.
Anyway, there is a good practice to define reference precisely.

combo

 

[tizzodizzo]

Thank you all for the advice. I have been able to complete my Macro. All of your guys advice helped.

Again thank You Very much.