Require update code so that it can save files in VBA7 Excel for both 32 & 64 bit versions

[VinoVino]

Hi there.


I have the following code that we are using to save a file to a pre-determined network address.

However since migrating to the latest version of excel the below code does not work with the new version of VBA7, also 64 bit and 32 bit Excel versions is proving a bit of an issue.


Think I might also have some issues with mapping of network drives, as this will be used by several end users to save their outputs I cannot guarantee that they will all have mapped the network folder to the same drive.


Can anyone please help me complete this task and allow our users to save the outputs in the desired area.


Many thanks in advance for your help






Sub Submit()
Dim vFileToOpen As Variant
Dim strCurDir As String
Dim VFilename As Variant


' Keep Original Dir
strCurDir = CurDir

VFilename = Worksheets("input").Range("D8") & " WLM " & Worksheets("input").Range("D12") & ".xlsx"

' Note: If the UNC path does not exist then it will default to your current one
SetCurrentDirectory "\\staffshare.ads.ecu.edu.au\FBL\Projects\Workload Models"

' Saves file
Application.EnableEvents = False
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=VFilename, FileFormat:=51, CreateBackup:=False
Application.DisplayAlerts = True
Application.EnableEvents = True

' Change directory back
ChDir strCurDir
ThisWorkbook.Close
End Sub

 

[strongm]

>code does not work

Can you be more explicit?

 

[VinoVino]

Sure can.


So I need the code to be able to handle the changes in VBA7.

So needs to be able to work on both 32bit and 64 bit code for the set current directory part.

I have looked on other forums and tried to follow their solutions but as of yet there has been no luck.


Also I think there might be an issue with mapping network drives as I have approximately 60 end users. So need to provide the full network string. I have read that there are problems with this in VBA7. Although not sure.

Basically just need this to save an excel file in a specific directory with specific naming convention for both 32 and 64 bit versions of Excel 2106.

Thanks

 

[strongm]

Need to see your declaration(s) for SetCurrentDirectory. Does it look anything like this, which it should?

[blue]#If VBA7 Then
    Private Declare PtrSafe Function SetCurrentDirectory Lib "kernel32" Alias "" SetCurrentDirectoryA(ByVal lpPathName As String) As Long 
#Else
    Private Declare Function SetCurrentDirectory Lib "kernel32" Alias "" SetCurrentDirectoryA(ByVal lpPathName As String) As Long 
#End If[/blue]

 

[strongm]

Did this help at all?

 

[VinoVino]

I have seen this but I end up getting an error message that I cannot fix.


Can you please tell me where I put the new current directory so that the macro is error free?


Sorry I am not familiar with Visual Basic or coding. I am just a data analyst.

 

[strongm]

>I have read that there are problems with this in VBA7. Although not sure.

Not with VBA7 per se - it is just that ChDir does not work with UNCs (it requires a drive letter), which is why whoever wrote your code uses SetCurrentDirectory

>I have seen this but I end up getting an error message that I cannot fix.

Again, you will have to be more explicit about what you did with the code, and exactly what error you are seeing.


I would suspect that in one of your VBA modules there is a declaration looking pretty much like this (it may say Public rather than Private):

[blue]

Private Declare Function SetCurrentDirectory Lib "kernel32" Alias "" SetCurrentDirectoryA(ByVal lpPathName As String) As Long

[/blue]

You need to replace that single line with the code I provided above.

 

[strongm]

Managed to get it working?