UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Compile Error Using A Move File Module, Access 2016    
 
   
M2C
post Jul 12 2018, 01:04 AM
Post#1



Posts: 664
Joined: 7-April 08
From: Dubai


Hi
I am trying to get this bit of code to move files in one folder to another but I get a compile error: "User-Defined type not defined" on the Sub MoveFiles()

Any ideas why?

I use the following on a button on a form:

CODE
'Button on form
Private Sub txtMoveFile_Click()
Call MoveFiles
End Sub


This is Sub routine on the same form

CODE
Sub MoveFiles()
  Dim r As Range, c As Range
'The line below is where there is a compile error
  Set r = Range("A2", Range("A2").End(xlDown))
  For Each c In r
    MoveCurrent c.Value2, "c:\temp", "c:\temp\temp2"
  Next c
End Sub


And this is the Public Sub

CODE
Public Sub MoveCurrent(file$, pathFrom$, pathTo$)
    Dim fso As Object
    Dim sourceFile As String
    Dim targetFile As String
    Dim answer As Integer

    Set fso = CreateObject("Scripting.FileSystemObject")
    If Right(pathFrom$, 1) <> "\" Then sourceFile = pathFrom$ & "\" & file$
    If Right(pathTo$, 1) <> "\" Then targetFile = pathTo$ & "\" & file$
    
    With fso
      If Not .FolderExists(pathTo$) Then
          MkDir pathTo$
      End If
      
      If .FileExists(targetFile) Then
          answer = MsgBox("File already exists in this location. " _
              & "Are you sure you want to continue? If you continue " _
              & "the file at destination will be deleted!", _
              vbInformation + vbYesNo)
          If answer = vbNo Then
              Exit Sub
          End If
          Kill targetFile
      End If
      If .FileExists(sourceFile) Then .MoveFile sourceFile, targetFile
    End With
    Set fso = Nothing
End Sub


Go to the top of the page
 
Phil_cattivocara...
post Jul 12 2018, 02:00 AM
Post#2



Posts: 199
Joined: 2-April 18



QUOTE (M2C)
CODE
Dim r As Range, c As Range
     'The line below is where there is a compile error
   Set r = Range("A2", Range("A2").End(xlDown))
Here you are using Excel Object Model. Where did you open Excel instance? (this post is under Access Modules section, so I think you are using this inside Access, not Excel). You must refer to a Range using a worksheet too.
See here for use and examples
Range Object (Excel)
Refer to Cells by Using a Range Object
How to select cells/ranges by using Visual Basic procedures in Excel
This post has been edited by Phil_cattivocarattere: Jul 12 2018, 02:01 AM
Go to the top of the page
 
M2C
post Jul 12 2018, 02:04 AM
Post#3



Posts: 664
Joined: 7-April 08
From: Dubai


OK Phil thanks for this. I am very much in deepwater here!!

Can this be done in Access?
Go to the top of the page
 
JonSmith
post Jul 12 2018, 02:22 AM
Post#4



Posts: 3,956
Joined: 19-October 10



QUOTE
I am very much in deepwater here!!


Not so much, you just need to use valid code, that code is designed to loop through all the cells in the A column of a worksheet starting at A2. Each cell would contain a filename and it would move that file from "c:\temp" to "c:\temp\temp2".

You state you use access so immediately this code cannot work for you.
Where is your list of files? what are the paths to the two folders?

You only asked why the code doesn't work in your original question. Tell us what you are trying to do and why and we'll advice on what we think is the best approach.
Moving files can of course be done in Access.
Go to the top of the page
 
M2C
post Jul 12 2018, 02:39 AM
Post#5



Posts: 664
Joined: 7-April 08
From: Dubai


Hi Jon

Thanks for explaining this.

I have some time on my hands today so I am trying to get prepared for a question that is heading my way for a project I have completed recently. And that is:

"After importing the data into Access from an excel spreadsheet I want to move the spreadsheet into an archive folder."



CODE
Private Sub txtMultiExcelSheets_Click()

Dim filepath As String
Dim User As String

User = Environ("Username")

'DoCmd.SetWarnings False
'DoCmd.RunSQL "DELETE tblImportFromExcel.* FROM tblImportFromExcel"
'DoCmd.SetWarnings True

filepath = "C:\Users\" & User & "\OneDrive\Documents\Data\Functions"
If FileExist(filepath) Then

'Imports the spreadsheet into tblImportFromExcel
Call importExcelSheets("C:\Users\admin\OneDrive\Documents\Data\Functions", "MyExcelImport")

'Now move the excel sheet to the archive folder
'Code???

Else
MsgBox "File not found."
End If

End Sub


The Public Function is:
CODE
Public Function importExcelSheets(Directory As String, TableName As String) As Long
On Error Resume Next
Dim strDir As String
Dim strFile As String
Dim i As Long
i = 0
If Left(Directory, 1) <> "\" Then
     strDir = Directory & "\"
Else
     strDir = Directory
End If
strFile = Dir(strDir & "*.XLSX")
While strFile <> ""
     i = i + 1
     strFile = strDir & strFile
     Debug.Print "importing " & strFile
     'Change to False if no columnheaders
     DoCmd.TransferSpreadsheet acImport, , "tblExcelImports", strFile, True
     strFile = Dir()
Wend
importExcelSheets = i
End Function
Go to the top of the page
 
JonSmith
post Jul 12 2018, 03:07 AM
Post#6



Posts: 3,956
Joined: 19-October 10



Marvelous, so the code you had before was about moving an entire folder whereas you only want a file.

Its actually super simple, VBA has a built in Name function that will rename a file, if the path is also changed the file will also be moved.

Here is an example (https://www.rondebruin.nl/win/s3/win026.htm)

CODE
Name "C:\Users\Ron\SourceFolder\Test.xls" As "C:\Users\Ron\DestFolder\TestNew.xls"


Add that line and change the paths in it to refer to your Excel file and the location you want it saved to and you are done smile.gif
Go to the top of the page
 
M2C
post Jul 12 2018, 03:50 AM
Post#7



Posts: 664
Joined: 7-April 08
From: Dubai


Oh WOW!!

That's great thanks for your help Jon
Go to the top of the page
 
BruceM
post Jul 12 2018, 07:15 AM
Post#8


UtterAccess VIP
Posts: 7,720
Joined: 24-May 10
From: Downeast Maine


That's good to know about the Name function (documented as the Name statement).

Now, if only VBA Help hadn't become absolutely worthless in Access 2016. The web version of Help doesn't even have Search functionality, much less a convenient functions reference. I'm still looking for a reasonable alternative. Access Help is moderately helpful, but rather limited.
Go to the top of the page
 
cheekybuddha
post Jul 12 2018, 07:21 AM
Post#9


UtterAccess VIP
Posts: 10,538
Joined: 6-December 03
From: Telegraph Hill


Maybe this is a good starting point?
Go to the top of the page
 
BruceM
post Jul 12 2018, 09:32 AM
Post#10


UtterAccess VIP
Posts: 7,720
Joined: 24-May 10
From: Downeast Maine


Hi David,

The VBA reference link within that link is what shows up when I try to use VBA Help. However, while it seems to have links to a lot of details about a lot of things, I miss having a convenient reference source for looking up functions and having the arguments and return type listed in a summary description. I can mostly get to where I need with Access Help, which lets me look up VBA functions, but I didn't think it was broken, so I don't know why they "fixed" it.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2018 - 01:06 PM