runtime error 1004 access 2003 1

[vba317]

I am trying to automate the creation of file names when I open and close an excel file so I can apply the code to many files instead of 1. I am using access and excel 2003. Currently I am using the following code to open an excel template. The original code works when I try and substitute strings within the file commands that's where I run into trouble. In the current code section when I hover over strUCI I see strUCI instead of what is in the variable.

'original code
With goXL
       .Workbooks.Open FileName:="Z:\Adhoc projects\ZZZ\ZZZ_CapsuleEcho_Sep2012.xlt"
        'Select Sheetname for information to go into.
        .Sheets("ZZZ_Capsule Echo").Select
    End With

'Current code
Dim strCurMonth As String
Dim strUCI As String
Dim strSaveFile As String
Dim strCName As String
Dim strFileLoc As String

With goXL
        strCName = strUCI
        strFileLoc = "Z:\Adhoc projects\strCName"        
        strSaveFile = "\strUCI\strUCI & _ & CapsuleEcho_" & (strCurMon) & ".xlt"
        .Workbooks.Open FileName:=strFileLoc & strSaveFile
        'Select Sheetname for information to go into.
        .Sheets("ZZZ_Capsule Echo").Select
    End With

 

[Andrzejek]

You dim it, but never assigned any value to strUCI

...
Dim strUCI As String
...
strUCI = "AbCd"
...
strSaveFile = "\" & strUCI & "\" & strUCI & "_CapsuleEcho_" & strCurMon & ".xlt"
[blue]Debug.Print strSaveFile[/blue]
...

Have fun.

---- Andy

 

[PHV]

strFileLoc = "Z:\Adhoc projects\" & strCName
strSaveFile = "\" strUCI & "_ CapsuleEcho_" & strCurMon & ".xlt"


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

 

[vba317]

I have attached the code where the strings are assigned.
PHV strSaveFile = "\" strUCI & "_ CapsuleEcho_" & strCurMon & ".xlt" gives me a compile error.

Me.txtUCI.SetFocus
strUCI = Me.txtUCI.Text
'Me.cboRptMon.SetFocus
If IsNull(Me.cboImpMonData.Value) Then
    MsgBox " Enter current month's data"
    Else
    liPdID = Me.cboRptMon.Value
strCurMonth = Me.cboRptMon.Column(1) 'CREATE REPORTS COMBOBOX
End If

 

[vba317]

mistyped PHV your suggestion worked