Macro Opening TXT As Workbook Changes Format

Apr 3, 2014

I am having problems to read a number inside a .txt file.

I use this code to open a .txt

Workbooks.OpenText filename:=data_DMC_filename _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array _
(1, 1), Array(2, 1)), DecimalSeparator:=",", ThousandsSeparator:=".", _
TrailingMinusNumbers:=True

At original file the number is like this format: 003791689000011403700010, but after opening as workbook it is like this 3,7917E+21

I tried changing the forma to text with this code bellow, but it looses information

Selection.NumberFormat = "0"

and the result is: 3791689000011400000000

I already wrote a lot of my code for other values, using this Workbooks.OpenText command, opening as workbook so I would not like to open the file in text format or so...

View 1 Replies


ADVERTISEMENT

Opening Workbook From Macro

Aug 23, 2006

I'm attempting to write a macro (in Excel) that uses a path that is given in one of the fields of the worksheet to open a workbook and get find a field to return to the original worksheet. I have working code to pull the information from a worksheet within the same workbook, but I can't seem to get it to work with an external worksheet. Can someone assist me in getting this to work?

I have attached my original code that works, so all I need to do is translate it to open the external workbook and worksheet instead of using the local worksheet. This is pretty simple right?

Public Function GetRate(strClassification As String, strContractNo As String) As Integer

View 9 Replies View Related

Run Macro Upon Opening Workbook

Sep 29, 2007

I would like to something arranged where a macro I currently have assigned to a button is automatically run once the workbook is opened. The reason being because I would it to be current with todays date. I have done the following and it results in nothing I still need to click the button to get the macro to work.

Private Sub Workbook_Open()
LightUpRows
End Sub

View 2 Replies View Related

Excel 2013 :: Format Date / Time And Macro To Run On Opening Orkbook

Feb 2, 2013

I need to place a date and time in a cell 14/9/2013 6:00 PM

Tried several formats under custom format without success.

Also need addition to following macro to enable it to start on opening of workbook

Code:

Sub Macro1()
Do
PauseTime = 1 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.

[Code]...

View 8 Replies View Related

Automatically Run Macro On Opening Workbook

May 19, 2009

I have attached a spreadsheet with a simple macro. How do i make the macro automatically run when the file is opened.

View 2 Replies View Related

VBA / Macro Automatically Opening Different Workbook

Dec 1, 2011

I wrote code for a few macros in a "save as" or practice version of a spreadsheet. Once I got the bugs worked out on the practice book I pasted the code into the actual spreadsheet I want to use. The problem is that whenever I run one of the macros it opens the practice book that I was using when I wrote the code. There should be no need for this and I can't figure out why that's happening. The code doesn't mention any workbook by name so I don't know why it would want to open the practice book.

View 4 Replies View Related

Autorun Macro When Opening Workbook

Jul 12, 2013

I need the following macro to 'autorun' when I open the workbook.

Sub stcopypaste()
'
' stcopypaste Macro
'
'
Columns("F:F").Select
Selection.Copy

[Code] ..........

View 2 Replies View Related

Macro Stops After After Opening Another Workbook

Sep 3, 2007

I am a novice at excel macro programming. My Excel 2000 macro inexplicably stops after the line: Workbooks.Open Filename:=stAddrFullname, ReadOnly:=True

1) I know that the remainder of the code is not executed because a breakpoint on the following line is never reached.
2) I know that this line is executed because the stAddrFullname workbook is clearly opened and is displayed when the code stops prematurely.
3) No error message is displayed and I have no 'OnError' instructions in the code.
4) (This is the bit I really don't understand). If I put a Breakpoint in the macro anywhere in the function preceding the "Workbooks.Open" statement, the macro reaches the Breakpoint and then runs through to completion with no problem. When I remove the breakpoint, it reverts to stopping as soon as it has opened the Workbook.
5) The stAddrFullname workbook has no macros in it.

View 5 Replies View Related

Macro - Opening, Copying And Saving Workbook To Worksheets

Jul 25, 2008

I have been trying to create a Macro that can do the following.

Open a Excel Workbook, Copy the Worksheets that I need (ignoring ones that are not needed.), Save them in the correct location with a new folder of date and time and saving them as csv files.

I have all the elements but can't seem to get them to gel correctly.

Just wondering if anyone has anything like this that I could adapt or edit.

View 9 Replies View Related

Stop Links Updating Opening Workbook With Macro

Jul 24, 2007

Currently I am using VBA Code below to Stop Update links prompts

Private Sub Workbook_Open()
Workbooks.Open "C:TempBook2.xls", UpdateLinks:=False
End Sub

Is there code I can use such This Workbook so that I can save this file as another name and it will work? Biz Auto Merged Post;Dear All, I have found VBA code that works.

Private Sub Workbook_Open()
Workbooks.Open ThisWorkbook.Name, UpdateLinks:=False
End Sub

View 3 Replies View Related

Show Custom Error Message If Opening Workbook Macro Code Fails

May 17, 2008

I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?

Private Sub btnOK_Click()
Application. ScreenUpdating = False
Dim LCSfile As String
LCSfile = frmSelectFile.Listbox1.Value
On Error Goto ErrHandler
Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV"
ErrHandler:
MsgBox ("File is not quantitated. Please select another file.")
Application.ScreenUpdating = True

End Sub

View 2 Replies View Related

Opening A File Without Opening A Workbook

Apr 17, 2007

I have a need to open a file from my companies intranet. My current method was to open said file via the method that the recorder gave me. However, I would like ot be able to open a said file without having to start open another workbook.

This is the path:
[url]

So the command is this:
Workbooks.Open [url]

Links are not actual links

So what I need to know. Is how can I open this file without opening a workbook. I haven't been able to use the VB "Open Statement" to open a file and I don't believe that I've been successfull using the Filesystem object either.

View 9 Replies View Related

Opening New Workbook And Pasting Values Into Current Workbook?

May 13, 2014

I am using the below code (i have combined different bits of code which i found on these forums) to open a new workbook, copy all the data in it and paste it into Sheet2 in the active workbook . Also, i want the data to be pasted on the next empty row as there is already data in Sheet2. The data that is being copied and pasted has the same format and it is going to be repeated many times.

The problem i am having is that it is not offsetting the data to the next empty row - i think it is a simple change but i can't seem to get there :s

Also, the code is currently selecting all of the data from the workbook that i open - is there a way i can select all of the data, excluding the first row (the first row contains the headings which i already have in Sheet2 in the active workbook).

[Code].....

View 5 Replies View Related

Copying Data From Userform To Another Workbook Without Opening Second Workbook

May 16, 2014

I have created userform and it works fine. Following code assigned to 'SUBMIT' button in userform - works fine. I am trying to include code where certain data from userform is also copied to workbook2 ( of course without opening it)- as marked in red...below

[Code].....

View 4 Replies View Related

Getting Data Form Workbook Without Opening The Workbook

Apr 6, 2014

Can I search a document without opening it?

If not how do I open a file if not already opened without using "ON ERROR"

View 3 Replies View Related

Opening A Workbook Then Switching Back To First Workbook

Apr 25, 2009

I am trying to make one of my macros open a file then switch back to the orignal file and then running the macro.

here is my

View 11 Replies View Related

Save Data From One Workbook To Another Without Opening Workbook

Mar 1, 2013

I have a spreadsheet that data gets exported to from a software program. There is information such as customer name, part number, inquiry number, etc that I'd like to use to develop a log of all my estimates I create for parts. I'd like excel to take the cells I select and insert them into certain cells in the other workbook and automatically save that workbook. This will create a log of all my estimates where I can then link the full estimate to the log. The information will have to go in the next available row. Is there any way I can accomplish this without having to open the other workbook and save the work book each time?

View 1 Replies View Related

Prevent Workbook Opening If Other Workbook Open & VBE

Aug 29, 2006

how to prevent a workbook from opening if another excel document is open and if a visual basic window is opened.

View 8 Replies View Related

Opening XML Format Files?

Apr 5, 2013

I need to open all files in a folder to modify a date in the first line. The files have a .cdr extn for a particular application but are basically text files with xml type tags. What would be the best way to proceed?

I tried opening as text in VBA but they are blank.

View 1 Replies View Related

Format Not Staying When Opening On Different Computer

Mar 6, 2014

I have created a file on a 24" iMac, but when i open it on a smaller laptop the cell sizing is not the same and i cannot see the full cells or the numbers in it which is difficult, because i have someone else working on the file and i have it locked so they can't see the data unless i unlock the sheet which is something i don't want to do.

View 4 Replies View Related

Error Opening File......different Format Than What Specified By Extension

May 15, 2009

Apparently my program quits after i try to open an excel file. If you open the file manually you get an error upon opening the file...something like this:

"File you are tyring to open is in a different format than specified by the file extension".

The problem is i am using an excel macro using VBA and once the file is opened the VBA execution quits.

The excel file turns out to be an xml file which excel can open the file and have the VBA continue running if i RENAME the file with an "xlsx" extension. The problem is that i cant ask the user to rename the file.........how do i get VBA to open the file without an issue?

View 13 Replies View Related

Workbook Keeps Opening By Itself?

Jun 12, 2013

I have just added some code to make my workbook close after it has been inactive for 5 minutes...this works fine except it opens itself up again after 5 minutes. If I select 'Enable Macros' it closes immediately and if not, it will stay open.

Here is all of the code I put in for the auto shut down:

ThisWorkbook:

[Code]....

Module 1

[Code] ....

View 3 Replies View Related

Opening In Different Workbook

Jan 6, 2007

I currently have a workbook that, when opened, automatically kicks off a macro (with a call from the Workbook_Open event in ThisWorkbook). I was to run that same called macro from a custom button that I put on a toolbar in Excel itself...which is pretty straight forward. However, when I do this, the macro kicks off twice...once from the button and once because the called macro's workbook opens and kicks off the macro with the Workbook_Open event.

So, what I was thinking was...is there a way to tell if a workbook is being opened by another workbook (by calling it's macro) or whether is was opened "normally (manually)"?

View 9 Replies View Related

Workbook Opening Twice

Nov 9, 2006

My friend emailed me a workbook. When she or I open the workbook, it opens two instances of it. Is there an option in excel that is causing it to do this? I need to send this a client and I do not want this to happen on their side.

View 6 Replies View Related

Opening Workbook On Sheet 1

Apr 23, 2014

I have a workbook that consists of 3 sheets, it's a template I use. Once I am done filling out sheet 3, I save as a new workbook. When I have to go back to the template workbook it always opens on sheet 3. Is there anyway that once I open the template workbook it will open on sheet 1?

View 1 Replies View Related

Add-in Opening A Workbook As Read Only

Feb 9, 2009

I have created an Add-in for use across the business, the add in calculates financial impact based on figures that are stored on the shared drive.

The addin currently opens the workbook, and sets the variables to figures contained in the workbook - and then closes the workbook.

To roll this out to the business - I need to get the add-in to open the workbook as read Only, so that it will allow multiple users to access at once. I have set to open as read only in the past - however this prompts me to save the workbook as something else.

Is there any way I can get this to just open as read only, do what it needs to - and then close; without having the option to save as something else?

View 8 Replies View Related

VBA - Opening A Workbook On A Network

Sep 29, 2009

As above i have a macro which opens a workbook which is on a network drive. This works fine for my multiple users...however i have discovered that if they do not have the drived mapped with the same drive letter at the start...it will throw up an error advsing that the file does not exist. Is there any way to by pass this.

Ex - "TestFile.xls"

File is stored in "E:NetworkTeam1Testfile.xls"

However, some users have access to this file and it is mapped as E:, but some users have been mapped as F: The advisors who have been mapped as F: cannot access the file. Is the VBA/Macro smart enough to bypass the drive letter and just search the rest of the past...NetworkTeam1Testfile.xls".

View 2 Replies View Related

Opening Workbook With Code

Nov 8, 2012

Why can't I get this code to work? The first line seems to work ok but not yet sure as the rest doen't work.

I have tried NEXT_YEAR as a string and as a number but I keep getting an error.

Sub test()
NEXT_YEAR = "2013"
ChDir "J:Maxtor backupFire DeptVacations " & NEXT_YEAR
Workbooks.Open(Filename:= _
"J:Maxtor backupFire DeptVacations " & NEXT_YEAR "VacGrp - 1 - " & NEXT_YEAR & ".xls""", UpdateLinks _
:=3).RunAutoMacros Which:=xlAutoOpen
End sub

Below is the code I am trying to replace and it works fine.

ChDir "J:Maxtor backupFire DeptVacations 2013"
Workbooks.Open(Filename:= _
"J:Maxtor backupFire DeptVacations 2013VacGrp - 1 - 2013.xls", UpdateLinks _
:=3).RunAutoMacros Which:=xlAutoOpen

View 5 Replies View Related

Define Workbook Name After Opening?

Jun 2, 2014

I have a code that opens a workbook and then calls a function on the workbook. What I want to do is reference that workbook so that when the function ends I can make that the active workbook again.

This is my code

Code:
Sub BarcLinkedCleanSort()
Dim fNameAndPath As Variant, wb As Workbook
Dim ws As Worksheet

[Code]....

View 1 Replies View Related

Email Upon Workbook Opening

Jul 28, 2006

I hope this is not a violation of any of the security rules. At work, we are wanting to track when users open up a workbook by sending an email (not my idea). I have the email on open part working just fine, I wanted to know if there is an option that it does not save the sent message in the sent folder?

Private Sub Workbook_Open()
Run "EmailMacro"
End Sub
Sub EmailMacro()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "User xx has opened workbook" & vbNewLine & vbNewLine & _
"..." ...................

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved