Hi,

Could you please help me this one?

I have an excel file with name "Credit Note template1.xls".
I need to activate the workbook,
Set xlWbook = xlApp.Workbooks.Open("H:\sreeja\Credit Note template1.xls") is working fine, but i need to open this file from another system. So for this i should copy this excel file to the path specified in the code. Otherwise it will not work.

Is it possible to activate this excel workbook without specifying full excel file path?

I tried the following way,

Set xlWbook = Workbooks("Credit Note template1.xls") , but is working fine with my system, but when i send to another system, it will not work.

Pls hlp me!!!

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.

Hi all

I am trying to import some csv files so I can combine them, but am having probs with the filename and location. Any thoughts. Thx in advance

VB:

Sub test() 
    Dim wsName As String 
     
    wsName = ActiveCell 
    Sheets("Data").Select 
    With ActiveSheet.UsedRange 
        LastRow = .SpecialCells(11).Row 
    End With 
     
    With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" \ " & wsName &", Destination:=Range("A" & LastRow)) 
        .Name = wsName 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .TextFilePromptOnRefresh = False 
        .TextFilePlatform = 850 
        .TextFileStartRow = 1 
        .TextFileParseType = xlDelimited 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = False 
        .TextFileTabDelimiter = True 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = True 
        .TextFileSpaceDelimiter = False 
        .TextFileColumnDataTypes = Array(1, 4, 4, 4, 1, 1) 
        .TextFileTrailingMinusNumbers = True 
        .Refresh BackgroundQuery:=False 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

I recieved some help from the Dude with a previous query regarding forcing N/A to 0 which has led me into a new one which is that by using

=IF(ISNA(YourFormula),0,YourFormula)

The formula is getting very long and i need to update the file path to the relevant data worksheet on a weekly basis for a large amount of rows. I tried copying the file path into a cell on another sheet within my workbook and then copying this cell into the formula e.g.

=IF(ISNA(VLOOKUP(A23,Data!$B$6,27,FALSE))*(VLOOKUP(A23,Costing,11,FALSE)),0,VLOOKUP(A23,Data!$B$6,27 ,FALSE))*(VLOOKUP(A23,Costing,11,FALSE)))

Where Data!$B$6 contains the file path to the workbook with the data
'[ MPS JAN.xls]2005'!$1:$65536

but i am not achieving any success, could anybody help?

This might be a little off topic, but I'll ask anyway.

I work on multiple workbooks. They all calculate a lot of functions and I am forced to create multiple workbooks to do these calculations. Linking to other workbooks basically displays the results of workbook1 and works from there.

But...

I need to keep an overview of these files, so I store them in different folders all within the same 'master' folder. So let's say: master folder is FolderMaster, and this contains folderA, folderB, folderC. FolderA contains workbookA1, workbookA2; folderB contains workbookB1, workbookC2 and finally folderC contains workbookC1 and workbookC2.

Suppose that this hierarchy might change. Moving eg workbook A2 to folderB. This would cause a #REF.

So I thought: organise your hierarchy in a 'master workbook' and store all paths to the individual files in a worksheet. If I link to a specific folder I do not directly link to the actual folder, but would use INDIRECT and grap the filepath from the masterworkbook. If the path to a workbook change I do not have to update all individual links, but only change the path in the masterworkbook. Right?

But how is the big one here. How do you formulate you path to a file? It can not be the entire file path since that's variable... (sometimes 'hd:User1:....'; sometimes 'HD:User2'...")

Hi,

I'm trying to programmatically generate a new workbook, save it with a filename based on a checmial name and filepath stored in a worksheet, then copy sheets from an existing .xls (is an 97/2003 format) into the new sheet.

Have read various post on how to do this but am persistently running into trouble as it always asks for the file path.

Note am using Excel 2007 but using/creating 2003 (.xls)

Here's a snippet of what I've done so far:

VB:

 
Dim Newwbk As workbook 
Dim FName 
 
Fname = Worksheets("Main").Range("B2") 
 
Set Newwbk = Workbooks.Add 
With Newwbk 
    .SaveAs Filename:=FName,Fileformat:=56 
End With 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I always get the Save As dialog and the filename is the excel default????

Have tried GetFileNameSaveAs thing, disable alerts etc but to no avail. IS this an Excel 2007 or a problem with my approach. Anyone else having the same problem.

Please help.

Maybe Ijust fired my brain but I am trying to paste several cells that contain formulas from one workbook to another without the full path to the original workbook being pasted. I want
this:

=IF(investment!D6>Amort!C10,Amort!A10,Tables!AA3)

Not This:

=IF('C:\Program Files\Exponenciel\Excel Templates for ACT!\Templates\[mymoney.XBT]investment'!D6>'C:\Program Files\Exponenciel\Excel Templates for ACT!\Templates\[mymoney.XBT]Amort'!C10,'C:\Program Files\Exponenciel\Excel Templates for ACT!\Templates\[mymoney.XBT]Amort'!A10,'C:\Program Files\Exponenciel\Excel Templates for ACT!\Templates\[mymoney.XBT]Tables'!AA3)

Hi all
I need a macro to do the following
1.Get list of .xls files from specified folder.
2.Append the files in new workbook in same folder.

Ex:
D:/users/excel/Sample_sheet1.xls
D:/users/excel/Sample_sheet2.xls
D:/users/excel/Sample_sheet3.xls

I want to get the list of Sample_*.xls and
create Sample.xls master file which adds the above three .xls as sheets in it.

Actually my macro as below

Code:

Sub GetExcelFileData() 
Dim strFilePath As String, strFilename As String, strFullPath As String 
Dim lngCounter As Long 
Dim oConn As Object, oRS As Object, oFSObj As Object 
Dim fileItem As Object 

Application.ScreenUpdating = False 

'This gives us a full path name e.g. C:tempfolderfile.txt 
'We need to split this into path and file name 
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT") 

Set srcFolder = oFSObj.GetFolder(ThisWorkbook.Path) 

'Open an ADO connection to the folder specified 
Set oConn = CreateObject("ADODB.CONNECTION") 
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=" & ThisWorkbook.Path & ";" & _ 
"Extended Properties='Excel 8.0;HDR=Yes';" 

Set oRS = CreateObject("ADODB.RECORDSET") 

lngCounter = 1 

'Now actually open the excel file and import into Excel 
For Each fileItem In srcFolder.Files 

strFile = fileItem.Name 

If Right(fileItem.Name, 4) = ".xls" And InStr(fileItem.Name, Left(strFile, InStr(strFile, ".") - 1)) = 1 Then 

oRS.Open "SELECT * FROM " & strFile, oConn, 3, 1, 1 
While Not oRS.EOF 
If lngCounter > 1 Then 
Sheets.Add After:=Worksheets(Worksheets.Count) 
Else 
lngCounter = 2 
End If 

ActiveSheet.Range("A1").CopyFromRecordset (oRS) 
Wend 
oRS.Close 
Columns("A:IV").AutoFit 
End If 
Next 

oConn.Close 

ActiveWorkbook.Saved = True 

End Sub

The main .xls has created and while im trying to open its throwing the below warning.

Run-time error '-2147467259 (80004005)':

The Microsoft Jet Database engine cannot open the file
\\testsystem\testdomain\exceloutput\. It is already oppened
exlusively by another user , or you need permission to view its data.

Could you please anyone help me on this....