VBA Tips - Get A File Path

I can open and save the Excel file if I use a mapped drive path, but not if i open it using the full path.

e:\sales\file.xls works fine, open, saves, re-opens.
\\server\sales\file.xls opens, saves and is then its corrupt.

I've experimented with this and its a consistent problem on 3 computers with various excel (2002, 2003 Small Buisness, 2003 Standard) versions and xp service packs (SP1, SP2, SP3). I also tried differnet opening methods, double click on the file vs excel>file>open>browse... and the only consistancy seems to be the path name. Has any one ever heard of anything like this?

To add a bit more info, this file starts as a template and our off site salesmen fill it out and e-mail it in. The problem only occurrs with files from one of our 8 salesmen. We've re-installed MS Office and updated all his software, but the problem presists.

How do I get this code to annotate the path in cell B4?

Sub ChooseFolder()
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "C:\"
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Clicked on Cancel"
Exit Sub
End If
MsgBox .SelectedItems(1)
End With
End Sub

Thanks!

Hi everyone! I am programming (or trying to!!) a Makro to make certain administrative tasks more efficient. Unfortunetly I am completly stuck:

I have declared String Variables and used data from the workbook to define them:

Dim SupplierCode As String
Dim Year As String
Dim ToyotaPlant As String
Dim Month As String
Dim Manifest As String

Sheets("january").Select

Range("B3").Select
SupplierCode = ActiveCell

Range("G3").Select
ToyotaPlant = ActiveCell

Range("J2").Select
Month = ActiveCell

Range("J3").Select
Year = ActivCell

Sheets("TPN").Select

Range("L15").Select
Manifest = ActiveCell

Now I would like to save the file, using these variables in the File-Path:

ActiveWorkbook.SaveAs Filename:="\\Brufs01\TRANSFER\TPN_Database\"Toyotaplant"\"Year"\"SupplierCode"\"Month"\"Manifest".xl s", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

This doesnt work, I think its only a syntax error but IMPOSSIBLE to make it work, aaargh

Also if this path doesnt exist I would like excel to simply create and not make a fuss!!! Any help would be very very much appreciated!!

Thanks alot in advance,

Jan

Sorry I have to restart part of my old thread.
Could someone please edit this part or the whole code..
Code:

MyFile =  ThisWorkbook.Path & "\Book2.xls" 

I want myself to enter the whole path myself like 'C\Macro and Excels trix\.. etc and not "thisworkbook.path & \book2.xls"

Thanks pedie

Code:

Sub Test2()

    Dim wb As Workbook, MyFile As String
    
    MyFile =  ThisWorkbook.Path & "\Book2.xls" 
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:=MyFile)
    On Error GoTo 0
    
    If wb Is Nothing Then MsgBox "Couldn't locate " & MyFile: Exit Sub
    
    With wb
        '.Activate
        '.Cells.Activate
        With ActiveSheet
            .Range("A1").Value = "Hello!"
            .Range("A3").Formula = "=A1"
        End With
        .Save
        .Close
    End With
    
End Sub

Hi,

I have been working on a file that has many little bugs and I reached a point where I simply want to re-do this file in order for it to work properly and not to give me various of random errors at times.

I am packing up on donuts and coffee and will have a long night in front of me but have couple of questions for anyone that has done such a process before.

First is - what can I bring over from the old file - can I at least paste as values some cells that are text only? Can I copy directly the password and paste in the cells? I don't want to bring any sort of bugs and will re-do as much as I have to.
Second - about the macro code - I will recreate the forms, but is it safe to copy and paste the code from the old file?

Thanks in advance!

Hi, everyone,

Please let me know any tips to reduce the size of an excel file. I'm have a file with several modules which generates an end report. I wanted to reduce the size of the file I remove the modules from the end report though...Is there anything else that I can do apart from this to reduce its size

Hi guys,

I am about to undertake a fairly large spreadsheet development project. One of the key requirements is that the size of the spreadsheet be kept to below 4mb, preferrably below 1mb. This is due to an unavoidable restriction.

Although the spreadsheet will not have large volumes of data, it will contain approximately 30 sheets. There are no graphics embedded in the sheet. I imagine there will be a reasonable amount of VBA and/or forumulas in the sheet. Each sheet will basically be a data-entry form with a number of formulas. No sheet will have more than 100 rows.

Without knowing the full details of the spreadsheet, it is difficult for you to give a definite answer as to the size of the sheet, so I am simply looking for some helpful tips in keeping the size down.

Do formulas take up more space than VBA code? When I format cells, should I format the entire row, or just the range? Do features like sheet and cell protection take up a lot of space?

Any other tips or suggestions?

Hi all,

My spreadsheet has links to an external "CSV" file. At the end of each quarter, I copy/paste the linked workbook to a different folder corresponding to the new quarter, and extract the Oracle data to a CSV file in the new folder. However, the linked workbook seems to have the old path hard-coded in it, as it does not link to the newly created CSV file - even though it resides in the same folder as the linked workbook.

My first preference is to be able to change the path for the source data non-programmatically. However, if that's not possible or too cumbersome, then I'm open to using VBA code.

Thanks.

m

Hi all,
I have searched high and low for an answer here - I usually trust the Office Experts site to get me through with regards to Word but it seems to be "under maintenance" now for a long time.
My circumstance is that I have created a template in 2003. I will have users on both 2007 and 2003 using the template.
I have managed to work out some code to force the name and path when the user goes to save.
However I would like some code to give my 2007 users no choice but for the document to be saved as a doc rather than a docx.
So far I have the below (which I want to duplicate for FileSave as well).

Code:

'Dictate File Name in Dialog Box
Sub FileSaveAs()
With Dialogs(wdDialogFileSaveAs)
    .Name = "H:\Quality\CORPORATE DEPARTMENTS\" & ActiveDocument.Tables(3).Cell(1, 1).Range
    .Show
End With
End Sub

Is there anything that can be added here so the default type is a 97-2003 document?
Thanks in advance,
LizzieGirl

hi Bro,

I've just started on VBA in Excel.
I've read a lot of threads like my problem. But I can combine it to sovle my problem.

If I have a workbook with 3 sheets.

I want to have a button in Sheet1.

Base on value of A3 in Sheet1, if A3 = 2 so when click it, it will automatic export sheet2 to .txt file, and if A3 = 3, it will export sheet3 to .txt file.

If A1 or A2 or A3 is blank, it will do nothing if you press button.

The txt file name bases on sheet1!A1 and Directory name bases on Sheet1!A2.

If the directory exist before, it will ask you whether you want to replace old file.

When it finish export, it will inform you about success. exp :" Your file is successfully saved in C:\A2\A1.txt "

Thanks!