I'm trying to open a file from server.After doing all the changes I have to save the file back to the server in the same name. It is asking for save as option.it shouldn't. I want to save the file in the same path and in same name without asking the option of "Save as". I hv full permission for editing the file
View 4 Replies
I have a macro which saves file to a specific folder. Problem is I can save the file only in my local drive C: but not on a server for some reason.
Here is the
At home in my home computer this code worked when C: was selected.
ChDir ("c:")
Title = Application.GetSaveAsFilename(Name, "Excel files (*.xls), *.xls")
ActiveWorkbook.SaveAs Name
Save the file in specific place
But when I substituted C: path with my server path it doesnt work! Have a look. Its the exact same code except my path is different.
ChDir ("\Lnf001Lnf1vol1SharedOP_ENGWork Order")
Title = Application.GetSaveAsFilename(Name, "Excel files (*.xls), *.xls")
ActiveWorkbook.SaveAs Name
I have a macros in my spreadsheet when I open it a number in the macros goes up 1 each time the worksheet is opened. I want to put this file on a server that can be accessed by various people. The problem I am getting is every time someone opens it from a different location with a different PC the number starts at 1 again. I want to keep the number going up 1 each time no matter who opens it.
View 4 Replies View RelatedI try to run a macro on my workbook everytime I open it by getting the data from the ftp file. This will be the service number for my customer. Both the excel and the data file are located in the ftp, so that I can retrieve the file wherever I go. Basically I got some code from the internet which work fine on the local network, but once it come to ftp server I am stuck there.
Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "Ftp://mycompany.com"
Const sDEFAULT_FNAME As String = "Service.txt"
Dim nFileNumber As Long
nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) <> "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber..................
How to pull data with a VLOOKUP to a file located on a Server.
My big problem is not understanding the syntax of the Server location. I've provided it below (obviously with some character changes).
I have a file located on my Hard Drive. I need to perform a VLOOKUP from A2 on this spreadsheet. The File on the Server is called "LookupTest.xls". The Range of Data is from Sheet 1 and is from A2:C4 and I need to pull the data from column C depending on what is selected from A2 from the file on my HD. I understand how to use VLOOKUP, just not when I'm pointing to another file on a Server.
Server mapping from Windows Explorer:
rbbabc$ on 'RP17409 - ABC Database (AHSSRVVN678.tge.com)'
Again, that server mapping is fake, as I changed it, but this is how it looks on Windows Explorer.
I'm trying to keep a master file of Data on a spreadsheet found on a server so that one master file can be updated, and several other tools pull from that Master File on VLOOKUP, Validation Lists, etc.
to copy files on computer which runs excell.
But is there a way to copy files from/to servers?
Any code that will grab the names of all files from all directories on an FTP server? I have the path & credentials to get to them, but I need a process to drop their names into a spreadsheet to compare against a list of files loaded onto a SQL database.
The directory structure on the server is subject to change, as are the number of files in each repository.
I am looking to automate our daily sales report.
I am aware of the windows task scheduler.
However, I'd like for this report to run and be sent out very early in the morning (5AM) before I'm even at work.
My computer is usually off because it's with me as I'm commuting into the office.
These files are saved out on our network drive.
Is there a way I can "open" the file and run the macro I have that builds my sales reports with my computer being off?
I have created a macro file which will fetch the data from sql server. Here is the code
f Me.Cmbchoose.Value = "" Then
MsgBox "Please Choose From the drop down Menu!!", vbExclamation
Exit Sub
End If
If Me.Cmbchoose.Value = "Merchants Reports" Then
Dim oConn As ADODB.Connection
[Code] .....
The code is working fine. Now my question is that I want to access it from my home. How do I do that without DSN set up?
I'm using VBA to upload the open workbook to app.box URL which is working great! Since I'm pretty new to VBA, I can't figure out how to get Excel to display the sucess/failed message from the server to the user. I used the VBA POST upload module from this awesome tutorial, but I can't figure out how to return the message to the user. I did figure out that on the website there is an "id="after_upload_message", not sure if that works. My code is below.
[Code] ......
For some reason the following Macro won't work:
Code:
Sub ExtractDataTest()
Dim FilePath$, Row&, Column&, Address$
'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Dxo.xlsx"
Const SheetName$ = "Open"
[Code] .......
I get a run-time error '52' on line ("Bad file name or number"):
Code:
If Dir(FilePath & FileName) = Empty Then
And when location is 'C:' it works and I don't get an error.
I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies View RelatedI have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy
[code]....
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
View 9 Replies View RelatedI was wondering if there is a way to write a macro that will take an excel file that I have called Alldata and then have it copy the first two sheets to a new workbook named something else( FullSparameter_0) and then copy the name of the third and fourth sheet into the new FullSparameter_0 workbook and then copy column A-E from sheet dB Alldata.xls and paste it into the FullSparameter_0.xls workbook in the same dB sheet in the same spot. And do the same thing with the Phase Sheet. i know this sounds confusing, but im really new to the VBA stuff.
I just need 15 different files from the original Alldata.xls that will be named FullSparameter_0, FullSparameter_1, .., FullSparameter_14. but each file will have the same two first sheets, Setup Information and Cal Verification, and the same last two sheet names, dB and Phase, but then in the dB and phase sheets, it will copy over the next four columns of data. so in FullSparameter_0 it will have column A-E from sheet dB in Alldata.xls in the dB sheet in FullSparameter_0 (Same with the Phase Sheets). Then in FullSparameter_1 it will have column A and Columns F,G,H and I (for dB and Phase). In fullSparameter_2 it will have column A and then J,K,L and M. Each FullSparameter_X will have the exact same first two sheets and then the other columns for the last two sheets will be pasted into columns A-E in dB and Phase.
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
I am trying to save my workbook in a specific directory with a specific filename and to incorporate a date field from within the spreadsheet. I have changed the format of the cell so it does not include / as i know this would not work. Saving it is the easy part but adding in the field from the spreadsheet is where im falling over.
View 2 Replies View RelatedI have a spreadsheet file (excel 2003). I'd like convert it in CSV with delim.
But when I save it appear message that say "the file contains some things not compatible with CSV with delimiter" then I can save it and lose information. After when I open it with Word it looks ok but delimiter is ";"
I set delimiter in windows and inside excel too to ","
But I can't undertand what are things not compatible. there are only words and number as general format....(there aren't "," and each other strange character).
How can I modify this code to simply display the pdf and not have it save to anywhere.....
Sub SavePDF_Click()
Dim UserSite As String
UserSite = Sheets("Site").Range("D102").Value
Sheets("View your Graphs").Select
Sheets("View your Graphs").Activate
[Code]...
How do I save the spreadsheet with the current name of the spreadsheet in a macro?
Example:
I want to save the spreadsheet to a network drive using the current name of the spreadsheet (it changes every other day). But I dont know to put the name in the SAVE statement. Here is what I have now:
Saving WORKSHEET file. I'm using this code:
View 4 Replies View RelatedI am trying to create a .csv file from an Excel file using VBA and when I use the ActiveWorkbook.SaveAs command, only the first row of text contains " quotes around the text. I have multiple rows that must contain the quotes. I have tried using """", and Chr(34), but then that puts """ (3 quotes) around my text. Is there a different function that I should be attempting to use? I am using concatanation to create a field where I would like the quoted text and am using the ActiveCell.Value command (ActiveCell.Text didn't seem to make any difference).
View 9 Replies View RelatedI have the below code to save a file once cretaed from master data, but it keeps failing on me.
View 3 Replies View RelatedI have a code that performs a task for me and will save the file at the end. It currently saves as a name I have selected and today's date. The problem I now face is that I will be completing this task several times a day and it will not work as the file names will be the same. Is there any way to save a unique name each time?
My code to save below:
Code:
ActiveWorkbook.SaveAs Filename:= _ "T:Lender UpdatesLender UpdatesLloydsCompleteAcknowledged " & Format(Date, "dd.mm.yyyy") & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
I am trying to develop a routine that will save the file I am currently working on, then save the current tab as a *.csv file with the name equal to cell "C1" in the same directory as the original file. Then I would like it to close the .csv file and reopen the original file all while being generic with the naming so that if the file name and location changes, the script will still work. I recorded a macro to get me started.
View 9 Replies View RelatedI have and xls list that needs to be saved in a text format without the use of "". The data needs to be read as data not as "data". The only way I have been able to do this is to save the file as a .prn and then rename the extension to .txt. My problem is that in order to rename the prn file i need to exit the file. If I do that then the macro ends and it will not rename. So I have worked around that by saving a second copy and then renaming the first copy. But I do not want to have a second copy. any ideas on how to get around this or to delete the scond copy from within the macro?
View 9 Replies View RelatedI have a workbook which utilizes 2 worksheets to fill a third. I would like a macro to be able to save JUST that worksheet, and not the other two. I thought there was a checkbox allowing you to "save selected" only, but I'm either dreaming, or it was in a previous version of Excel (I know use Excel 2000).
View 9 Replies View RelatedI am trying to save this file but I'm having problems with "my Name" This information is in a workbook that is already opened called "Recaps" and the sheetname is "Data" and the value I am looking for is in "$C$5". I open another workbook called "Spare" and want to rename and save it to the code below.
Dim myPath$, myName$
Application.DisplayAlerts = False
myPath = "C:Service RecapsJohn Mudaro"
myName = Workbook ("Recaps) Sheets("Data").Range("C5").Value
ActiveWorkbook.SaveAs myPath & myName
Application.DisplayAlerts = True
way to construct my save file so that it saves to the correct location.
I have a a FileSearch that lookings in a specific Folder and the sub folders for a file type and then saves them to a different location. Here is the location of the save in regards to the original location...
Filesearch folder= C:path
Savefolder=C:path he folder its found in & " excel converted"
If that makes any sense, basically I need to call the path of the file I opened, but not including the file name.
I have done this several times in the past, but don't do it often. I have an
Excel spreadsheet that I need to save in DBase IV format, but when I try, it
cu ts off all of the columns to the right of what I can see on the screen
when I go back into the DBF file.
