VBA: format newly created sheets as text

**maria.blue44** · 05-16-2013, 02:23 PM

I have a macro named checkURL which creates multiple sheets. The code is below.
I want to add some code so that as soon as sheets are created - it will format the created sheet as text and performs the required of the actions.

Please Login or Register  to view this content.

**tehneXus** · 05-16-2013, 02:35 PM

try

Please Login or Register  to view this content.

**maria.blue44** · 05-20-2013, 08:27 AM

This line is giving problem:My links are in sheet named weblink page.
Set MyRange = Sheet1.Range("A1:A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row)

If I runs the macro 2nd time it shows sheet1 is not available.

**maria.blue44** · 05-20-2013, 12:13 PM

Seems if is missing as well.

**maria.blue44** · 06-03-2013, 01:03 AM

Still looking for a working solution !!!

**tehneXus** · 06-03-2013, 02:56 PM

Hi,

if your data is in "weblink page" you should be very sure that this sheet will never be removed because you are using the codename in:

Please Login or Register  to view this content.

try

Please Login or Register  to view this content.

If this does not help it would be great to see a sample workbook

**maria.blue44** · 06-04-2013, 12:39 AM

Please find the attachment.

**tehneXus** · 06-04-2013, 08:36 AM

Hi, please check the file attached: Bookm.xlsm

I made some testing with financial data from yahoo and changed the following line, which, I think was your problem. Unfortunately the dates where created although the number-format was set to Text...

Please Login or Register  to view this content.

(Note: The URL list must be in "weblink page", if not you have to change it in the code or, rewrite code to ActiveSheet or sth.)

**maria.blue44** · 06-04-2013, 09:44 AM

In my page I have a value "9E10" which turns into "9.00E+10". For such cases only I want to turn my newly created sheets as text.

**maria.blue44** · 06-04-2013, 10:35 AM

So the solution did not work on my data. i.e .WebDisableDateRecognition = True

**tehneXus** · 06-04-2013, 10:42 AM

Could you provide a URL where "9E10" is in the table?

**maria.blue44** · 06-04-2013, 12:26 PM

I can not disclose the link. I thought to send you a message in your inbox but I am not allowed perhaps because of making duplicate post.

**tehneXus** · 06-04-2013, 02:45 PM

Hello,

So I found out that excel is guessing the format of the text its importing and then setting the format to whatever it wants, I did not found any property in the QueryTable object to prevent this.
Setting the NumberFormat to text either before or after the import does not change this.
The only thing I could image right now is a workaround, looping all imported cells, checking for a format which is not text and then changing the value with some string operations.

Sorry for not having a better idea

**maria.blue44** · 06-04-2013, 09:13 PM

I think its the only best idea

VBA: format newly created sheets as text

LinkBack

Thread Tools

Rate This Thread

Display

VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Re: VBA: format newly created sheets as text

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions