VBA Macro Error
Hello Experts,
   The macro below was recorded by excel and modified by myself.  The second macro is exactly what Excel recorded.  It is supposed to go to ABCD.xls , select all the cells on a sheet, copy them, go back to  the original WXYZ.xls, select all the cells, and paste the data from ABCD.xls.  However, when I execute my edited macro I get the following error:
Run-Time Error '1004':
Application-Defined or object-defined error
I stepped through the code and found that the is occuring on 'Cells.Select'.
      Windows("VD" & Trim(inputDat) & ".csv").Activate
      Sheets("VD" & Trim(inputDat) & "").Activate
      Cells.Select
      Selection.Copy
      Windows("VXW." & Trim(InputWDat) & ".xls").Activate
      Sheets("" & Trim(inputDay) & "").Select
      Cells.Select
      ActiveSheet.Paste
When I execute the recorded macro, it works just fine.
'************Recorded Macro*****************
  Windows("VD070603.csv").Ac
  Cells.Select
  Selection.Copy
  Windows("Vinnie.20070603.x
  Sheets("SUN").Select
  Cells.Select
  ActiveSheet.Paste
What the hell's going on?
   The macro below was recorded by excel and modified by myself.  The second macro is exactly what Excel recorded.  It is supposed to go to ABCD.xls , select all the cells on a sheet, copy them, go back to  the original WXYZ.xls, select all the cells, and paste the data from ABCD.xls.  However, when I execute my edited macro I get the following error:
Run-Time Error '1004':
Application-Defined or object-defined error
I stepped through the code and found that the is occuring on 'Cells.Select'.
      Windows("VD" & Trim(inputDat) & ".csv").Activate
      Sheets("VD" & Trim(inputDat) & "").Activate
      Cells.Select
      Selection.Copy
      Windows("VXW." & Trim(InputWDat) & ".xls").Activate
      Sheets("" & Trim(inputDay) & "").Select
      Cells.Select
      ActiveSheet.Paste
When I execute the recorded macro, it works just fine.
'************Recorded Macro*****************
  Windows("VD070603.csv").Ac
  Cells.Select
  Selection.Copy
  Windows("Vinnie.20070603.x
  Sheets("SUN").Select
  Cells.Select
  ActiveSheet.Paste
What the hell's going on?
Forget about the double quotes. They are just zero-length strings, as you must know.
Try this version of your code:
      Workbooks("VD" & Trim(inputDat) & ".csv").Sheets("VD" & Trim(inputDat)).Activate
      Cells.Select
      Selection.Copy
      Workbooks("VXW." & Trim(InputWDat) & ".xls").Sheets(Trim(inputD
      Cells.Select
      ActiveSheet.Paste
Kevin
      Workbooks("VD" & Trim(inputDat) & ".csv").Sheets("VD" & Trim(inputDat)).Activate
      Cells.Select
      Selection.Copy
      Workbooks("VXW." & Trim(InputWDat) & ".xls").Sheets(Trim(inputD
      Cells.Select
      ActiveSheet.Paste
Kevin
ASKER
Zorvek,
   I get the same response.
   I get the same response.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Zorvek,
   I have moved everything into a form to allow myself to mask an input box.  Below is the code.  I added both of you suggestions and got the same response I've been getting with your first code, and got a different response on the second.  The second code gives me a suscript out of range error for  your code, " .UsedRange.Copy Workbooks("VXW." & ......"
Private Sub cmdDay_Click()
  txtDay.Visible = False
  lblDay.Visible = False
  lblDaya.Visible = False
  cmdDay.Visible = False
  txtSht.Visible = True
  lblSht.Visible = True
  lblShta.Visible = True
  cmdSht.Visible = True
  frmExtract.Caption = "Tandem ID"
End Sub
Private Sub cmdSht_Click()
  txtSht.Visible = False
  lblSht.Visible = False
  lblShta.Visible = False
  cmdSht.Visible = False
  txtID.Visible = True
  lblTID.Visible = True
  lblTIDa.Visible = True
  cmdTID.Visible = True
  frmExtract.Caption = "Tandem ID"
  frmExtract.Hide
'*************************
'Set Variable Types
'*************************
Dim PathInfo As String
 Dim objFSO, promptStr, inputDat, CSVFileDet, CSVFileSum, sheetName, NewNameD, NewNameS, i
 Set objDet = CreateObject("Scripting.Fi
Â
'*************************
' Get Tandem Logon ID &Â PW.
'*************************
Â
' Â promptStr = ""
' Â defaultStr = ""
' Â 'Do While True
' Â Â promptStr = promptStr &Â "Enter your Tandem ID."
' Â Â promptStr = promptStr &Â vbNewLine &Â vbNewLine
' Â Â inputID = InputBox(promptStr, "Import File", defaultStr)
   inputID = frmExtract.txtID.Value
    Â
' Â Â promptStr = promptStr &Â "Enter your Tandem password."
' Â Â promptStr = promptStr &Â vbNewLine
' Â Â InputPW = InputBox(promptStr, "Import File", defaultStr)
   InputPW = frmExtract.txtPW
    Â
'*************************
' Get week beginning date.
'*************************
  Â
' Â Â promptStr = promptStr &Â "Enter the week beginning date. (yyyymmdd)"
' Â Â promptStr = promptStr &Â vbNewLine
' Â Â InputWDat = InputBox(promptStr, "Import File", defaultStr)
   InputWDat = frmExtract.txtWeek
    Â
'*************************
' Get extract day for worksheet.
'*************************
  Â
' Â Â promptStr = promptStr &Â "Enter the day of extract."
' Â Â promptStr = promptStr &Â vbNewLine
' Â Â promptStr = promptStr &Â "Use one of the following:"
' Â Â promptStr = promptStr &Â vbNewLine
' Â Â promptStr = promptStr &Â "Sun" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Mon" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Tues" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Wed" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Thu" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Fri" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Sat" &Â vbNewLine
' Â Â inputDay = InputBox(promptStr, "Import File", defaultStr)
   inputDay = frmExtract.txtSht
  Â
Â
'*************************
' Get date of file needed to be imported to Excel from user.
'*************************
' Â promptStr = ""
' Â defaultStr = ""
 Do While True
' Â Â promptStr = promptStr &Â "Enter only the date (yymmdd) for the file you want to import."
' Â Â promptStr = promptStr &Â vbNewLine &Â vbNewLine
' Â Â promptStr = promptStr &Â "DO NOT enter file path or extention."
' Â Â promptStr = promptStr &Â vbNewLine
  Â
' Â Â inputDat = InputBox(promptStr, "Import File", defaultStr)
   inputDat = frmExtract.txtDay
  Â
  Â
'*************************
'If file is not found, inform user that entry does not exist
'*************************
   If inputDat = " " Then
    CSVFileDet = " "
    promptStr = CSVFileDet & vbNewLine
    promptStr = promptStr & "DOES NOT EXIST.  Enter Again."
    promptStr = promptStr & vbNewLine & vbNewLine
    defaultStr = inputDat
    Exit Do
   End If
  Â
  Â
   'FTP FILES
Dim txtUser, txtPass
txtUser = "ptms." &Â Trim(inputID)
txtPass = ""Â & Trim(InputPW)
  f = "C:\volume\temp\XFa9Tz0P.t
  Open f For Output As #1
  Print #1, "open pmtdev"
  Print #1, txtUser
  Print #1, txtPass
  Print #1, "lcd c:\volume\"
  Print #1, "cd; 51#; sway"
  Print #1, "ascii"
  Print #1, "prompt"
  Print #1, "get VD" & Trim(inputDat); "c:\volume\" & "VD" & Trim(inputDat) & ".csv"
  Print #1, "get VS" & Trim(inputDat); "c:\volume\" & "VS" & Trim(inputDat) & ".csv"
  Print #1, "bye"
  Close #1
  Shell "c:\windows\system32\ftp.exe -s:C:\volume\temp\XFa9Tz0P
  Open f For Output As #1
  Print #1, "aaaaaaaaaaaaaaaaaaaaaaaaa
  Print #1, "bbbbbbbbbbbbbbbbbbbbbbbbb
  Print #1, "ccccccccccccccccccccccccc
  Print #1, "aaaaaaaaaaaaaaaaaaaaaaaaa
  Print #1, "bbbbbbbbbbbbbbbbbbbbbbbbb
  Print #1, "ccccccccccccccccccccccccc
  Print #1, "aaaaaaaaaaaaaaaaaaaaaaaaa
  Print #1, "bbbbbbbbbbbbbbbbbbbbbbbbb
  Print #1, "ccccccccccccccccccccccccc
  Print #1, "aaaaaaaaaaaaaaaaaaaaaaaaa
  Print #1, "bbbbbbbbbbbbbbbbbbbbbbbbb
  Print #1, "ccccccccccccccccccccccccc
  Close #1
  Kill "C:\volume\temp\XFa9Tz0P.t
 Â
 Â
'*************************
'Set variable "CSVFileDet" with user entered date
'*************************
   If inputDat = "" Then
    CSVFileDet = ""
    Exit Do
   Else
    CSVFileDet = "C:\Volume\VD" & Trim(inputDat) & ".csv" & ""
   End If
'*************************
'If "CSVFileDet" does not exist, inform user
'*************************
   If objDet.FileExists(CSVFileD
    Exit Do
   Else
    promptStr = CSVFileDet & vbNewLine
    promptStr = promptStr & "DOES NOT EXIST.  Enter Again."
    promptStr = promptStr & vbNewLine & vbNewLine
    defaultStr = inputDat
   End If
  Â
Loop
'*************************
'Search for VD + "inputDat" in the C:\Volume\ folder
'*************************
'ScreenUpdating = False
With Application.FileSearch
  .NewSearch
  .Filename = "VD" & Trim(inputDat) & ".csv"
  .LookIn = "C:\Volume\"
  .SearchSubFolders = False
 Â
'*************************
'If file is found, Execute
'*************************
  If .Execute() > 0 Then
 Â
    For i = 1 To .FoundFiles.Count
      PathInfo = .FoundFiles(i)
      MsgBox "" & .Filename & " File Located.... Importing File: " & Trim(inputDat) & ".csv"""
    Next i
'*************************
'Open .FoundFile(i) Â Â **** .FoundFile(i) = Trim(inputDat).csv ] ****
'*************************
   Â
    Workbooks.Open Filename:=PathInfo
      ChDir "C:\Volume\"
     Â
     Â
  With Workbooks("VD" & Trim(inputDat) & ".csv").Sheets("VD" & Trim(inputDat))
   .UsedRange.Copy Workbooks("Vinnie." & Trim(InputWDat) & ".xls").Sheets(Trim(inputD
  End With
' Â Â Â Â Â Â Windows("VD070603.csv").Ac
' Â Â Â Â Â Â Windows("VD" &Â Trim(inputDat) &Â ".csv").Activate
' Â Â Â Â Â Â Sheets("VD" &Â Trim(inputDat) &Â "").Activate
' Â Â Â Â Â Â Cells.Select
' Â Â Â Â Â Â Selection.Copy
' Â Â Â Â Â Â Windows("Vinnie." &Â Trim(InputWDat) &Â ".xls").Activate
' Â Â Â Â Â Â Sheets("" &Â Trim(inputDay) &Â "").Select
' Â Â Â Â Â Â Cells.Select
' Â Â Â Â Â Â ActiveSheet.Paste
' Â Â Â Â Â Â Cells.Select
' Â Â Â Â Â Â Selection.Copy
' Â Â Â Â Â Â Windows("Vinnie." &Â Trim(InputWDat) &Â ".xls").Activate
' Â Â Â Â Â Â Sheets("" &Â Trim(inputDay) &Â "").Select
' Â Â Â Â Â Â Cells.Select
' Â Â Â Â Â Â ActiveSheet.Paste
' Â Â ActiveWorkbook.SaveAs Filename:="C:\Volume\" &Â Trim(inputDat) &Â ".xls", FileFormat:=xlExcel5, _
' Â Â Â Â Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
' Â Â Â Â CreateBackup:=False
   Â
'*************************
'Format Detail Worksheet
'*************************
' Â Â Windows("C:\Volume\" &Â Trim(inputDat) &Â ".xls").Activate
' Â Â ActiveSheet.Name = ""Â & Trim(inputDat) &Â "_Detail" &Â ""
' Â Â ActiveSheet.Columns("A:D")
' Â Â ActiveSheet.Columns("A:D")
' Â Â ActiveSheet.Range("A2").Se
' Â Â ActiveWindow.FreezePanes = True
' Â Â ActiveSheet.Range("A3").Se
' Â Â ActiveWorkbook.Save
' Â Â ActiveWorkbook.Close
' Â Â Else
' Â Â Â Â MsgBox "There were no files found."
  End If
 Â
End With
'*************************
'Search for VS + "inputDat" in the C:\Volume\ folder
'*************************
'With Application.FileSearch
' Â Â .NewSearch
' Â Â .Filename = "VS" &Â Trim(inputDat) &Â ".csv"
' Â Â .LookIn = "C:\Volume\"
' Â Â .SearchSubFolders = False
'
'*************************
'If file is found, Execute
'*************************
'
' Â Â If .Execute() >Â 0 Then
'
' Â Â Â Â For i = 1 To .FoundFiles.Count
' Â Â Â Â Â Â PathInfo = .FoundFiles(i)
' Â Â Â Â Â Â MsgBox ""Â & .Filename &Â " File Located.... Renaming &Â Importing File: "Â & NewNameS &Â ".csv"""
' Â Â Â Â Next i
' Â Â Â Â Workbooks.Open Filename:="C:\Volume\" &Â Trim(inputDat) &Â ".xls"
' Â Â Â Â Â Â ChDir "C:\Volume\"
' Â Â Sheets.Add
' Â Â Sheets("Sheet1").Select
' Â Â ActiveSheet.Name = ""Â & Trim(inputDat) &Â "_Summary" &Â ""
'
' Â Â NewSheetName = ActiveSheet.Name
' Â Â Else
' Â Â Â Â MsgBox "There were no files found."
' Â Â End If
'End With
  ActiveWorkbook.Save
' Â Â ActiveWorkbook.Close
  Windows("VD" & Trim(inputDat) & ".csv").Activate
  ActiveWorkbook.Save
  ActiveWorkbook.Close
 Â
'*************************
'Import Summary CSV File
'*************************
'
' Â Â With ActiveSheet.QueryTables.Ad
' Â Â Â Â Destination:=Range("A1"))
' Â Â Â Â .FieldNames = True
' Â Â Â Â .RowNumbers = False
' Â Â Â Â .FillAdjacentFormulas = False
' Â Â Â Â .PreserveFormatting = True
' Â Â Â Â .RefreshOnFileOpen = False
' Â Â Â Â .RefreshStyle = xlInsertDeleteCells
' Â Â Â Â .SavePassword = False
' Â Â Â Â .SaveData = True
' Â Â Â Â .AdjustColumnWidth = True
' Â Â Â Â .RefreshPeriod = 0
' Â Â Â Â .TextFilePromptOnRefresh = False
' Â Â Â Â .TextFilePlatform = 437
' Â Â Â Â .TextFileStartRow = 1
' Â Â Â Â .TextFileParseType = xlDelimited
' Â Â Â Â .TextFileTextQualifier = xlTextQualifierDoubleQuote
' Â Â Â Â .TextFileConsecutiveDelimi
' Â Â Â Â .TextFileTabDelimiter = True
' Â Â Â Â .TextFileSemicolonDelimite
' Â Â Â Â .TextFileCommaDelimiter = True
' Â Â Â Â .TextFileSpaceDelimiter = False
' Â Â Â Â .TextFileColumnDataTypes = Array(1, 1, 1, 1)
' Â Â Â Â .TextFileTrailingMinusNumb
' Â Â Â Â .Refresh BackgroundQuery:=False
' Â Â End With
   Â
'*************************
'Format Summary Worksheet
'*************************
' Â Â ActiveSheet.Columns("A:D")
' Â Â ActiveSheet.Columns("A:D")
' Â Â ActiveSheet.Range("A2").Se
' Â Â ActiveWindow.FreezePanes = True
' Â Â ActiveSheet.Range("A3").Se
' Â Â ActiveWorkbook.Save
'
'ScreenUpdating = True
End Sub
Private Sub cmdTID_Click()
  txtID.Visible = False
  lblTID.Visible = False
  lblTIDa.Visible = False
  cmdTID.Visible = False
  txtPW.Visible = True
  lblTPW.Visible = True
  cmdTPW.Visible = True
  frmExtract.Caption = "Tandem Password"
 Â
End Sub
Private Sub cmdTPW_Click()
  txtPW.Visible = False
  lblTPW.Visible = False
  cmdTPW.Visible = False
  txtWeek.Visible = True
  lblWeek.Visible = True
  lblWeeka.Visible = True
  cmdWeek.Visible = True
  frmExtract.Caption = "Starting Week Date"
End Sub
Private Sub cmdWeek_Click()
  txtWeek.Visible = False
  lblWeek.Visible = False
  lblWeeka.Visible = False
  cmdWeek.Visible = False
  txtDay.Visible = True
  lblDay.Visible = True
  lblDaya.Visible = True
  cmdDay.Visible = True
 Â
  frmExtract.Caption = "Day of Extract"
End Sub
   I have moved everything into a form to allow myself to mask an input box.  Below is the code.  I added both of you suggestions and got the same response I've been getting with your first code, and got a different response on the second.  The second code gives me a suscript out of range error for  your code, " .UsedRange.Copy Workbooks("VXW." & ......"
Private Sub cmdDay_Click()
  txtDay.Visible = False
  lblDay.Visible = False
  lblDaya.Visible = False
  cmdDay.Visible = False
  txtSht.Visible = True
  lblSht.Visible = True
  lblShta.Visible = True
  cmdSht.Visible = True
  frmExtract.Caption = "Tandem ID"
End Sub
Private Sub cmdSht_Click()
  txtSht.Visible = False
  lblSht.Visible = False
  lblShta.Visible = False
  cmdSht.Visible = False
  txtID.Visible = True
  lblTID.Visible = True
  lblTIDa.Visible = True
  cmdTID.Visible = True
  frmExtract.Caption = "Tandem ID"
  frmExtract.Hide
'*************************
'Set Variable Types
'*************************
Dim PathInfo As String
 Dim objFSO, promptStr, inputDat, CSVFileDet, CSVFileSum, sheetName, NewNameD, NewNameS, i
 Set objDet = CreateObject("Scripting.Fi
Â
'*************************
' Get Tandem Logon ID &Â PW.
'*************************
Â
' Â promptStr = ""
' Â defaultStr = ""
' Â 'Do While True
' Â Â promptStr = promptStr &Â "Enter your Tandem ID."
' Â Â promptStr = promptStr &Â vbNewLine &Â vbNewLine
' Â Â inputID = InputBox(promptStr, "Import File", defaultStr)
   inputID = frmExtract.txtID.Value
    Â
' Â Â promptStr = promptStr &Â "Enter your Tandem password."
' Â Â promptStr = promptStr &Â vbNewLine
' Â Â InputPW = InputBox(promptStr, "Import File", defaultStr)
   InputPW = frmExtract.txtPW
    Â
'*************************
' Get week beginning date.
'*************************
  Â
' Â Â promptStr = promptStr &Â "Enter the week beginning date. (yyyymmdd)"
' Â Â promptStr = promptStr &Â vbNewLine
' Â Â InputWDat = InputBox(promptStr, "Import File", defaultStr)
   InputWDat = frmExtract.txtWeek
    Â
'*************************
' Get extract day for worksheet.
'*************************
  Â
' Â Â promptStr = promptStr &Â "Enter the day of extract."
' Â Â promptStr = promptStr &Â vbNewLine
' Â Â promptStr = promptStr &Â "Use one of the following:"
' Â Â promptStr = promptStr &Â vbNewLine
' Â Â promptStr = promptStr &Â "Sun" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Mon" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Tues" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Wed" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Thu" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Fri" &Â vbNewLine
' Â Â promptStr = promptStr &Â "Sat" &Â vbNewLine
' Â Â inputDay = InputBox(promptStr, "Import File", defaultStr)
   inputDay = frmExtract.txtSht
  Â
Â
'*************************
' Get date of file needed to be imported to Excel from user.
'*************************
' Â promptStr = ""
' Â defaultStr = ""
 Do While True
' Â Â promptStr = promptStr &Â "Enter only the date (yymmdd) for the file you want to import."
' Â Â promptStr = promptStr &Â vbNewLine &Â vbNewLine
' Â Â promptStr = promptStr &Â "DO NOT enter file path or extention."
' Â Â promptStr = promptStr &Â vbNewLine
  Â
' Â Â inputDat = InputBox(promptStr, "Import File", defaultStr)
   inputDat = frmExtract.txtDay
  Â
  Â
'*************************
'If file is not found, inform user that entry does not exist
'*************************
   If inputDat = " " Then
    CSVFileDet = " "
    promptStr = CSVFileDet & vbNewLine
    promptStr = promptStr & "DOES NOT EXIST.  Enter Again."
    promptStr = promptStr & vbNewLine & vbNewLine
    defaultStr = inputDat
    Exit Do
   End If
  Â
  Â
   'FTP FILES
Dim txtUser, txtPass
txtUser = "ptms." &Â Trim(inputID)
txtPass = ""Â & Trim(InputPW)
  f = "C:\volume\temp\XFa9Tz0P.t
  Open f For Output As #1
  Print #1, "open pmtdev"
  Print #1, txtUser
  Print #1, txtPass
  Print #1, "lcd c:\volume\"
  Print #1, "cd; 51#; sway"
  Print #1, "ascii"
  Print #1, "prompt"
  Print #1, "get VD" & Trim(inputDat); "c:\volume\" & "VD" & Trim(inputDat) & ".csv"
  Print #1, "get VS" & Trim(inputDat); "c:\volume\" & "VS" & Trim(inputDat) & ".csv"
  Print #1, "bye"
  Close #1
  Shell "c:\windows\system32\ftp.exe -s:C:\volume\temp\XFa9Tz0P
  Open f For Output As #1
  Print #1, "aaaaaaaaaaaaaaaaaaaaaaaaa
  Print #1, "bbbbbbbbbbbbbbbbbbbbbbbbb
  Print #1, "ccccccccccccccccccccccccc
  Print #1, "aaaaaaaaaaaaaaaaaaaaaaaaa
  Print #1, "bbbbbbbbbbbbbbbbbbbbbbbbb
  Print #1, "ccccccccccccccccccccccccc
  Print #1, "aaaaaaaaaaaaaaaaaaaaaaaaa
  Print #1, "bbbbbbbbbbbbbbbbbbbbbbbbb
  Print #1, "ccccccccccccccccccccccccc
  Print #1, "aaaaaaaaaaaaaaaaaaaaaaaaa
  Print #1, "bbbbbbbbbbbbbbbbbbbbbbbbb
  Print #1, "ccccccccccccccccccccccccc
  Close #1
  Kill "C:\volume\temp\XFa9Tz0P.t
 Â
 Â
'*************************
'Set variable "CSVFileDet" with user entered date
'*************************
   If inputDat = "" Then
    CSVFileDet = ""
    Exit Do
   Else
    CSVFileDet = "C:\Volume\VD" & Trim(inputDat) & ".csv" & ""
   End If
'*************************
'If "CSVFileDet" does not exist, inform user
'*************************
   If objDet.FileExists(CSVFileD
    Exit Do
   Else
    promptStr = CSVFileDet & vbNewLine
    promptStr = promptStr & "DOES NOT EXIST.  Enter Again."
    promptStr = promptStr & vbNewLine & vbNewLine
    defaultStr = inputDat
   End If
  Â
Loop
'*************************
'Search for VD + "inputDat" in the C:\Volume\ folder
'*************************
'ScreenUpdating = False
With Application.FileSearch
  .NewSearch
  .Filename = "VD" & Trim(inputDat) & ".csv"
  .LookIn = "C:\Volume\"
  .SearchSubFolders = False
 Â
'*************************
'If file is found, Execute
'*************************
  If .Execute() > 0 Then
 Â
    For i = 1 To .FoundFiles.Count
      PathInfo = .FoundFiles(i)
      MsgBox "" & .Filename & " File Located.... Importing File: " & Trim(inputDat) & ".csv"""
    Next i
'*************************
'Open .FoundFile(i) Â Â **** .FoundFile(i) = Trim(inputDat).csv ] ****
'*************************
   Â
    Workbooks.Open Filename:=PathInfo
      ChDir "C:\Volume\"
     Â
     Â
  With Workbooks("VD" & Trim(inputDat) & ".csv").Sheets("VD" & Trim(inputDat))
   .UsedRange.Copy Workbooks("Vinnie." & Trim(InputWDat) & ".xls").Sheets(Trim(inputD
  End With
' Â Â Â Â Â Â Windows("VD070603.csv").Ac
' Â Â Â Â Â Â Windows("VD" &Â Trim(inputDat) &Â ".csv").Activate
' Â Â Â Â Â Â Sheets("VD" &Â Trim(inputDat) &Â "").Activate
' Â Â Â Â Â Â Cells.Select
' Â Â Â Â Â Â Selection.Copy
' Â Â Â Â Â Â Windows("Vinnie." &Â Trim(InputWDat) &Â ".xls").Activate
' Â Â Â Â Â Â Sheets("" &Â Trim(inputDay) &Â "").Select
' Â Â Â Â Â Â Cells.Select
' Â Â Â Â Â Â ActiveSheet.Paste
' Â Â Â Â Â Â Cells.Select
' Â Â Â Â Â Â Selection.Copy
' Â Â Â Â Â Â Windows("Vinnie." &Â Trim(InputWDat) &Â ".xls").Activate
' Â Â Â Â Â Â Sheets("" &Â Trim(inputDay) &Â "").Select
' Â Â Â Â Â Â Cells.Select
' Â Â Â Â Â Â ActiveSheet.Paste
' Â Â ActiveWorkbook.SaveAs Filename:="C:\Volume\" &Â Trim(inputDat) &Â ".xls", FileFormat:=xlExcel5, _
' Â Â Â Â Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
' Â Â Â Â CreateBackup:=False
   Â
'*************************
'Format Detail Worksheet
'*************************
' Â Â Windows("C:\Volume\" &Â Trim(inputDat) &Â ".xls").Activate
' Â Â ActiveSheet.Name = ""Â & Trim(inputDat) &Â "_Detail" &Â ""
' Â Â ActiveSheet.Columns("A:D")
' Â Â ActiveSheet.Columns("A:D")
' Â Â ActiveSheet.Range("A2").Se
' Â Â ActiveWindow.FreezePanes = True
' Â Â ActiveSheet.Range("A3").Se
' Â Â ActiveWorkbook.Save
' Â Â ActiveWorkbook.Close
' Â Â Else
' Â Â Â Â MsgBox "There were no files found."
  End If
 Â
End With
'*************************
'Search for VS + "inputDat" in the C:\Volume\ folder
'*************************
'With Application.FileSearch
' Â Â .NewSearch
' Â Â .Filename = "VS" &Â Trim(inputDat) &Â ".csv"
' Â Â .LookIn = "C:\Volume\"
' Â Â .SearchSubFolders = False
'
'*************************
'If file is found, Execute
'*************************
'
' Â Â If .Execute() >Â 0 Then
'
' Â Â Â Â For i = 1 To .FoundFiles.Count
' Â Â Â Â Â Â PathInfo = .FoundFiles(i)
' Â Â Â Â Â Â MsgBox ""Â & .Filename &Â " File Located.... Renaming &Â Importing File: "Â & NewNameS &Â ".csv"""
' Â Â Â Â Next i
' Â Â Â Â Workbooks.Open Filename:="C:\Volume\" &Â Trim(inputDat) &Â ".xls"
' Â Â Â Â Â Â ChDir "C:\Volume\"
' Â Â Sheets.Add
' Â Â Sheets("Sheet1").Select
' Â Â ActiveSheet.Name = ""Â & Trim(inputDat) &Â "_Summary" &Â ""
'
' Â Â NewSheetName = ActiveSheet.Name
' Â Â Else
' Â Â Â Â MsgBox "There were no files found."
' Â Â End If
'End With
  ActiveWorkbook.Save
' Â Â ActiveWorkbook.Close
  Windows("VD" & Trim(inputDat) & ".csv").Activate
  ActiveWorkbook.Save
  ActiveWorkbook.Close
 Â
'*************************
'Import Summary CSV File
'*************************
'
' Â Â With ActiveSheet.QueryTables.Ad
' Â Â Â Â Destination:=Range("A1"))
' Â Â Â Â .FieldNames = True
' Â Â Â Â .RowNumbers = False
' Â Â Â Â .FillAdjacentFormulas = False
' Â Â Â Â .PreserveFormatting = True
' Â Â Â Â .RefreshOnFileOpen = False
' Â Â Â Â .RefreshStyle = xlInsertDeleteCells
' Â Â Â Â .SavePassword = False
' Â Â Â Â .SaveData = True
' Â Â Â Â .AdjustColumnWidth = True
' Â Â Â Â .RefreshPeriod = 0
' Â Â Â Â .TextFilePromptOnRefresh = False
' Â Â Â Â .TextFilePlatform = 437
' Â Â Â Â .TextFileStartRow = 1
' Â Â Â Â .TextFileParseType = xlDelimited
' Â Â Â Â .TextFileTextQualifier = xlTextQualifierDoubleQuote
' Â Â Â Â .TextFileConsecutiveDelimi
' Â Â Â Â .TextFileTabDelimiter = True
' Â Â Â Â .TextFileSemicolonDelimite
' Â Â Â Â .TextFileCommaDelimiter = True
' Â Â Â Â .TextFileSpaceDelimiter = False
' Â Â Â Â .TextFileColumnDataTypes = Array(1, 1, 1, 1)
' Â Â Â Â .TextFileTrailingMinusNumb
' Â Â Â Â .Refresh BackgroundQuery:=False
' Â Â End With
   Â
'*************************
'Format Summary Worksheet
'*************************
' Â Â ActiveSheet.Columns("A:D")
' Â Â ActiveSheet.Columns("A:D")
' Â Â ActiveSheet.Range("A2").Se
' Â Â ActiveWindow.FreezePanes = True
' Â Â ActiveSheet.Range("A3").Se
' Â Â ActiveWorkbook.Save
'
'ScreenUpdating = True
End Sub
Private Sub cmdTID_Click()
  txtID.Visible = False
  lblTID.Visible = False
  lblTIDa.Visible = False
  cmdTID.Visible = False
  txtPW.Visible = True
  lblTPW.Visible = True
  cmdTPW.Visible = True
  frmExtract.Caption = "Tandem Password"
 Â
End Sub
Private Sub cmdTPW_Click()
  txtPW.Visible = False
  lblTPW.Visible = False
  cmdTPW.Visible = False
  txtWeek.Visible = True
  lblWeek.Visible = True
  lblWeeka.Visible = True
  cmdWeek.Visible = True
  frmExtract.Caption = "Starting Week Date"
End Sub
Private Sub cmdWeek_Click()
  txtWeek.Visible = False
  lblWeek.Visible = False
  lblWeeka.Visible = False
  cmdWeek.Visible = False
  txtDay.Visible = True
  lblDay.Visible = True
  lblDaya.Visible = True
  cmdDay.Visible = True
 Â
  frmExtract.Caption = "Day of Extract"
End Sub
My last post will work if both workbooks are open in the same Excel instance. Perhaps it's time to use the VBE debugger and inspect the workbook names you are generating. Make sure they match the actual workbook names exactly.
Kevin
Kevin
ASKER
Zorvek,
   I still can't get your submission to work.  I'm using a temporary fix using static names.  I will keep working on your submission and see if I can identify why it's not working.
If anyone has any suggestions, they will be greatly appreciated.
Thanks
   I still can't get your submission to work.  I'm using a temporary fix using static names.  I will keep working on your submission and see if I can identify why it's not working.
If anyone has any suggestions, they will be greatly appreciated.
Thanks
ASKER
Thanks Everyone for your assistance!! Â
Rather than using dynamic names, I am still using static. Â The purpose of the dynamic names, was to allo me to keep files for longer than 1 week and allow an archive to be created of files that were downloaded via FTP. Â My workaround for my errors above is as follows. Â Instead of downloading the files and attempting to import them based on their dynamic names, I am downloading the first file with a static name. Â Additionally, I am downloading the first file again into a designated folder and downloading the second file into another designated folder. Â If the folders do not exist, they are created. Â Since the first file that is being downloaded on a T1 intranet and will never exeed 17kb, downloading it twice has virtually no impact on the code's performance. Â After downloading the macro then imports the downloaded file to the correct sheet. Â Once the import has been completed, the file downloaded and given a static name is deleted.
Below is the code I am using...
Option Explicit
Public VREXXDAT, VREXWDAT, VREXSDAY, VREXFDAY, VREXTID, VREXTPW, VRETID, VRETPS, VREDET, VREDDT, VRESDT, F
Private Sub cmdSave_Click()
' Â Â ActiveWorkbook.Save
  frmVRE.Hide
  Unload frmVRE
End Sub
Sub UserForm_Initialize()
    txtVREIxD.SetFocus
 Â
End Sub
Private Sub cmdCancel_Click()
  frmVRE.Hide
  Unload frmVRE
End Sub
Private Sub cmdCancelCal_Click()
  frmVRE.Hide
  Unload frmVRE
 Â
End Sub
Private Sub cmdVREIxD_Click()
If txtVREIxD.Value = ""Â Then
    MsgBox "Please Enter Your Tandem ID"
    txtVREIxD.SetFocus
   Â
  Else
  If Left(LCase(txtVREIxD), 5) = "pmts." Then
    MsgBox "Please Remove PMTS. from your user ID."
    txtVREIxD.SetFocus
  Else
 Â
    VREXTID = txtVREIxD.Value
    txtVREIxD.Visible = False
    lblVREIxD.Visible = False
    lblVREIxDa.Visible = False
    cmdVREIxD.Visible = False
    txtVREPxW.Visible = True
    lblVREPxW.Visible = True
    cmdVREPxW.Visible = True
    txtVREPxW.SetFocus
    frmVRE.Caption = "Enter Tandem Password"
 Â
  End If
End If
End Sub
Private Sub cmdVREPxW_Click()
If txtVREPxW.Value = ""Â Then
    MsgBox "Please Enter Your Tandem PW"
    txtVREPxW.SetFocus
 Â
  Else
  VREXTPW = txtVREPxW.Value
  txtVREPxW.Visible = False
  lblVREPxW.Visible = False
  cmdVREPxW.Visible = False
  cmdCancel.Visible = False
  Cal1.Visible = True
  cmdOkCal.Visible = True
  cmdCancelCal.Visible = True
  frmVRE.Caption = "Select Extract Date"
End If
End Sub
Private Sub Cal1_Click()
  txt1.Value = Cal1.Value
  lblFile.Caption = Format(txt1.Value, "yymmdd")
  lblDay.Caption = Format(txt1.Value, "ddd")
 Â
  If lblDay.Caption = "Sun" Then
 Â
    lblWeekBegin.Caption = Format(txt1.Value, "yymmdd")
    VREXXDAT = lblFile.Caption
    VREXSDAY = "SUN"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Sunday"
   Â
  End If
 Â
  If lblDay.Caption = "Mon" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 1
    VREXXDAT = lblFile.Caption
    VREXSDAY = "MON"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Monday"
  End If
   Â
  If lblDay.Caption = "Tue" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 2
    VREXXDAT = lblFile.Caption
    VREXSDAY = "TUE"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Tuesday"
  End If
  If lblDay.Caption = "Wed" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 3
    VREXXDAT = lblFile.Caption
    VREXSDAY = "WED"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Wednesday"
  End If
  If lblDay.Caption = "Thu" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 4
    VREXXDAT = lblFile.Caption
    VREXSDAY = "THU"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Thursday"
  End If
  If lblDay.Caption = "Fri" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 5
    VREXXDAT = lblFile.Caption
    VREXSDAY = "FRI"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Friday"
  End If
  If lblDay.Caption = "Sat" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 6
    VREXXDAT = lblFile.Caption
    VREXSDAY = "SAT"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Saturday"
  End If
 Â
End Sub
Private Sub cmdOKCal_Click()
  Cal1.Visible = False
  cmdOkCal.Visible = False
  cmdCancelCal.Visible = False
  lblWork.Visible = True
 Â
  Continue
 Â
End Sub
 Sub Continue()
'*************************
'Create Folder with Week Beginning Date if it does not exist
'*************************
  If Dir("C:\Volume\Extracts\De
    MkDir ("C:\Volume\Extracts\Detai
  End If
 Â
  If Dir("C:\Volume\Extracts\Su
    MkDir ("C:\Volume\Extracts\Summa
  End If
'*************************
'GET FTP FILES
'*************************
  VRETID = "pmts." & VREXTID
  VRETPS = VREXTPW
  VREDET = "VD" & VREXXDAT
  VREDDT = "VD" & VREXXDAT & ".csv"
  VRESDT = "VS" & VREXXDAT & ".csv"
  F = "C:\volume\temp\VREIMPRT.V
  Open F For Output As #1
  Print #1, "open pmtdev"
  Print #1, VRETID
  Print #1, VRETPS
  Print #1, "lcd C:\Volume"
  Print #1, "cd " & " $data09"
  Print #1, "cd " & "volstats"
  Print #1, "ascii"
  Print #1, "prompt"
  Print #1, "get " & VREDET; " C:\volume\" & VREXFDAY & ".CSV"
  Print #1, "get " & VREDDT; " C:\Volume\Extracts\Detail\
  Print #1, "get " & VRESDT; " C:\Volume\Extracts\Summary
  Print #1, "bye"
  Close #1
  Shell "C:\windows\system32\ftp.exe -s:C:\volume\temp\VREIMPRT
'*************************
'ACTIVATE DAY OF WEEK SHEET. Â IMPORT CSV TO DAY OF WEEK SHEET
'*************************
  If VREXSDAY = "SUN" Then
    Application.Wait Now + TimeValue("0:00:5")
    Sheets("SUN").Activate
    Get_Sunday
  Else
    If VREXSDAY = "MON" Then
      Application.Wait Now + TimeValue("0:00:5")
      Sheets("MON").Activate
      Get_Monday
    Else
      If VREXSDAY = "TUE" Then
        Application.Wait Now + TimeValue("0:00:5")
        Sheets("TUE").Activate
        Get_Tuesday
      Else
        If VREXSDAY = "WED" Then
          Application.Wait Now + TimeValue("0:00:5")
          Sheets("WED").Activate
          Get_Wednesday
        Else
      If VREXSDAY = "THU" Then
        Application.Wait Now + TimeValue("0:00:5")
        Sheets("THU").Activate
        Get_Thursday
      Else
     If VREXSDAY = "FRI" Then
      Application.Wait Now + TimeValue("0:00:5")
      Sheets("FRI").Activate
      Get_Friday
     Else
  If VREXSDAY = "SAT" Then
    Application.Wait Now + TimeValue("0:00:5")
    Sheets("SAT").Activate
    Get_Saturday
  End If
 Â
End If
End If
End If
End If
End If
End If
 Â
End Sub
Sub DelFile()
Dim F
  F = "C:\volume\temp\VREIMPRT.V
  Open F For Output As #1
  Print #1, "                                   ."
  Print #1, "                                  . "
  Print #1, "                                 . .. "
  Print #1, "                     . .. ....  .    .. .~O7O$"
  Print #1, "                     ..,DOZ?7,.  ..    ..?7NNON"
  Print #1, "                    ...NNNNNNNNDO.. .   ....DINN8,"
  Print #1, "                  ....$NNNNNNNNNNMNN8? .  .. . IDNND="
  Print #1, "                  . INNMNNNNNNMNNMMNNNNN7.....
  Print #1, "             . .. . . INMMMNNMNNNNNNNMMNNNNNNN8N
  Print #1, "             ... .. INNMNMMNNMNMNNNNNNNNNNNNNN
  Print #1, "             7IN.?,8NNMNNNNMNMMNNNNNNNN
  Print #1, "      . ... .  ... ONNNNNNNNNNNNNMNMNNNNNNNNN
  Print #1, "       ... ..$N? ,DO8DDNNNNNNNNMNNNNNNNNNNN
  Print #1, "      ....~NNNNNNNNNNNNNDONNNNNN
  Print #1, "     ...,NNNNNNNNNNNNMNNNNMNNNN
  Print #1, "    .  ,NNNNNMNMNNNMNNNNNNNNNMMMN
 Â
  Close #1
  Close #1
  Close #1
  Close #1
  Application.Wait Now + TimeValue("0:00:03")
 Â
  frmVRE.lblWork.Caption = "Saving..."
 Â
  If frmVRE.lblDay.Caption = "Sun" Then
    VREXFDAY = "Sunday"
  End If
  If frmVRE.lblDay.Caption = "Mon" Then
    VREXFDAY = "Monday"
  End If
  If frmVRE.lblDay.Caption = "Tue" Then
    VREXFDAY = "Tuesday"
  End If
  If frmVRE.lblDay.Caption = "Wed" Then
    VREXFDAY = "Wednesday"
  End If
  If frmVRE.lblDay.Caption = "Thu" Then
    VREXFDAY = "Thursday"
  End If
  If frmVRE.lblDay.Caption = "Fri" Then
    VREXFDAY = "Friday"
  End If
  If frmVRE.lblDay.Caption = "Sat" Then
    VREXFDAY = "Saturday"
  End If
 Â
  Kill "C:\Volume\" & VREXFDAY & ".CSV"
 Â
  Kill "C:\Volume\Temp\VREIMPRT.V
     Â
  frmVRE.Hide
  Unload frmVRE
  ActiveWorkbook.Save
  Sheets("Total Summary").Activate
   Â
  End Sub
 Â
Sub Get_Sunday()
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Sunday_2"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Monday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Monday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Tuesday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Tuesday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Wednesday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Wednesday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Thursday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Thursday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
' Â Â Â Â .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Friday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Friday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Saturday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Saturday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Rather than using dynamic names, I am still using static. Â The purpose of the dynamic names, was to allo me to keep files for longer than 1 week and allow an archive to be created of files that were downloaded via FTP. Â My workaround for my errors above is as follows. Â Instead of downloading the files and attempting to import them based on their dynamic names, I am downloading the first file with a static name. Â Additionally, I am downloading the first file again into a designated folder and downloading the second file into another designated folder. Â If the folders do not exist, they are created. Â Since the first file that is being downloaded on a T1 intranet and will never exeed 17kb, downloading it twice has virtually no impact on the code's performance. Â After downloading the macro then imports the downloaded file to the correct sheet. Â Once the import has been completed, the file downloaded and given a static name is deleted.
Below is the code I am using...
Option Explicit
Public VREXXDAT, VREXWDAT, VREXSDAY, VREXFDAY, VREXTID, VREXTPW, VRETID, VRETPS, VREDET, VREDDT, VRESDT, F
Private Sub cmdSave_Click()
' Â Â ActiveWorkbook.Save
  frmVRE.Hide
  Unload frmVRE
End Sub
Sub UserForm_Initialize()
    txtVREIxD.SetFocus
 Â
End Sub
Private Sub cmdCancel_Click()
  frmVRE.Hide
  Unload frmVRE
End Sub
Private Sub cmdCancelCal_Click()
  frmVRE.Hide
  Unload frmVRE
 Â
End Sub
Private Sub cmdVREIxD_Click()
If txtVREIxD.Value = ""Â Then
    MsgBox "Please Enter Your Tandem ID"
    txtVREIxD.SetFocus
   Â
  Else
  If Left(LCase(txtVREIxD), 5) = "pmts." Then
    MsgBox "Please Remove PMTS. from your user ID."
    txtVREIxD.SetFocus
  Else
 Â
    VREXTID = txtVREIxD.Value
    txtVREIxD.Visible = False
    lblVREIxD.Visible = False
    lblVREIxDa.Visible = False
    cmdVREIxD.Visible = False
    txtVREPxW.Visible = True
    lblVREPxW.Visible = True
    cmdVREPxW.Visible = True
    txtVREPxW.SetFocus
    frmVRE.Caption = "Enter Tandem Password"
 Â
  End If
End If
End Sub
Private Sub cmdVREPxW_Click()
If txtVREPxW.Value = ""Â Then
    MsgBox "Please Enter Your Tandem PW"
    txtVREPxW.SetFocus
 Â
  Else
  VREXTPW = txtVREPxW.Value
  txtVREPxW.Visible = False
  lblVREPxW.Visible = False
  cmdVREPxW.Visible = False
  cmdCancel.Visible = False
  Cal1.Visible = True
  cmdOkCal.Visible = True
  cmdCancelCal.Visible = True
  frmVRE.Caption = "Select Extract Date"
End If
End Sub
Private Sub Cal1_Click()
  txt1.Value = Cal1.Value
  lblFile.Caption = Format(txt1.Value, "yymmdd")
  lblDay.Caption = Format(txt1.Value, "ddd")
 Â
  If lblDay.Caption = "Sun" Then
 Â
    lblWeekBegin.Caption = Format(txt1.Value, "yymmdd")
    VREXXDAT = lblFile.Caption
    VREXSDAY = "SUN"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Sunday"
   Â
  End If
 Â
  If lblDay.Caption = "Mon" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 1
    VREXXDAT = lblFile.Caption
    VREXSDAY = "MON"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Monday"
  End If
   Â
  If lblDay.Caption = "Tue" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 2
    VREXXDAT = lblFile.Caption
    VREXSDAY = "TUE"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Tuesday"
  End If
  If lblDay.Caption = "Wed" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 3
    VREXXDAT = lblFile.Caption
    VREXSDAY = "WED"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Wednesday"
  End If
  If lblDay.Caption = "Thu" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 4
    VREXXDAT = lblFile.Caption
    VREXSDAY = "THU"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Thursday"
  End If
  If lblDay.Caption = "Fri" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 5
    VREXXDAT = lblFile.Caption
    VREXSDAY = "FRI"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Friday"
  End If
  If lblDay.Caption = "Sat" Then
   Â
    lblWeekBegin.Caption = "0" & Format(txt1.Value, "yymmdd") - 6
    VREXXDAT = lblFile.Caption
    VREXSDAY = "SAT"
    VREXWDAT = lblWeekBegin.Caption
    VREXFDAY = "Saturday"
  End If
 Â
End Sub
Private Sub cmdOKCal_Click()
  Cal1.Visible = False
  cmdOkCal.Visible = False
  cmdCancelCal.Visible = False
  lblWork.Visible = True
 Â
  Continue
 Â
End Sub
 Sub Continue()
'*************************
'Create Folder with Week Beginning Date if it does not exist
'*************************
  If Dir("C:\Volume\Extracts\De
    MkDir ("C:\Volume\Extracts\Detai
  End If
 Â
  If Dir("C:\Volume\Extracts\Su
    MkDir ("C:\Volume\Extracts\Summa
  End If
'*************************
'GET FTP FILES
'*************************
  VRETID = "pmts." & VREXTID
  VRETPS = VREXTPW
  VREDET = "VD" & VREXXDAT
  VREDDT = "VD" & VREXXDAT & ".csv"
  VRESDT = "VS" & VREXXDAT & ".csv"
  F = "C:\volume\temp\VREIMPRT.V
  Open F For Output As #1
  Print #1, "open pmtdev"
  Print #1, VRETID
  Print #1, VRETPS
  Print #1, "lcd C:\Volume"
  Print #1, "cd " & " $data09"
  Print #1, "cd " & "volstats"
  Print #1, "ascii"
  Print #1, "prompt"
  Print #1, "get " & VREDET; " C:\volume\" & VREXFDAY & ".CSV"
  Print #1, "get " & VREDDT; " C:\Volume\Extracts\Detail\
  Print #1, "get " & VRESDT; " C:\Volume\Extracts\Summary
  Print #1, "bye"
  Close #1
  Shell "C:\windows\system32\ftp.exe -s:C:\volume\temp\VREIMPRT
'*************************
'ACTIVATE DAY OF WEEK SHEET. Â IMPORT CSV TO DAY OF WEEK SHEET
'*************************
  If VREXSDAY = "SUN" Then
    Application.Wait Now + TimeValue("0:00:5")
    Sheets("SUN").Activate
    Get_Sunday
  Else
    If VREXSDAY = "MON" Then
      Application.Wait Now + TimeValue("0:00:5")
      Sheets("MON").Activate
      Get_Monday
    Else
      If VREXSDAY = "TUE" Then
        Application.Wait Now + TimeValue("0:00:5")
        Sheets("TUE").Activate
        Get_Tuesday
      Else
        If VREXSDAY = "WED" Then
          Application.Wait Now + TimeValue("0:00:5")
          Sheets("WED").Activate
          Get_Wednesday
        Else
      If VREXSDAY = "THU" Then
        Application.Wait Now + TimeValue("0:00:5")
        Sheets("THU").Activate
        Get_Thursday
      Else
     If VREXSDAY = "FRI" Then
      Application.Wait Now + TimeValue("0:00:5")
      Sheets("FRI").Activate
      Get_Friday
     Else
  If VREXSDAY = "SAT" Then
    Application.Wait Now + TimeValue("0:00:5")
    Sheets("SAT").Activate
    Get_Saturday
  End If
 Â
End If
End If
End If
End If
End If
End If
 Â
End Sub
Sub DelFile()
Dim F
  F = "C:\volume\temp\VREIMPRT.V
  Open F For Output As #1
  Print #1, "                                   ."
  Print #1, "                                  . "
  Print #1, "                                 . .. "
  Print #1, "                     . .. ....  .    .. .~O7O$"
  Print #1, "                     ..,DOZ?7,.  ..    ..?7NNON"
  Print #1, "                    ...NNNNNNNNDO.. .   ....DINN8,"
  Print #1, "                  ....$NNNNNNNNNNMNN8? .  .. . IDNND="
  Print #1, "                  . INNMNNNNNNMNNMMNNNNN7.....
  Print #1, "             . .. . . INMMMNNMNNNNNNNMMNNNNNNN8N
  Print #1, "             ... .. INNMNMMNNMNMNNNNNNNNNNNNNN
  Print #1, "             7IN.?,8NNMNNNNMNMMNNNNNNNN
  Print #1, "      . ... .  ... ONNNNNNNNNNNNNMNMNNNNNNNNN
  Print #1, "       ... ..$N? ,DO8DDNNNNNNNNMNNNNNNNNNNN
  Print #1, "      ....~NNNNNNNNNNNNNDONNNNNN
  Print #1, "     ...,NNNNNNNNNNNNMNNNNMNNNN
  Print #1, "    .  ,NNNNNMNMNNNMNNNNNNNNNMMMN
 Â
  Close #1
  Close #1
  Close #1
  Close #1
  Application.Wait Now + TimeValue("0:00:03")
 Â
  frmVRE.lblWork.Caption = "Saving..."
 Â
  If frmVRE.lblDay.Caption = "Sun" Then
    VREXFDAY = "Sunday"
  End If
  If frmVRE.lblDay.Caption = "Mon" Then
    VREXFDAY = "Monday"
  End If
  If frmVRE.lblDay.Caption = "Tue" Then
    VREXFDAY = "Tuesday"
  End If
  If frmVRE.lblDay.Caption = "Wed" Then
    VREXFDAY = "Wednesday"
  End If
  If frmVRE.lblDay.Caption = "Thu" Then
    VREXFDAY = "Thursday"
  End If
  If frmVRE.lblDay.Caption = "Fri" Then
    VREXFDAY = "Friday"
  End If
  If frmVRE.lblDay.Caption = "Sat" Then
    VREXFDAY = "Saturday"
  End If
 Â
  Kill "C:\Volume\" & VREXFDAY & ".CSV"
 Â
  Kill "C:\Volume\Temp\VREIMPRT.V
     Â
  frmVRE.Hide
  Unload frmVRE
  ActiveWorkbook.Save
  Sheets("Total Summary").Activate
   Â
  End Sub
 Â
Sub Get_Sunday()
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Sunday_2"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Monday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Monday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Tuesday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Tuesday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Wednesday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Wednesday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Thursday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Thursday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
' Â Â Â Â .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Friday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Friday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
Sub Get_Saturday()
 Â
  With ActiveSheet.QueryTables.Ad
    Destination:=Range("A1"))
    .Name = "Saturday"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimi
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimite
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumb
    .Refresh BackgroundQuery:=False
  End With
 Â
  DelFile
 Â
End Sub
ASKER
Zorvek,
   I was able to get your last post to run successfully after I  posted my the current code I'm using above.  Since your submission worked, I'll award you the points.  I will however continue to use the "work-around" I poseted above.
Thanks Everyone.
   I was able to get your last post to run successfully after I  posted my the current code I'm using above.  Since your submission worked, I'll award you the points.  I will however continue to use the "work-around" I poseted above.
Thanks Everyone.
Also, does the name of the 'day' sheet actually have double quotes at the beginning and end?