getobject

B

Brennan

is there a way to use the getobject function to bring up a box that allows
you to search for the file that you want to get? Thanks all

Brennan
 
B

Brennan

Thanks Arvin,

I have to admit that I am somewhat new to access and I don't understand how
this fits into my question. Could you provide some further direction?
Thanks so much for your time.

Brennan
 
A

Arvin Meyer [MVP]

Brennan said:
Thanks Arvin,

I have to admit that I am somewhat new to access and I don't understand
how
this fits into my question. Could you provide some further direction?
Thanks so much for your time.

Brennan
Click to expand...
 
A

Arvin Meyer [MVP]

Sorry for the empty post:

Put all the code in a standard module. Then:

Put a button and a text box on a form:

Private Sub cmdBrowse_Click()
On Error Resume Next
Me!txtFileName = GetOpenFile()
End Sub
 
B

Brennan

HI Arvin,

Thanks for your help. I followed your suggestion and then I was able to
understand how to do this. The example on the website had a filter for excel
so I used that in my code below after I understood how to do it. It worked
one time, but after that I got the following error. Could you take a look
and let me know what I am doing incorrectly? Thanks again for the help.

Run Time Error '91':
Object variable or with block variable not set

Here is my current code:
Private Sub Command87_Click()
Dim xlobject As Object, xlsheet As Object
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)


Set xlobject = GetObject(strInputFileName)
Set xlsheet = xlobject.Application.activeworkbook.sheet1
With xlsheet
.range("b1").Value = "Presale"
.range("c1").Value = "Postsale"
.range("d1").Value = "Total P&L"
.range("a2").Value = "Consulting Revenue"
.range("b2").Value = PreCR
.range("c2").Value = PostCR
.range("d2").Value = CR
.range("a3").Value = "Customer Education"
.range("b3").Value = PreCE
.range("c3").Value = PostCE
.range("d3").Value = CE
.range("a4").Value = "Total Revenue"
.range("b4").Value = PreTR
.range("c4").Value = PostTR
.range("d4").Value = TR
.Cells(1, 1).Font.Size = 25


End With

Set xlobject = Nothing


End Sub
 
A

Arvin Meyer [MVP]

I see several errors. Replace your code with this:

Private Sub cmdExcel_Click()
Dim xlobject As Object, xlsheet As Object
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

Set xlobject = CreateObject("Excel.Application")
With xlobject.Application
.Visible = True
'Open the Workbook
.Workbooks.Open strInputFileName
End With
Set xlsheet = xlobject.Application.ActiveWorkbook.Worksheets(1)
With xlsheet
.Range("b1").Value = "Presale"
.Range("c1").Value = "Postsale"
.Range("d1").Value = "Total P&L"
.Range("a2").Value = "Consulting Revenue"
.Range("b2").Value = "PreCR"
.Range("c2").Value = "PostCR"
.Range("d2").Value = "CR"
.Range("a3").Value = "Customer Education"
.Range("b3").Value = "PreCE"
.Range("c3").Value = "PostCE"
.Range("d3").Value = "CE"
.Range("a4").Value = "Total Revenue"
.Range("b4").Value = "PreTR"
.Range("c4").Value = "PostTR"
.Range("d4").Value = "TR"
.Cells(1, 1).Font.Size = 25


End With

Set xlobject = Nothing

End Sub
 
Top