SHELL syntax?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I guess I can't get this thing working without showing everything. I would
be greatly appreciative if someone could tell me what is wrong with the
following code ...I think the Shell syntax may be wrong....Help! (can you
tell I'm at my wits' end?)...............................................

Private Sub cmdImportAllFiles_Click()
On Error GoTo cmdImportAllFiles_Err

Dim OtherFile As String
Dim PrepFolder As String
Dim ProcessedFolder As String
Dim OtherFolder As String

'I thought this would work (because of the spaces in the folder names), but
it did not - so I commented it out
' PrepFolder = """" & "M:\Customer Service\NEW HR Reporter\" & """"
' ProcessedFolder = """" & "M:\Customer Service\NEW HR
Reporter\PROCESSED\" & """"
' OtherFolder = """" & "M:\Customer Service\NEW HR Reporter\OTHER
RECEIVED\" & """"

MsgBox "initializing variables" 'for debugging
PrepFolder = "M:\Customer Service\NEW HR Reporter\"
HRSumFile = "HRSum.xls"
ProcessedFolder = "M:\Customer Service\NEW HR Reporter\PROCESSED\"
OtherFolder = "M:\Customer Service\NEW HR Reporter\OTHER RECEIVED\"

'------------------------------------------------------------
MsgBox "Opening HRSum" 'for debugging
'Open Excel file HRSum, which will run a macro to fill in the data to be
imported


'================== is this where the problem is?==========
Call Shell("EXCEL.EXE " & PrepFolder & HRSumFile, 6)
'================== is this where the problem is?==========


MsgBox "Transferring spreadsheet HRPrep"
' import new combined "Prep" Excel scorecard file
DoCmd.TransferSpreadsheet acImport, 5, "tblSummary", PrepFolder &
"HRPrep.xls", True, ""
'------------------------------------------------------------
' get "OTHER" files
OtherFile = Dir$(OtherFolder & "*.xls")
'get the first file in the folder
Do While Len(OtherFile) > 0
DoCmd.TransferSpreadsheet acImport, 5, "tblSummary", OtherFolder &
OtherFile, True, ""
Name OtherFolder & OtherFile As ProcessedFolder & OtherFile
' Move the file once you're done with it
OtherFile = Dir$()
' Get the next file
Loop

cmdImportAllFiles_Exit:
Exit Sub

cmdImportAllFiles_Err:
MsgBox Error$
Resume cmdImportAllFiles_Exit

End Sub


Thanks in advance.
 
This should work.


Dim OtherFile As String
Dim PrepFolder As String
Dim ProcessedFolder As String
Dim OtherFolder As String

PrepFolder = "M:\Customer Service\NEW HR Reporter\"
HRSumFile = "HRSum.xls"

'------------------------------------------------------------
MsgBox "Opening HRSum" 'for debugging
'Open Excel file HRSum, which will run a macro to fill in the data to
beimported

'================== is this where the problem is?==========
Call Shell("EXCEL.EXE " & """" & PrepFolder & HRSumFile & """", 6)
 
Thanks Daniel. I knew about the multi-quote syntax, but it didn't occur to
me to look for that in the SHELL statement (I guess I was looking at the code
too long). I modified your recommendation a bit and it seems to be working.

Again, thanks.
 
Back
Top