Dim an unbound textbox?

[Simon]

I am trying to copy an excel .xlt file and create a .xls file from the
OnClick event of a Command button using vba.
If I use a fixed path for the .xls this is fine. However, I would like to
automatically default to the Clients individual file and allow the user to
give it a name. I have an unbound textbox (Text12) on my form which has its
data set to DLookup... for the path and a second unbound textbox (Text4) for
the user to type a name. I have changed the code a thousand times but can't
get it right. Can anyone help?
The latest attempt is this,
Dim xl As Object
Dim SourceFile As String
Dim ClientFile As String
Dim DestinationFile As String

SourceFile = "D:\Documents and Settings\Simon\Desktop\ValSumTemp.xlt"
ClientFile = [Text12]
DestinationFile = ClientFile & Me![Text4] & Me![Text10]

FileCopy SourceFile, DestinationFile

I am using Access and Excel 2003 and any help would be gratefully received.

 

[Arvin Meyer [MVP]]

Use this function to determine your path:

http://www.mvps.org/access/api/api0001.htm

You'd set your textbox's controlsource to:

=GetOpenFile()

 

[Simon]

Thanks for that Arvin, but what I really want to do is force the file path
which will change from client to client and just allow the user to give the
doc a name. Hence Text12 which is invisible is the path and the user enters a
name in Text4. Using the GetOpenFile method allows the user more options than
this.

Use this function to determine your path:

http://www.mvps.org/access/api/api0001.htm

You'd set your textbox's controlsource to:

=GetOpenFile()
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I am trying to copy an excel .xlt file and create a .xls file from the
OnClick event of a Command button using vba.
If I use a fixed path for the .xls this is fine. However, I would
like to automatically default to the Clients individual file and
allow the user to give it a name. I have an unbound textbox (Text12)
on my form which has its data set to DLookup... for the path and a
second unbound textbox (Text4) for the user to type a name. I have
changed the code a thousand times but can't get it right. Can anyone
help?
The latest attempt is this,
Dim xl As Object
Dim SourceFile As String
Dim ClientFile As String
Dim DestinationFile As String

SourceFile = "D:\Documents and Settings\Simon\Desktop\ValSumTemp.xlt"
ClientFile = [Text12]
DestinationFile = ClientFile & Me![Text4] & Me![Text10]

FileCopy SourceFile, DestinationFile

I am using Access and Excel 2003 and any help would be gratefully
received.

 

[Douglas J. Steele]

So what values are in the text boxes, and what does DestinationFile contain
after the concatenation?

Before your FileCopy statement, put:

Debug.Print "[Text12] = " & Me![Text12]
Debug.Print "[Text4] = " & Me![Text4]
Debug.Print "[Text10] = " & Me![Text10]
Debug.Print "DestinationFile = " & DestinationFile

Go to the Immediate window (Ctrl-G), and check what's printed.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for that Arvin, but what I really want to do is force the file path
which will change from client to client and just allow the user to give
the
doc a name. Hence Text12 which is invisible is the path and the user
enters a
name in Text4. Using the GetOpenFile method allows the user more options
than
this.

Use this function to determine your path:

http://www.mvps.org/access/api/api0001.htm

You'd set your textbox's controlsource to:

=GetOpenFile()
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I am trying to copy an excel .xlt file and create a .xls file from the
OnClick event of a Command button using vba.
If I use a fixed path for the .xls this is fine. However, I would
like to automatically default to the Clients individual file and
allow the user to give it a name. I have an unbound textbox (Text12)
on my form which has its data set to DLookup... for the path and a
second unbound textbox (Text4) for the user to type a name. I have
changed the code a thousand times but can't get it right. Can anyone
help?
The latest attempt is this,
Dim xl As Object
Dim SourceFile As String
Dim ClientFile As String
Dim DestinationFile As String

SourceFile = "D:\Documents and Settings\Simon\Desktop\ValSumTemp.xlt"
ClientFile = [Text12]
DestinationFile = ClientFile & Me![Text4] & Me![Text10]

FileCopy SourceFile, DestinationFile

I am using Access and Excel 2003 and any help would be gratefully
received.

 

[Simon]

I have a .xlt with a macro in it. All I want to do is copy the .xlt to a .xls
and then transfer a number of queries using the DoCmd.TransferSpreadsheet
method to the new .xls.
I have temporarily removed the TransferSpreadsheet code just to try to get
the doc copied. This all works fine if I fix the DestinationFile in the code
ie "D:\...." but I want to pre-determine the folder and let the user just
name it. Hence the 2 text boxes (1 invisible to store the folder and the 2nd
visible to allow a user defined name).
Thanks for your continued help.

So what values are in the text boxes, and what does DestinationFile contain
after the concatenation?

Before your FileCopy statement, put:

Debug.Print "[Text12] = " & Me![Text12]
Debug.Print "[Text4] = " & Me![Text4]
Debug.Print "[Text10] = " & Me![Text10]
Debug.Print "DestinationFile = " & DestinationFile

Go to the Immediate window (Ctrl-G), and check what's printed.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for that Arvin, but what I really want to do is force the file path
which will change from client to client and just allow the user to give
the
doc a name. Hence Text12 which is invisible is the path and the user
enters a
name in Text4. Using the GetOpenFile method allows the user more options
than
this.

Use this function to determine your path:

http://www.mvps.org/access/api/api0001.htm

You'd set your textbox's controlsource to:

=GetOpenFile()
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Simon wrote:
I am trying to copy an excel .xlt file and create a .xls file from the
OnClick event of a Command button using vba.
If I use a fixed path for the .xls this is fine. However, I would
like to automatically default to the Clients individual file and
allow the user to give it a name. I have an unbound textbox (Text12)
on my form which has its data set to DLookup... for the path and a
second unbound textbox (Text4) for the user to type a name. I have
changed the code a thousand times but can't get it right. Can anyone
help?
The latest attempt is this,
Dim xl As Object
Dim SourceFile As String
Dim ClientFile As String
Dim DestinationFile As String

SourceFile = "D:\Documents and Settings\Simon\Desktop\ValSumTemp.xlt"
ClientFile = [Text12]
DestinationFile = ClientFile & Me![Text4] & Me![Text10]

FileCopy SourceFile, DestinationFile

I am using Access and Excel 2003 and any help would be gratefully
received.

 

[Douglas J. Steele]

I repeat:

So what values are in the text boxes, and what does DestinationFile contain
after the concatenation?

Before your FileCopy statement, put:

Debug.Print "[Text12] = " & Me![Text12]
Debug.Print "[Text4] = " & Me![Text4]
Debug.Print "[Text10] = " & Me![Text10]
Debug.Print "DestinationFile = " & DestinationFile

Go to the Immediate window (Ctrl-G), and check what's printed.

It's pretty hard for us to help you if you won't tell us what's wrong!


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a .xlt with a macro in it. All I want to do is copy the .xlt to a
.xls
and then transfer a number of queries using the DoCmd.TransferSpreadsheet
method to the new .xls.
I have temporarily removed the TransferSpreadsheet code just to try to get
the doc copied. This all works fine if I fix the DestinationFile in the
code
ie "D:\...." but I want to pre-determine the folder and let the user just
name it. Hence the 2 text boxes (1 invisible to store the folder and the
2nd
visible to allow a user defined name).
Thanks for your continued help.

So what values are in the text boxes, and what does DestinationFile
contain
after the concatenation?

Before your FileCopy statement, put:

Debug.Print "[Text12] = " & Me![Text12]
Debug.Print "[Text4] = " & Me![Text4]
Debug.Print "[Text10] = " & Me![Text10]
Debug.Print "DestinationFile = " & DestinationFile

Go to the Immediate window (Ctrl-G), and check what's printed.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for that Arvin, but what I really want to do is force the file
path
which will change from client to client and just allow the user to give
the
doc a name. Hence Text12 which is invisible is the path and the user
enters a
name in Text4. Using the GetOpenFile method allows the user more
options
than
this.

:

Use this function to determine your path:

http://www.mvps.org/access/api/api0001.htm

You'd set your textbox's controlsource to:

=GetOpenFile()
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Simon wrote:
I am trying to copy an excel .xlt file and create a .xls file from
the
OnClick event of a Command button using vba.
If I use a fixed path for the .xls this is fine. However, I would
like to automatically default to the Clients individual file and
allow the user to give it a name. I have an unbound textbox (Text12)
on my form which has its data set to DLookup... for the path and a
second unbound textbox (Text4) for the user to type a name. I have
changed the code a thousand times but can't get it right. Can anyone
help?
The latest attempt is this,
Dim xl As Object
Dim SourceFile As String
Dim ClientFile As String
Dim DestinationFile As String

SourceFile = "D:\Documents and
Settings\Simon\Desktop\ValSumTemp.xlt"
ClientFile = [Text12]
DestinationFile = ClientFile & Me![Text4] & Me![Text10]

FileCopy SourceFile, DestinationFile

I am using Access and Excel 2003 and any help would be gratefully
received.

 

[Simon]

Thanks Doug. Text12 is quite a long file path and I had missed a space in it.
I'm really annoyed with that and apologise for wasting your time.
Simon

I repeat:

So what values are in the text boxes, and what does DestinationFile contain
after the concatenation?

Before your FileCopy statement, put:

Debug.Print "[Text12] = " & Me![Text12]
Debug.Print "[Text4] = " & Me![Text4]
Debug.Print "[Text10] = " & Me![Text10]
Debug.Print "DestinationFile = " & DestinationFile

Go to the Immediate window (Ctrl-G), and check what's printed.

It's pretty hard for us to help you if you won't tell us what's wrong!


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a .xlt with a macro in it. All I want to do is copy the .xlt to a
.xls
and then transfer a number of queries using the DoCmd.TransferSpreadsheet
method to the new .xls.
I have temporarily removed the TransferSpreadsheet code just to try to get
the doc copied. This all works fine if I fix the DestinationFile in the
code
ie "D:\...." but I want to pre-determine the folder and let the user just
name it. Hence the 2 text boxes (1 invisible to store the folder and the
2nd
visible to allow a user defined name).
Thanks for your continued help.

So what values are in the text boxes, and what does DestinationFile
contain
after the concatenation?

Before your FileCopy statement, put:

Debug.Print "[Text12] = " & Me![Text12]
Debug.Print "[Text4] = " & Me![Text4]
Debug.Print "[Text10] = " & Me![Text10]
Debug.Print "DestinationFile = " & DestinationFile

Go to the Immediate window (Ctrl-G), and check what's printed.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks for that Arvin, but what I really want to do is force the file
path
which will change from client to client and just allow the user to give
the
doc a name. Hence Text12 which is invisible is the path and the user
enters a
name in Text4. Using the GetOpenFile method allows the user more
options
than
this.

:

Use this function to determine your path:

http://www.mvps.org/access/api/api0001.htm

You'd set your textbox's controlsource to:

=GetOpenFile()
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Simon wrote:
I am trying to copy an excel .xlt file and create a .xls file from
the
OnClick event of a Command button using vba.
If I use a fixed path for the .xls this is fine. However, I would
like to automatically default to the Clients individual file and
allow the user to give it a name. I have an unbound textbox (Text12)
on my form which has its data set to DLookup... for the path and a
second unbound textbox (Text4) for the user to type a name. I have
changed the code a thousand times but can't get it right. Can anyone
help?
The latest attempt is this,
Dim xl As Object
Dim SourceFile As String
Dim ClientFile As String
Dim DestinationFile As String

SourceFile = "D:\Documents and
Settings\Simon\Desktop\ValSumTemp.xlt"
ClientFile = [Text12]
DestinationFile = ClientFile & Me![Text4] & Me![Text10]

FileCopy SourceFile, DestinationFile

I am using Access and Excel 2003 and any help would be gratefully
received.