open an excel file that has spaces in the filename

I am trying to work with a series of excel files from access that sometimes have spaces in the name..... example "Hello World.XLS" instead of "HelloWorld.XLS" I have no trouble opening space-free filenames but when there are spaces , I get a file not found error.

Is there a way to open files that have spaces using

... .workbooks.open("file name.xls")

or do I need to get all the files re-named?
thanks again
p


Post your answer or comment

comments powered by Disqus
I need to open an Excel file that has been stored in the Access Database using the insert Object functionality of MS Access manually.

What i am aware of is that i cant just read the field containing the Excel File into a Byte Array and pass it to the Excel object in C#,as the file is wrapped in the OLE Wrapper used by Access while inserting the file in database.

I have tried locating the Header of Excel file from the byte array and read the file from there on but it is not working.

while (true)
{
if (0xE11AB1A1E011CFD0 == BitConverter.ToUInt64(byStream, i))
break;
i++;
}
output.Write(byStream, i, byStream.Length - i-1);

byStream is a byte array into which i have read the Excel file from Database.
I am locating the Excel file header in the byte stream and am writing the byte array to a file from that location.But on opening the written file it dosent work.
Similar approach had worked in case of Images but now in this case.

Can some one please tell me as to how i can open the Excel File.
Can I use Interop.Access object to achieve the goal??

Hi

What I want to do is following:

Open an Excel file based on an existing Access table. This event will be triggered by pressing a button on a form.

Could anyone please point me in the right direction?

Regards

how can I open an excel file from my forms?

I'm looking for a way of opening an excel file directly after running a report to export to excel using vba code

Hello,

I want to setup some code to open an excel file the immediately after opening it I want to click save as and overwrite the existing file.
I have the code setup correctly for opening the file but I am not sure how to overwrite the existing one.

Dim dbs As Database
Dim exceltest As Object

Set dbs = CurrentDb
Set exceltest = CreateObject("Excel.Application")

exceltest.Visible = True
exceltest.Workbooks.Open Filename:="C:Watchtest.xls"

Any help would be great.

Thanks
durdle
________
no2 vaporizer review

Hi Guys,
Sorry for posting also here and to excell but it's related.
When I try to import an excell sheet that has dates in the form
19/9/03 to access, I get type conversion failure.
If the date is 19/09/03 then it works fine.

I have also one other column with numbers. If there is space between them it gives me again a type conversion failure, even though I format the cell to text.
For instance: 345 678 ->error
but 345678 works ok.

So I need a function to remove internal spaces from this column.
I also need a function to put 0 before the month unless someone has a better idea on how to make it work.

Thanks in advance,
George

How can you open an excell file from out of an access database?

Grts,

Gino V.

Hi there, I'm currently using a database that is acting as a Document Management System. It's main function is to tag documents with properties and custom properties.

I have successfuly been able to tag word, excel and powerpoint documents but have run into trouble with PDF files.

It seems I can't write to a pdf's properties from access, however I have been able to do this from excel? Hence to tag a pdf file I have been opening an excel file that has code along the lines of:

' SET ADOBE ACROBAT OBJECTS
Set AcroApp = CreateObject("AcroExch.App")
Set AVDoc = CreateObject("AcroExch.AVDoc")
Call AVDoc.Open(sSourcePath & "" & sResourceID, "")
Set AVDoc = AcroApp.GetActiveDoc
Set PDDoc = AVDoc.GetPDDoc

' SET DOCUMENT PROPERTIES
PDDoc.SetInfo "Property Name", "Values Saved"

Any ideas how this can be incorporated into Access? For the excel tool to work I have added a reference to PDFMaker.xla

Any help with this would be greatly appreciated, it's fair to say this has been driving me nuts...

I created an Excel file using:

DoCmd.OutputTo acOutputQuery, _
"Compile_Query", acFormatXLS, _
"G:ServiceCompanySanDiegoWarehouseLCDLine_Summ ariesDaily" _
& DateMonth & DateDay & DateYear & ".XLS", False

However, after creating this excel file, i want it to automatically open.

Is there any way to do this in 97?

Hi,

I have created a data entry form and one of the things I would like to do is to have an Excel file open automatically if the value in one of the data entry form fields is above a certain criteria (this will then trigger the data entry person that they need to complete another file, which is not yet linked into Access).

Is it possible to do this? If so, any advice on how to do so would be great. I am very new to Access and don't yet know VBA or SQL.

If it's not possible by this method, would it be possible to trigger it to open another data entry form in the same database? (in which case I could expedite my plan to bring the other data over into Access).

Thanks.

i have an excel file named form.xls

i would like to create code by pressing a button to open that form.xls, print the active sheet (sheet2) and the close ms excel.

can somebody help me?

thanks

Hi sorry to post again, but you are all very helpful and ive almost finished by DB.

I have a button that has to export 4 queries into excel spreadsheets, and then open an excel spreadsheet that has links to the 4 exports and does some nuber crunching and produces a report.

the button code is:

Dim fullpath As String
fullpath = CurrentProject.Path
' exports expenditure
DoCmd.OutputTo acQuery, "qryExpenditureExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "Exp.xls", True, "", 0
' exports income
DoCmd.OutputTo acQuery, "qryIncomeExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "In.xls", True, "", 0
' exports priority debts
DoCmd.OutputTo acQuery, "qryPriorityDebtsExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "PRIO.xls", True, "", 0
' exports non priority debts
DoCmd.OutputTo acQuery, "qryNPDExport", "MicrosoftExcelBiff8(*.xls)", fullpath & "NPD.xls", True, "", 0
' Access form button
Call OpenSpecific_xlFile


It calls this code to open the file:

Sub OpenSpecific_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sfullpath As String
Dim sPath As String


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sfullpath = CurrentProject.Path & "FinancialStatementTemplate.xlt"


' Open it
With oXL
.Visible = True
.Workbooks.Open (sfullpath)
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''
' Run it from a form button

Private Sub cmdOpenExcelFIle_Click()
' Access form button
Call OpenSpecific_xlFile



I need to then make sure that FinancialStatementTemplate is then the top window so that it can be printed.

how do i set the focus on this specific window?

Thanks in advance.

Kev

I am having a problem getting Access to output to an excel file.

I want to take a form that I have that has 20 or so records with 6 or 7 fields for each and put it into an excel file that is formated the way I need everythign to look.

I also need it to make the file name such as CCCAAAMMDDYYYY (3 letter company abbreviation,3 letter initials and then todays date) I am thinking that the company abbreviation will be pulled from a query, then initials will be a form that opens, and the date can be pulled from access' date function.

I tried using vb code and doing an output to comand but that did not work.
I am confused someone please help if possible.

All,
I am using access 2003 and I need help on importing data. I have already setup a table I to import data and I am using it in a subform on my main form. The first table is the invoice table which I have as the main form for data entry. The users create a record inputting the invoice number and corresponding info. The users then switch to a subform to input data corresponding to the invoice table. I have a combobox on the subform so the users can select the invoice number. It should auto populate the corresponding fields from the main form. Next it should auto populate the remaining fields in relation to the remaining fields. But, the information in the subform is imported from an excel file that I have to setup so the users can push an import button and populate this existing table. The main table contains the invoice # and the imported table has invoice # and the tracking number. I have setup a 1: many relationship between these two tables. The invoice # can have many tracking number records. The imported file contains records with the invoice # and tracking number then corresponding data. I at a lost in setting this up. I think my problem is that the users create the record with invoice# before data is imported to the corresponding table. After the users select the import # from the subform they still would have to select the corresponding tracking #. Can someone please help me with this design and thought process.
Thanks

Hi,

I need to open an excel *.xlsx file from Access 2010 using a macro or VBA but can't get any of the examples on various forums to work. Please help.

Thanks in advance.

Stu

I wanted to try formatting an excel file that gets spit out by my Access program so I didn't have to make manual format changes in Excel. I got good results and all, but the excel file that is created is EXTREMELY large. The original excel file, without any auto-formatting from me, is about 60 KB. This file is about 10 MB.

All I did was color the headings grey, set the text in the headings (first row) to bold, and fix column widths. This is done in all 4 tabs. The workbook has a total of maybe 100 records....not even. Why would this make the file so large?

What can I do to prevent this?

I have a database which allows users to import data from an excel file that is located in /forms/user.xls off the root of the database. There is a button to open the spreadsheet to edit it and a button to import the data from the spreadsheet. The problem is that the spreadsheet retains the information and if the import button is clicked a second time the information is improted twice. None of the fields on the spreadsheet correspond to the primary key in the table, so it is possible to wind up with duplicates. Is there some VBA I could add to the button's on click event after the import code that would wipe out all the rows but the top one on the spreadsheet or replace the spreadsheet with a blank one from somewhere else?? Thanks

I am working on supporting spaces in the DB filename. I got the rest of my program working... other than being able to invoke Access with the specified filename for it to open.


	Code:
	C:Documents and Settingsc_mlueckMy DocumentsFandango>"C:Program FilesMicrosoft OfficeOffice12MSACCESS.EXE" "Schema
Ideas.accdb"

C:Documents and Settingsc_mlueckMy DocumentsFandango>dir "Schema Ideas.accdb"
 Volume in drive C has no label.
 Volume Serial Number is C8E4-5856

 Directory of C:Documents and Settingsc_mlueckMy DocumentsFandango

01/18/2013  04:45 PM         3,203,072 Schema Ideas.accdb
               1 File(s)      3,203,072 bytes
               0 Dir(s)  125,846,482,944 bytes free

The envoking of MSACCESS.EXE results in:



Does Access 2007 MSACCESS.EXE not support DB's with a space in the file name being spefied to auto-open via command line switches?

Hi I have been looking around if I could find a simple way to open a specific excell file from access: I need to open a excell file from access located in a specific location "C:Users....." from a comand in Access, when then I close the excel file I should return to my Access page.
Thanks
M

What is the best way to import an Excel file into multiple tables in access?

I have an excel file that has numbers stored as negative numbers. How can I convert the number to a positive?

Hi,

Is it possible to use the data of an excel file that is on a sharepoint. I have already imported the sharepoint list, but there is have only the ulr to the excel but i want to import the data from that file.

Can someone help me out?

Kind regards

Koen

OK this one isn't vital or anything, I was just curious...

Inevitably I run across a field that has spaces in the heading. E.G. Field 1. Recently I have done so with a huge table filled with these types of field names (i.e. field names with spaces).

When I drag and drop these fields down ina query, Access seems to recognize the field names with spaces and doesn't put brackets around these values by default in the GUI... however, if I click the SQL statement that is made, all the field names with spaces appear with brackets around them and the ones that don't have spaces do not.

The kicker is that if you now change this from a SELECT query to an APPEND query, when you choose the table to append to... Access will auto fill all the fields with the same names, put brackets around the fields that have spaces.

Then when you try to run the append query Access throws this error:
Quote: The INSERT INTO statement contains the following field name:'[Field 1]' Make sure you have typed the name correctly, and try the operation again. When you look at the SQL, though, everything that should have brackets does... so how do you fix it?

The answer is to remove the brackets in the GUI, but this is very time consuming if you have a table with a ton of "spaced field names".

The crazy thing is that once you remove the brackets in the GUI, Access remembers that you've deleted them. But where? How? Even if I remove the brackets in the GUI, if I then look in the SQL the brackets are there for both the INSERT INTO and the SELECT statement... So how does Access know, to leave the brackets off, at the append line, once you've deleted them?

Because I inherited a GIANT table filled with these types of field names, whenever I create an append table, I have to go through and delete all the brackets by hand... this is uber-lame!

Does anyone know of an easier way to deal with this? Or how I can get Access to force brackets initially, for field names with spaces. Seems that'd be a setting or something... why would they force brakctes for the instert but not for the select? It doesn't make any sense. Seems like if that throws an error they'd want to keep it consistent.

Anyway, if you have some insight as to the best way to deal with this, I'd be please to learn something new... I know what some of you might say... but please don't tell me not to use spaces in field names... that won't be helpful as I already know not to do this... like I said, sometimes the rest of the world doesn't know it's a no no

Thanks,
Gary

Every morning, one of our employees gets an email with an Excel file that must be downloaded, and the pertinent data must then be transferred to another Excel file were it is then sent to a queue for an ERP system. This seems like an unnecessary task. I need to develop a way to automatically download the excel file from the email every time it is sent, then automatically send the necessary data to another Excel file so it can be imported to a queue. Is there a way to automatically download Excel files from emails using Access? Thanks!


Not finding an answer? Try a Google search.