Microsoft Access Forums - Import/Export Data http://www.accessforums.net/ Using or generating external data. en Wed, 22 May 2013 04:12:12 GMT vBulletin 60 http://www.accessforums.net/images/misc/rss.png Microsoft Access Forums - Import/Export Data http://www.accessforums.net/ XML file opens in excel 2010/2013 correctly but not in Access http://www.accessforums.net/import-export-data/xml-file-opens-excel-2010-2013-correctly-35064.html Wed, 22 May 2013 01:30:07 GMT When I open the xml file I got from the states sporting body http://www.racingnsw.com.au/Site/_content/racebooks/20130525RAND0.xml in Excel it opens... When I open the xml file I got from the states sporting body http://www.racingnsw.com.au/Site/_co...30525RAND0.xml in Excel it opens correctly each header and data align. When I use Access and External Data and Import XML, it opens I chose Structure and data and import. I received 2 tables with no data.

There are columns from A to DC and 171 rows of data. How am I importing it incorrectly. ]]> Import/Export Data flebber http://www.accessforums.net/import-export-data/xml-file-opens-excel-2010-2013-correctly-35064.html Export Data to Excel Pivot Table Loss of Formatting http://www.accessforums.net/import-export-data/export-data-excel-pivot-table-loss-formatting-34968.html Fri, 17 May 2013 14:26:47 GMT Hi All I currently have a large excel file that I have now transferred over to Access for ease of use. However I then want to Export this data... Hi All

I currently have a large excel file that I have now transferred over to Access for ease of use. However I then want to Export this data from Access back into an Excel Pivot Table for Analysis purposes.


My problem is that my original Excel file contained a Profit Margin column which was formatted as a percentage and when this was imported into Access this column showed the data as a percentage but was formatted as Short Text. As a result when I attempted to change the formatting to number my populated data was completely changed (mostly to whole numbers.) I need to find a way to format this column correctly as when I export the data back into an Excel Pivot table I can no longer take an average of my Profit Margin data presumably because it doesn't recognize the data.

I am a complete novice with Access and I might have missed something blindingly obvious so any info would be great.

Many Thanks ]]> Import/Export Data Damian http://www.accessforums.net/import-export-data/export-data-excel-pivot-table-loss-formatting-34968.html Connecting to External Data http://www.accessforums.net/import-export-data/connecting-external-data-34950.html Thu, 16 May 2013 16:21:13 GMT I've recently upgraded from using Access XP to Access 2013. I typically use Access to connect to a DB and modify and/or make global changes to my data. In Access XP, I would connect through the 'Connections' option and locate my DB through 'localhost\sqlexpress'. Now with 2013, I cannot even find an option remotely close to allowing me to do this.

Can someone help point me in the right direction? Thanks! ]]> Import/Export Data UnderEstimated http://www.accessforums.net/import-export-data/connecting-external-data-34950.html Linking Excel spreadsheet http://www.accessforums.net/import-export-data/linking-excel-spreadsheet-34938.html Thu, 16 May 2013 09:51:48 GMT Dear all,

I've linked an Excel spreadsheet to my database. One of the fields in the spreadsheet contains more than 255 characters and the imported data is truncated to exactly 255 characters. I tried changing the properties in Access from Text to Memo without much success.

I would appreciate any comments. Many thanks ]]> Import/Export Data nikogeorgiev http://www.accessforums.net/import-export-data/linking-excel-spreadsheet-34938.html Find last used column address using vba http://www.accessforums.net/import-export-data/find-last-used-column-address-using-vba-34926.html Wed, 15 May 2013 20:28:54 GMT Hi All, I am trying to find last used column address from an excel sheet, but I am not getting proper cell address. I am using MS Access 2003 and... Hi All,
I am trying to find last used column address from an excel sheet, but I am not getting proper cell address. I am using MS Access 2003 and please find following code snippet that I used for finding the last column.

(ColumnFound - used with "cells.find" to get particular row position from the sheet.)

Range(ColumnFound.AddressLocal).Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select
Loop

Msgbox(ActiveCell.AddressLocal)

I have around 30 sheets in a workbook, some sheets this code is working correct but some sheets it giving wrong result. I am not sure what wrong with code/excel sheet. Please anyone help me to solve this issue.

Regards,
Selvakumar R ]]> Import/Export Data selvakumar.arc http://www.accessforums.net/import-export-data/find-last-used-column-address-using-vba-34926.html Export Report to existing workbook with multiple new tabs in excel http://www.accessforums.net/import-export-data/export-report-existing-workbook-multiple-new-tabs-34792.html Fri, 10 May 2013 02:16:59 GMT Hi there, I am failry new to access and have some challenges with exporting a report. I read a few threads but have no idea/understanding on how... Hi there,

I am failry new to access and have some challenges with exporting a report. I read a few threads but have no idea/understanding on how to use the coding.

I have a report that I would like to export in an existing workbook but want to create new tabs for each one of my 1st group in my report
eg: Product | business unit | country | Number | are my columns and I want a tab in excel by product.

Thank you for your help!
C. ]]> Import/Export Data Chatholo http://www.accessforums.net/import-export-data/export-report-existing-workbook-multiple-new-tabs-34792.html Export to Word http://www.accessforums.net/import-export-data/export-word-34764.html Thu, 09 May 2013 15:03:51 GMT I need to export header and footer information to Word, Excel, and PowerPoint files. This is for a document control system that includes many... I need to export header and footer information to Word, Excel, and PowerPoint files. This is for a document control system that includes many existing files but is not controlled. I have created a table in Access with control information and need to "push" that information to the hyperlinked file. What is the best way to do this? ]]> Import/Export Data dagwood http://www.accessforums.net/import-export-data/export-word-34764.html subscript out of range error http://www.accessforums.net/import-export-data/subscript-out-range-error-34681.html Mon, 06 May 2013 16:50:33 GMT Hi, I am trying to import a very simple excel spreadsheet (no formulas, just text and numbers) into my database, but I want to append to an... Hi,

I am trying to import a very simple excel spreadsheet (no formulas, just text and numbers) into my database, but I want to append to an existing table not just import. So, when I go to external data, Import, browse for my file, select append option and specify the table, then when I go through the wizard and hit Finish I get a "Subscript out of range" error. I have never received this before and the answers i have "Googled" are not relevant.

The column headings in the excel spreadsheet and the access table are the same. I have attached an example of my spreadsheet.

Thanks for any help!
Attached Files
]]> Import/Export Data adray13 http://www.accessforums.net/import-export-data/subscript-out-range-error-34681.html Importing data into Access database from Excel textboxes http://www.accessforums.net/import-export-data/importing-data-into-access-database-excel-textboxes-34662.html Sun, 05 May 2013 18:29:56 GMT Is it possible to import data from a couple of textboxes into an Access database from Excel, and if so, how?? I basically want to click a button... Is it possible to import data from a couple of textboxes into an Access database from Excel, and if so, how??

I basically want to click a button and the contents of the two text boxes go into an already existing Access database as a new row with the unique Id / Primary key entered automatically.

All help greatly appreciated. ]]> Import/Export Data Sambora http://www.accessforums.net/import-export-data/importing-data-into-access-database-excel-textboxes-34662.html <![CDATA[[Solved] Column assignments in an INSERT INTO type of query.]]> http://www.accessforums.net/import-export-data/column-assignments-insert-into-type-query-34582.html Wed, 01 May 2013 18:48:15 GMT Self taught access user that doesn't understand how a 3rd party is doing something. I would appreciate any help in getting an explanation.

I examined the sql that a 3rd party utilizes and I don't understand how they are getting it to work. The 3rd party sends me text files, empty tables in an mdb, and action queries in the mdb. The SQL in the action queries use INSERT INTO statements. When I execute the queries, the empty tables become populated with the text file data.

Code:
INSERT INTO PROP_ENT ( prop_id, prop_val_yr,....) SELECT  PROP_ENT_TXT.prop_id, PROP_ENT_TXT.prop_val_yr,...) FROM PROP_ENT_TXT;
My lack of understanding comes from the fact that the SELECT part of the statement references various source fields when in fact the text files don't have source field names. I pop open the text files in a text editor and I don't see any type of delimiters.

I would appreciate if someone could take a minute and explain to me how this is working without field names being assigned in the text files.

Thanks for helping out a novice user.

Tom in Austin, Texas ]]> Import/Export Data tfulmer http://www.accessforums.net/import-export-data/column-assignments-insert-into-type-query-34582.html <![CDATA[[Solved] Primary Key Auto Number not available in Excel for import]]> http://www.accessforums.net/import-export-data/primary-key-auto-number-not-available-excel-34567.html Wed, 01 May 2013 01:14:35 GMT I exported a Table to excel and reorganized it and I attempted to import it back to an empty Table with a properly define indexed Primary Key defined... I exported a Table to excel and reorganized it and I attempted to import it back to an empty Table with a properly define indexed Primary Key defined as autoNumber. The Access 2010 import tool does not list autoNumber as a valid data type for the PK IDCarv. I am wondering how to remedy this situation. Idea 1) I have a copy of the pre reorg Table with the autoNumber in it. I could export it, delete the data and copy the good data into it. Idea 2) I could create an empty properly define table shell and export it and copy (append?) the reorg data into it. Or any suggestions? If its not one problem, its another! Have I missed something in the export/import process? TIA Phil ]]> Import/Export Data justphilip2003 http://www.accessforums.net/import-export-data/primary-key-auto-number-not-available-excel-34567.html Import Word form to Access form as new record (user friendly) http://www.accessforums.net/import-export-data/import-word-form-access-form-new-record-34548.html Tue, 30 Apr 2013 15:31:25 GMT I have attached a very simple word doc and database for reference. I have seen a lot of posts in this forum regarding attempts to do what I would... I have attached a very simple word doc and database for reference. I have seen a lot of posts in this forum regarding attempts to do what I would like to do, but none are user friendly at all.
It seems to me this should be a fairly easy thing to do. I want to click a button on my form and fill in a new record by retrieving a word document that has the info needed. If you look at my Test example, I have 3 fields: a date, a combobox choice, and a checkbox. I have code that is supposed to extract the info from the word doc and form a new record, but I get an error "User-defined type not defined" the line of code it hangs on is: "Dim appWord As Word.Application"

The code was taken from a tutorial on "2000". Does any one have a more up to date version that works with "2007"? Thank you for any help.
Private Sub CmdTest_Click()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean
On Error GoTo ErrorHandling
strDocName = "C:\Users\bbrazeau\Desktop\Test"
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Users\bbrazeau\Desktop\" & _
"Test.accdb;"
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic
With rst
.AddNew
!TestDate = doc.FormFields("TestDate").Result
!TestText = doc.FormFields("TestText").Result
!TestChoice = doc.FormFields("Check1").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Contract Imported!"
Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
& "contain the required form fields. " _
& "No data imported.", vbOKOnly, _
"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup
End Sub
Attached Files
]]> Import/Export Data bbrazeau http://www.accessforums.net/import-export-data/import-word-form-access-form-new-record-34548.html (#Num!) this is what I get when I import linked Text into Access, Pls help http://www.accessforums.net/import-export-data/num-what-i-get-when-i-import-34543.html Tue, 30 Apr 2013 12:58:24 GMT Hi, I have linked text in the following format: B1235§123456789§03§M§David§Smith§2011§GR3AM§EM18 13026§324559948§02§M§bimothy§ppshaw§0010§GR... Hi,

I have linked text in the following format:
B1235§123456789§03§M§David§Smith§2011§GR3AM§EM18
13026§324559948§02§M§bimothy§ppshaw§0010§GR 1/2 PM§MR3
35822§325144523§02§M§graven§yuLAsensi§0011§GR 1/2 A AM§MR18
91202§308929977§06§F§gaabiqa§ChowDdhury§0015§6.1 AM§MR1

When imported to access it all looks ok except the first line and first field has (#Num1) sign. not the brackets, means anything with a Alpha numeric has this #Num!. that is "B1235" turns to #Num!. why ??? I want it appear as is.

any idea?

Thanks,
Vivah ]]> Import/Export Data vivah http://www.accessforums.net/import-export-data/num-what-i-get-when-i-import-34543.html Problem with data type when importing CSV via VBA http://www.accessforums.net/import-export-data/problem-data-type-when-importing-csv-via-34537.html Tue, 30 Apr 2013 07:36:44 GMT Hello, I am an Access rookie. I am trying to import a CSV file that has a number column that can vary (1,11, 111.001101, 11.000001) and when I... Hello,

I am an Access rookie. I am trying to import a CSV file that has a number column that can vary (1,11, 111.001101, 11.000001) and when I try to import this via VBA, I can't seem to make it work without calling it a Text field. My challenge with the data is that I cannot control the formatting as I am getting the files from various parties and I need some way of programmatically rounding this out to the nearest whole number.

I have encountered this before with Access and imports, but it was only with dates and times. I was able to work around these by putting in a module that makes a new column and copies the data over as the proper data type and this works- just not sure how to do this for varying integer/double/long data types.

Any thoughts?

Cheers,

Ray ]]> Import/Export Data raynman1972 http://www.accessforums.net/import-export-data/problem-data-type-when-importing-csv-via-34537.html Importing filtered data from excel. http://www.accessforums.net/import-export-data/importing-filtered-data-excel-34503.html Sun, 28 Apr 2013 15:58:38 GMT Hello everybody! I need to import some data from and excel worksheet (VisaID, TestDate and Mark) to a access table, tblTest (TestID, ContactID and... Hello everybody!
I need to import some data from and excel worksheet (VisaID, TestDate and Mark) to a access table, tblTest (TestID, ContactID and Mark). The problem is that I only have to import the data from my contacts, not the rest, and I´d like to do it programatically so that I can save a lot of time, however I don't know neither how to start nor the best way to do it, maybe these are the steps i need to follow:

1º Because tblContacts contains : ContactID and VisaID, could I export the contactID to the contacts who VisaID in the worksheet match with the VisaID of the tblContacts?

2º Could I filter then the worksheet, importing only the records with a not null ContactID to my TestTable (VisaID, TestDate and Mark)?

I'll be very gratefull of any help.
Thank you very much in advance.:rolleyes: ]]> Import/Export Data JOSE LUIS http://www.accessforums.net/import-export-data/importing-filtered-data-excel-34503.html