Problem in the data of the file in the FTP after the saving of excelsheets

[Yuvraj]

Hi All,

I am facing a strange problem in the save code.

The details are as follows:

Public Function SaveData() As Boolean
For intcount = 1 To intMgrCount + 1
strSheetName = ThisWorkbook.Worksheets("A").Range("P3").Offset
(intcount - 1, 0).Value
Set mwksDataStore = mwbDataStore.Worksheets(strSheetName)
Set rngTgt = mwksDataStore.UsedRange
rngTgt.ClearContents
Set rngTgt = mwksDataStore.Range("A2")
Set rngSrc = ThisWorkbook.Worksheets(strSheetName).UsedRange
Set rngTgt = rngTgt.Resize(rngSrc.Rows.Count,
rngSrc.Columns.Count)
rngTgt.Value = rngSrc.Value
Next intcount

fMsg.msg = "Uploading data. Please wait..."

saveData = saveSPTes()

End Function


//The Problem lies here
Explanation: The application gets the file from the FTP and saves it
in Temp folder and then opens the application using that data.

When I save the application workbook which is ideally the abcd.xls in
the Temp folder using

mwbDataStore.save
mwbDataStore.Close
these two lines of code it saves the abcd.xls in the Temp folder and
then using upload function i zip it back to 8657.zip in the FTP.

I do not know that some of the sheets which it saves say sheet1 in
8657.zip

have data
#### a b c d
09/06/09 e f g h
09/06/09 i j k l

I am not understanding while saving the data it showed 09/06/09 in
case of ####, but when i open the file which it ha put back to server,
the 09/06/09 for first line is changed to #### though i am just using
"mwbDataStore.save" which just saves the workbook as it is.

Why it happens only for the first line and also it says if you put the
cursor on #### that negative dates are not allowed, but ideally before
i save the workbook i saw that the dates were not negative. It is same
as the other rows, Is that some thing to do with the excel properties
what negative as everything is same as other rows.

Private Function saveSPTes() As Boolean
strXLS$ = mwbDataStore.FullName
strZIP$ =8657
mwbDataStore.save
mwbDataStore.Close
Set mwbDataStore = Nothing

With myFtp
.Connect
saveSP = .upload(tempDir() & strZIP$, FTP_DATA_PATH & strZIP$)
End With
End Function



Please help guys.

I am a bit perturbed with this strange behavious of the
mwbDataStore.save

Regards,

Yuvaraj

 

[Joel]

The #### are only due to the column width being too narrow. Just increase
the widtrth of the columns and everything will be ok.

You don't have to zip the excel file if you FTP using binary mode instead of
text mode unless there is a firewall that is blocking XLS files.

 

[Yuvraj]

The #### are only due to the column width being too narrow.  Just increase
the widtrth of the columns and everything will be ok.

You don't have to zip the excel file if you FTP using binary mode insteadof
text mode unless there is a firewall that is blocking XLS files.






















- Show quoted text -

Thanx Joe for the reply, but the issue is different.

I am not worried if the #### is having the date which can be seen by
increasing the length of the row.

Ideally the #### i am talking does not contain any date it is just
############ till infinite and does not have any value.

The reason why it is happening is because if you right click on that
cell and see the format then the format of that has been changed from
general to date which is ##########.

For the other cells that format is general hence it displays as date
ideally,09/06/09.
I am woried because that is not something whichi is done by the code,
then why while saving it is changing the format of the cell from
general to date format which gives the value as #### and that is also
happening for only one cell.

Regards,

Yuvraj

 

[Joel]

I can't tell from the code if this is an XLS file or CSV. I suspecting it is
CSV which is really a text file with field seperated by commas. I was
assuming it was XLS. Below are my comments if the file is CSV. XLS should
not cause the problems you are seeing.

Normally when you transfer CSV data from one software package to another
software package you add delimiates like double quote to provent accidental
conversion. Excel is performing automatic translation on number that it
thinks is dates when it shouldn't.

I don't know if the problem is with saving or reading the CSV data. You
need to open the CSV file with Notepad to determine if the problem is with
the saving or reading. I can write a macro which will save the spreadsheet
data and put double quotes around each field or single quotes.

Let me know if you need additional help.

 

[Dave Peterson]

So what do you see in the formula bar? Or after you've changed the number
format to General?

Saved from a previous post.

It could mean a few things.

1. The columnwidth is too narrow to show the number.

Widen the column or change the font size of that cell. Or change the
numberformat to General.

2. You have a date/time in that cell and it's negative

Don't use negative dates. If excel was helping you, it may have
changed the format to a date. Change it back to General (or some
other number format).

If you need to see negative date/times:
Tools|options|Calculation Tab|and check 1904 date system
(but this can cause trouble--watch what happens to your dates
and watch what happens when you copy|paste dates to a different
workbook that doesn't use this setting)

3. You have a lot of text in the cell, the cell is formatted as Text.

Format the cell as general.

4. You really have ###'s in that cell.

Clean up that cell.

5. You have # in a cell, but it's format is set to Fill.

Change the format
(format|cells|alignment tab|horizontal box, change it to General.

 

[Yuvraj]

I can't tell from the code if this is an XLS file or CSV.  I suspectingit is
CSV which is really a text file with field seperated by commas.  I was
assuming it was XLS.  Below are my comments if the file is CSV.  XLS should
not cause the problems you are seeing.

Normally when you transfer CSV data from one software package to another
software package you add delimiates like double quote to provent accidental
conversion.  Excel is performing automatic translation on number that it
thinks is dates when it shouldn't.

I don't know if the problem is with saving or reading the CSV data.  You
need to open the CSV file with Notepad to determine if the problem is with
the saving or reading.  I can write a macro which will save the spreadsheet
data and put double quotes around each field or single quotes.

Let me know if you need additional help.










- Show quoted text -

Thank you Joe for the Reply.

Please find the few more details

The file say abcd.xls has two sheets "Sheet1" and "Sheet2".

The Sheet1 data which ideally gives this issue is the processed data
which after being processed via a scheduler program copies it into
abcd.xls used by my excel application.

Ideally the file which opens up is abcd.xls but the workbook.save the
abcd.xls gives problem in the Sheet1 which is ideally the data copied
from a csv file via scheduler.

In short when the application opens up abcd.xls and we go to the
Sheet1 which has the csv data being copied it is the same date
09/06/09 but when my code does workbook.save to save it the first row
ideally of the Sheet1 which was the processed data from csv files via
scheduler gets affected.

I thank you for the help but it would be great if all of you can help
me with the solution to this.

Moreover if you can send me that code which can convert the text
format to date format it will also help but i am worried how will i
change the particular date column out of 10 columns in the csv files
processed by the scheduler where will i fit it in.

Regards,

Yuvaraj

 

[Yuvraj]

So what do you see in the formula bar?  Or after you've changed the number
format to General?

Saved from a previous post.

It could mean a few things.

1.  The columnwidth is too narrow to show the number.

    Widen the column or change the font size of that cell.  Or change the
    numberformat to General.

2.  You have a date/time in that cell and it's negative

    Don't use negative dates.  If excel was helping you, it may have
    changed the format to a date.  Change it back to General (or some
    other number format).

    If you need to see negative date/times:
    Tools|options|Calculation Tab|and check 1904 date system
    (but this can cause trouble--watch what happens to your dates
    and watch what happens when you copy|paste dates to a different
    workbook that doesn't use this setting)

3.  You have a lot of text in the cell, the cell is formatted as Text.

    Format the cell as general.

4.  You really have ###'s in that cell.

    Clean up that cell.

5.  You have # in a cell, but it's format is set to Fill.

    Change the format
    (format|cells|alignment tab|horizontal box, change it to General.











--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you Dave for your reply.

Dave ideally the code opens up abcd.xls file and then copies the
Sheet1, Sheet2 of abcd.xls to the application excel file ideally
app.xls and then the code does workbook.save which saves the app.xls
so when the application opens it should show the changed data.

The problem caused is the abcd.xls Sheet2 data which is in general
format, ideally processed by the Scheduler.

The application when it copies the abcd.xls Sheet1 data to app.xls
Sheet1 it shows the correct Date with the format as General, but when
it is saved in the File Server, it shows the format changed to Date.

So as that was a general format, and when automatically gets converted
to Date format shows ####.

This happend on workbook.save function which ideally saves app.xls and
loads it to FTP so the application again opens it from FTP next time
app.xls.

The points mentioned by you is very significant.

I want to know that the app.xls has Sheet1 and Sheet2.

Ideally Sheet1 copies abcd.xls Sheet1 data which is in general format,
but after workbook.save that abcd.xls Sheet1 data which is in general
format gets changed to date format on app.xls after workbook.save.

Sheet1 data of abcd.xls was processed by the Scheduler and was pasted
there. Ideally the file processed by the scheduler was a csv which
after processing was pasted to an excel file abcd.xls.

Ideally when we open abcd.xls Sheet1 in independant mode it shows
General format for Sheet1 but when it is copied to app.xls via
application and saved by workbook.save function whicg ideally saves
app.xls the Sheet1 in app.xls changed to date format from general
format hence shows ####.

Why this conversion on its own Dave.

Please help and do ask me if you all need any information.

Regards,

Yuvraj

 

[Dave Peterson]

I don't have any idea why your program that saves the file converts the dates to
####. I don't know enough about what FTP program you're using to help.

But I would look at the original data to see if those dates were really dates.
If you use:

=isnumber(a1)
where A1 contained one of those "dates", do you see true or false?

I'm guessing that those values are not really dates (based on your description
of the numberformat of the cells).

But when you paste them into another worksheet, it's like typing the value
yourself. Excel likes to help and if it looks like you're typing (or pasting) a
date, then excel will treat the value as a date and change the number format.

You may want to test by changing the columnwidth after the paste to see if that
helps with the FTP upload. (Just a guess.)

If it does, you're lucky.

 

[Yuvraj]

I don't have any idea why your program that saves the file converts the dates to
####.  I don't know enough about what FTP program you're using to help.

But I would look at the original data to see if those dates were really dates.
If you use:

=isnumber(a1)
where A1 contained one of those "dates", do you see true or false?

I'm guessing that those values are not really dates (based on your description
of the numberformat of the cells).

But when you paste them into another worksheet, it's like typing the value
yourself.  Excel likes to help and if it looks like you're typing (or pasting) a
date, then excel will treat the value as a date and change the number format.

You may want to test by changing the columnwidth after the paste to see if that
helps with the FTP upload.  (Just a guess.)

If it does, you're lucky.



















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanx Dave,

The FTP and all is no issues at all dave ideally what gets saved in
Temp folder is put to FTP.

I generally see what is saved in Temp folder app.xls file.

Regarding the sheet1 of app.xls which i am saving is not in date
format, It is general format(ideally text format) but the value there
is a date like 09/06/10.

Hence when i save app.xls out of 10 values which is ideally date but
in text format, 2 gets converted to date format on its own in the
sheet1 of app.xls.

This I do not want I want the format which is text to e retained
though the value in that Sheet 1 is a date, not in date format.

Hence 2 out of 10 gets changed to date format hence gives ####.

Generally it is the topmost rows.

Regards,

Yuvraj

Problem is that when we save it by

 

[Dave Peterson]

If the cell is formatted as General and you see that 09/06/10 in the cell, then
the value is not a date--it's just a string.

You could verify this by using:
=isnumber(a1)
where A1 contained that value.

(You sure you checked the numberformat of the cell, right?)

======
But I'm confused.

Do you see the ###'s in your saved excel file or do you only see them in your
uploaded (after the FTP) data file?

If you don't see them in excel, then the problem is not with excel. If you see
them in excel and the values are really dates, then you should be able to widen
the column to see the date.

It sounds to me (and this is just a guess) is that the program that's displaying
the FTP'd data expects a number (dates are just numbers formatted nicely in
excel) and it's not getting them.

So I would look at those cells that cause trouble once more (in app.xls) and
make sure that the values are really dates--not just strings masquerading as
dates.

Yuvraj wrote:

 

[Yuvraj]

If the cell is formatted as General and you see that 09/06/10 in the cell, then
the value is not a date--it's just a string.

You could verify this by using:
=isnumber(a1)
where A1 contained that value.

(You sure you checked the numberformat of the cell, right?)

======
But I'm confused.

Do you see the ###'s in your saved excel file or do you only see them in your
uploaded (after the FTP) data file?

If you don't see them in excel, then the problem is not with excel.  Ifyou see
them in excel and the values are really dates, then you should be able towiden
the column to see the date.

It sounds to me (and this is just a guess) is that the program that's displaying
the FTP'd data expects a number (dates are just numbers formatted nicely in
excel) and it's not getting them.

So I would look at those cells that cause trouble once more (in app.xls) and
make sure that the values are really dates--not just strings masqueradingas
dates.

Yuvraj wrote:

<<snipped>>













--

Dave Peterson- Hide quoted text -

- Show quoted text -

I see it in the saved excel file Dave, upload does not have issue.

Ideally what the value in the cells before save is a text or string,
after workbook.save the text format or general format of the cell
changes to date format in the saved app.xls.

Hence it shows #### and this is till infinite you cannot see that. Yes
you can see the message ideally there which I mentioned before.

But Dave the value before save in those cells are text and after save
it becomes date format hence shows the ############## value there.

Ideally it does for first few rows.

I am perturbed why first rows only what has happened.

The only thing I can add is that the abcd.xls has got those data
pasted from a csv files and app.xls gets it pasted by abcd.xls.

Please consider my thanx for your assistance.

Regards,

Yuvraj