Hi All,
I am trying to open a workbook that will be in a location that is dependent on what the user defines via input boxes:
Now the user defined items are used later, that's why they are formatted as such, but we can use information as part of that to find the location. The hiccup I have is after the "SP2270" is an additional name, in this case "(GEW Dual Panda)". After doing various searches online for various solutions none seem to be working for me. I am probably mis-interpreting it so I was hoping someone might have a fresh new way of looking at it.
No I could define each additional file name. For example I know that "(GEW Dual Panda)" will appear if my variable "lassy" = 2270. However "lassy" could be quite a number of possibilities and to manually code all corresponding file names would be very time consuming and any additional files wouldn't need to be added to the code. If this is what I have to be then so be it but I'm hoping there is an alternative method.
Many thanks in advance to any and all help.
And apologies if the above makes no sense...
I am trying to open a workbook that will be in a location that is dependent on what the user defines via input boxes:
Code:
Dim assy As String
Dim job As String
assy = InputBox("Please enter assembly number in the format Dxxxx-xxx", "Assembly Number")
' i.e. D2270-502
job = InputBox("Please enter job number (6 digits)", "Works Order Number")
' i.e. 733802
If Len(assy) = 9 Or Len(job) = 6 Then
Dim lassy As String
Dim ljob As String
Dim excelloc As String
lassy = Mid(assy, 2, 4)
' i.e. 2270
ljob = "WO" & Left(job, 4)
' i.e. WO7338
excelloc = Dir("F:\CELAB\EVERYONE\Toby\DSL Database\SP" & lassy & "*" & "\DWGSET\" & ljob & ".xls")
' i.e. F:\CELAB\EVERYONE\Toby\DSL Database\SP2270 (GEW Dual Panda)\DWGSET\WO7338.xls
DoCmd.TransferSpreadsheet acImport, , "DSL_T", excelloc
End If
Now the user defined items are used later, that's why they are formatted as such, but we can use information as part of that to find the location. The hiccup I have is after the "SP2270" is an additional name, in this case "(GEW Dual Panda)". After doing various searches online for various solutions none seem to be working for me. I am probably mis-interpreting it so I was hoping someone might have a fresh new way of looking at it.
No I could define each additional file name. For example I know that "(GEW Dual Panda)" will appear if my variable "lassy" = 2270. However "lassy" could be quite a number of possibilities and to manually code all corresponding file names would be very time consuming and any additional files wouldn't need to be added to the code. If this is what I have to be then so be it but I'm hoping there is an alternative method.
Many thanks in advance to any and all help.
And apologies if the above makes no sense...