×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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

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

>code does not work

Can you be more explicit?

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

(OP)
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

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

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

CODE

#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 

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

Did this help at all?

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

(OP)
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.

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

>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):

CODE

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


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

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

Managed to get it working?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close