Hey guys and gals, I have a script here (partly ripped from others) where i download a file from the internet, it saves it on my desktop. It then opens the file transports data to it and moves it to another directory. All this works fine when the downloaded file is saved as a single name. However, the saved file name will alter depenging on the branch using the system. So for instance i am in the Austin branch so it saves as Austin.xls however if i was in the Corpus Christi branch it saves as Corpus Christy.xls.
So it works fine with austin however when i do corpus, it saves fine on the desktop but when it goes to move the data it says it cant be found, but the file path in the error is correct and the file is there. I tried to do a replace function to remove the space and add an underscore and that works fine but it adds a crap load of spaces in the beginning of the file name and still says the file cant be found.
any pointers on how i can fix this would be greatly appreciated. see code below
So it works fine with austin however when i do corpus, it saves fine on the desktop but when it goes to move the data it says it cant be found, but the file path in the error is correct and the file is there. I tried to do a replace function to remove the space and add an underscore and that works fine but it adds a crap load of spaces in the beginning of the file name and still says the file cant be found.
any pointers on how i can fix this would be greatly appreciated. see code below
Code:
Private Sub SubmitBtn_Click()
Dim BranchNumber As String
BranchNumber = Nz(DLookup("Branch", "CurrentVariables"))
Dim branchname As String
branchname = Nz(DLookup("BranchName", "Branches", "BranchNumber = '" & BranchNumber & "'"))
Dim tablecheck As String
tablecheck = Nz(DLookup("FirstName", "TrainingUpload"))
If tablecheck = "" Then
MsgBox "There Are No Training Records To Upload"
Exit Sub
Else
Call downloadfile.downloadfile("(file removed for protection", "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\ " & branchname & ".xls")
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
blnEXCEL = False
' Replace True with False if you do not want the first row of
' the worksheet to be a header row (the names of the fields
' from the recordset)
blnHeaderRow = False
' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True
' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file into which you will write the data
Set xlw = xlx.Workbooks.Open("C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\ " & branchname & ".xls")
' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets("Safway_Emp_Import learncenter")
' Replace A1 with the cell reference into which the first data value
' is to be written
Set xlc = xls.Range("A2") ' this is the first cell into which data go
Set dbs = CurrentDb()
' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rst = dbs.OpenRecordset("TrainingUpload", dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
If blnHeaderRow = True Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1, 0)
End If
' write data to worksheet
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1, 0)
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
' Close the EXCEL file while saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close True ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
Dim fso
Dim file As String, sfol As String, dfol As String
file = branchname & ".xls" ' change to match the file names
sfol = "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\" ' change to match the source folder path
dfol = "P:\Temp\Training Attendance Forms\" ' change to match the destination folder path
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(sfol & file) Then
MsgBox sfol & file & " does not exist!", vbExclamation, "Source File Missing"
ElseIf Not fso.FileExists(dfol & file) Then
fso.MoveFile (sfol & file), dfol
Else: MsgBox dfol & file & " already exists!", vbExclamation, "Destination File Exists"
End If
Dim sql As String
sql = "DELETE * FROM TrainingUpload;"
DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True
End If
