blazerunner1
New Member
- Joined
- Dec 20, 2013
- Messages
- 1
Hello all,
I am running excel 2010 and outlook 2010 on a Windows 7 machine at work. What I am attempting to do is write a macro that will open an .oft file and copy data from a spreadsheet into the .oft file. I have a very basic understanding of VBA and have had a hard time adapting any code I have been able to find.
So, very simply, what I am trying to do right now is write a macro to open an .oft file. Once i have the base code to do that, I want to be able to copy data from excel into the .oft file. Generally, where I run into a problem is when I get to the point in the code that actually directs it to open the file. As a basic example, right now the .oft is saved to V:\Users\name\Desktop\Dev\Test.oft.
So my code looks like this:
Sub OpenOFT()
'
'Email
'
'
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant
' File filters
Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
' Default Filter to *.*
FilterIndex = 3
' Set Dialog Caption
Title = "Select a File to Open"
' Select Start Drive & Path
ChDrive ("V")
ChDir ("V:\Users\name\Desktop\Dev\")
With Application
' Set File Name to selected File
Filename = .GetOpenFilename(Filter, FilterIndex, Title)
End With
' Exit on Cancel
If Filename = False Then
MsgBox "No file was selected."
Exit Sub
End If
' Open File
Workbooks.Open Filename
MsgBox Filename, vbInformation, "File Opened" ' This can be removed
End Sub
It hangs on the Filename part, and I am not clear on which part needs to be altered to make this work properly. It opens the dialog box and allows me to select the file I want to use, but ultimately what I want it to do is auto-select the file. Is there anything that can be taken out to simplify this? I only intend to use this macro to open .oft files at this time.
Thanks in advance!
I am running excel 2010 and outlook 2010 on a Windows 7 machine at work. What I am attempting to do is write a macro that will open an .oft file and copy data from a spreadsheet into the .oft file. I have a very basic understanding of VBA and have had a hard time adapting any code I have been able to find.
So, very simply, what I am trying to do right now is write a macro to open an .oft file. Once i have the base code to do that, I want to be able to copy data from excel into the .oft file. Generally, where I run into a problem is when I get to the point in the code that actually directs it to open the file. As a basic example, right now the .oft is saved to V:\Users\name\Desktop\Dev\Test.oft.
So my code looks like this:
Sub OpenOFT()
'
'
'
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant
' File filters
Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
' Default Filter to *.*
FilterIndex = 3
' Set Dialog Caption
Title = "Select a File to Open"
' Select Start Drive & Path
ChDrive ("V")
ChDir ("V:\Users\name\Desktop\Dev\")
With Application
' Set File Name to selected File
Filename = .GetOpenFilename(Filter, FilterIndex, Title)
End With
' Exit on Cancel
If Filename = False Then
MsgBox "No file was selected."
Exit Sub
End If
' Open File
Workbooks.Open Filename
MsgBox Filename, vbInformation, "File Opened" ' This can be removed
End Sub
It hangs on the Filename part, and I am not clear on which part needs to be altered to make this work properly. It opens the dialog box and allows me to select the file I want to use, but ultimately what I want it to do is auto-select the file. Is there anything that can be taken out to simplify this? I only intend to use this macro to open .oft files at this time.
Thanks in advance!